Personal column in logger_logs

[JuergenSchuster]

I would love to have my personal column "personal" varchar2 which I can populate with an additional parameter and which is also indexed.

Business case I have a lot of logging outputs for one department. Thousands of lines and also hundreds of departments. I just want to filter quickliy to a single department.

The extra clob column is not indexed and is also be used be logging outputs >4K. So we can not use this.

[martindsouza]

@JuergenSchuster can you please provide examples as to what you'd put in here. I'm hesitant on adding such a column.

[JuergenSchuster]

@martindsouza So we have a data load loading millions of data. We use logging to log how much was loaded per ARE which is one of 500 Siemens departments. So at the end of the load we will have 100.000 logger entries for 500 different AREs. We want to filter logger_logs where personal = '4DAF'

[jeffreykemp]

This doesn't quite sound like a debugging requirement, more of a business requirement - so I'd create a purpose-built table for these logs. /2c

[janihur]

Disclaimer: I'm not familiar with logger. However this requirement sounds too narrow to be included into a general purpose logging package (sorry to shoot down all your ideas!).

(Brainstorm warning) How about if you simply wrap logger with your own code:

1. Add table logger_user_logs with foreign key to logger_logs. The content of the table and indexing is under your control.
2. Add PL/SQL wrapper for logger that accepts your special parameters. The wrapper calls native logger subprograms and inserts your special parameters to logger_user_logs.

The above doesn't work as such because logger currently won't tell you the logger_logs.id. Maybe it should so that the framework could be extended this way ?

[JuergenSchuster]

Guys, this was ONE concrete example what you can do with this column, because @martindsouza asked for it.

The requirement is simply having a separate indexed column and a corresponding parameter for your personal use, like tags to group and filter log entries.

Of course I could do a custom solution but thats the whole point of ERs not to have the need for custom changes ;-)

[gilcrest]

It's a good idea. I think a broader way of looking at it would be to add a separate key:value data store associated to a log. Can add ideas on how to do this later as have to run, but I agree something like this is valuable.

[jeffreykemp]

If you're on 12c you could put json into the clob and put a json index on it :) https://oracle-base.com/articles/12c/indexing-json-data-in-oracle-database-12cr1

[JuergenSchuster]

@jeffreykemp You understand this, but me I only understand in general what you are suggesting and I'm sure more than 80% of the regular PL/SQL developers have no idea what you are talking about :-D

We need to find solutions for the simple guys.

I'm implementing Logger now at my customer and do a styleguide how to use it. My PL/SQL colleague who should use it asked me how he can filter the log output of one program e. g. doing a dataload logically. You processing a lot of data and generate thousands of logging rows. He simply wanted to tag the log rows when a new set of data is processed.

It's not making the output more readable. It's simply querying the output of this one procedure for a certain criteria, e. g. department, order or any personal tag you can think of.

We are focusing very much in creating log outputs and this is accomplished in a great way. But when you think about analyzing the output a category / tag field is missing.

[jeffreykemp]

I was being a bit facetious with my json suggestion :)

Seriously, though you will get a lot of logs from different users and different sessions; logger is designed to support this.

The way you filter down to the logs you're interested in are by querying on the combination of User_Name, Module, Action, Client_Identifier and/or Client_Info, or even SID; whatever works for your application.

If that's not sufficient, I will usually create a dedicated logging table for the particular purpose, with columns and constraints that suit each scenario. Querying it will be easier and perform better; and the data will be more useful.

[JuergenSchuster]

@jeffreykemp thats all true what you said. I can analyze the logs well enough when I have output of different procedures. BUT when I have thousands of lines per procedure and they repeat every 100 but with a different criteria, you can not sort this out.

And you could also use this column for puting a personal note in it, like your name and filter the logs that are you interested in particular.

Believe me. We are here at a customer they miss this feature which they have in their custom logging solution. But I want to get rid of that and replace it with Logger. So we should have this feature too which they really need.

And I don't want to create an additional extra solution just for this case. The whole business case for using Logger and replace your custom solution. Just use Logger to cover it all ;-)

[janihur]

I don't think the suggestions by @gilcrest @jeffreykemp are a joke only - that's a way to dump (un)structured data (or data which structure can only be defined by the user of the loggger) to a database without modifying the database structure. I have used xmltype in a few occasions for similar purpose.

I only now realised that the request is in fact the same than traditional eBS way: add DATA1 ... DATAN varchar2 columns to the table and leave the usage completely for the user.

[SvenWeller]

I had a similar scenario to what Jürgen described. Sometimes we just need an indexed column where we can put specific information in, just to be able to filter then on that column. For example "OrderID=123456789". So if we search through the logs, we can easily filter on the OrderID we are interested in.

The JSON suggestion however is a really interesting one. Maybe even a combination of both. One JSON column and one TAG column. We could later add virtual columns on that JSON and search trough that data in a really efficient manner.

In another project we couldn't use logger because of a similiar thing. There we have several million plsql calls per second! If we turn tracing on, then of cause performance goes down considerably. But the problem is just to analyze such a large number of traceing information requires efficient access into structured log data. This is currently not easily done with logger.

[martindsouza]

Ok there's been some valid points here and interesting suggestions. We have to be cautious about adding an index. If it slows down Logger then it probably won't be implemented.

A few things based on the examples provided above:

• Logger is meant for developers. If storing business data then it isn't the right place for it.
• Logger is meant to store data quickly so as to have as minimal impact as possible on the system. If retrieval of said data takes a while then that's the cost of fast inserts.
• In my experience, 95% of the time I query logger_logs_5_min with a filter on scope, client_id, or session.

I will tag this as a future item. Key things that need to be thought of before implementing:

• [ ] How do we add an indexed column and not have any (or very minimal) performance impact
• [ ] How do we do this without changing the API? Right now I like that main logger calls only have 4 parameters. Before adding a new parameter I think about it a lot. Do we add a new function? Do we store indexed data in a separate table?

[jeffreykemp]

IF this is implemented, I would expect it to be an enhancement to the append_params procedure, not an additional parameter to the log calls.

Indexing should, I think, be something that people would apply as needed/desired after installing Logger, taking into account the implications for performance.

[JuergenSchuster]

Logger is meant for developers. If storing business data then it isn't the right place for it.

@martindsouza: You don't get the point. It is NOT for storing business data BUT using additional data for the log output to filter the log output later. Of course it is probably some kind of business key but it can also just be a tag to filter out parts out of the repeated logging in ONE procedure from a loop. So NO reporting for customers but simply helping the developer to filter out a certain log part from a loop of data.

I had (and still have) the same problem like @SvenWeller. Custom Logging solutions simply have this addional column which is used heavily with my customer. So for that reason they refused to replace it with Logger and for my current project the developer asked me the same and I have no answer. Logger is 3 times better then all custom solutions, but this is a major drawback where I simply loose the opportunity to replace the custom solution.

I agree with @jeffreykemp It does not need to be indexed by default. If I want to have a faster retrieval it is up to me (developer) to index that column afterwards and live with the delay then. This is relevant in debugging mode only so not on production.

@martindsouza Whats wrong with an additional parameter, default null. This won't hurt anybody, easy to understand, easy to ignore. Define:

Right now I like that main logger calls only have 4 parameters.

Please choose a simple solution for simple people like me and 90% of the PL/SQL developers. You don't use what you don't understand ;-) Additional tag column, varchar2(4000), no index, 5th parameter default null. Sometimes you don't need node.js JSON and Markdown. Sometimes life can be easy :-)

[jeffreykemp]

The absence of named parameters in the documented sample code caused me some worry for this very reason - since almost everyone will be using positional notation, logger can't easily be changed to accommodate more parameters.

IF any additional parameters to all the log procedures are to be added, these parameters should come with a warning to always use named notation, at least for the additional parameters (if not for all), e.g.:

logger.log('START', l_scope, null, l_params, p_extra1 => 'bla');

logger.log('START', l_scope, null, l_params, p_something => 'foo');

Publishing this as the recommended practice going forward will mitigate against future problems with adding more parameters to these procedures.

However, I would still prefer to see this added as a "power option" for the append_params procedure.

P.S. Juergen, why don't you fork the project for them and add the extra column & parameter? I've already done this for my own extensions.

[JuergenSchuster]

@jeffreykemp I don't understand your suggestion with append_params. They already fill the extra column with parameters. Do you think of a separate call of append_params before every logger.log call to fill the extra column?

Forking the whole thing and maintain it by myself for an extra column? I want to sell the customer a standard solution he can install and have all functionality he needs and expects. This is a working tool for me that should work out of the box ;-)

I wouldn't expect to have so much discussion about a business critical and extremely easy to implement enhancement request. I don't like democracy :-D

[jeffreykemp]

The idea is to have an alternative form of append_params which fills in your extra columns, not the "extra" column. You call it once with your business ID data when your code has it. The advantage of this approach is that all calls to the log routines will log this data, so you don't need to change all your calls to logger.

Shrink-wrapped proprietary code must work "out of the box" exactly how you want it, otherwise you can't use it. Logger, however, is free and open-source, which means we have the freedom to fork it and make whatever changes our clients need to support their requirements. That's one of the primary benefits of open source software!

[JuergenSchuster]

@jeffreykemp can you do an example? I'm not sure if I get it right. You say use the current functionality of append_params to fill the current extra column with the new filter data? So something like:

for l_rec in ( select ... ) loop
   logger.append_param(l_params, 'deptno', l_rec.deptno);
   logger.log('empno', l_rec.empno);
end loop;

I believe that it could make sense for a big company to fork Webkit or Android. But regular users should rather request their requirements so that they get in the new version of Logger. But I understand if they don't after such a long process for such a simple and mission critical enhancement request :-)

[jeffreykemp]

Yes, something like that, except that it would have to be an alternative version, and would specify which "extra" column it should set instead of an arbitrary attribute, e.g.

  for l_rec in ( select ... ) loop
    logger.append_param_extra(l_params, p_extra => 'ref1', p_val => l_rec.deptno);
    logger.append_param_extra(l_params, p_extra => 'ref2', p_val => l_rec.empno);
    logger.log('processing record', l_scope, null, l_params);
    ...
    logger.log('partway through', l_scope, null, l_params);
    ...
    logger.log('processing record finished', l_scope, null, l_params);
    l_params.delete; -- should we clear the parameters ready for next loop iteration?
  end loop;

or something like that. It would really depend on what form the "extra" column (or columns) take. This approach means that your new filter data is logged with every call to logger.log. (Whether doing the .delete is a good idea or not, however, is open to discussion.)

[JuergenSchuster]

I checked this solution but you have the complete parameter list in the extra column together with your fake parameter which has the additional info. So no workaround yet.

OK. I officially give up now. I think thats the problem why I don't like Open Source projects, because it is not business driven. It's more like a playground for specialists who want to play with their skills and source code. I invested so much is this very simple request. I would have loved to have a company behind Logger and simply pay them for implementing this business critical CR. I have accomplished 4 ERs for Slack and Droptask with just one single email. They see the business value right away and implement it to make their product better.

This is the spirit I'm missing here. Seeing it as a product and want to make it better and satisfy the customers...

[martindsouza]

I really appreciate all the feedback and ideas on this ticket. It's great to see all the ideas and suggestions for his project.

I want to highlight the goal of Logger: "The goal of logger is to be as simple as possible to install and use."

@jeffreykemp re named parameters: I'm a big advocate of named parameters for most PL/SQL development. For utility functions that are repeatedly used (such as Logger) I don't think they should be included since it should be quick and easy for developers to use. Thus we don't promote them in our sample code.

re adding an additional parameter: For the reason above I'm very hesitant about adding new parameters. It's not just this issue to deal with. What happens when another requirement comes up and we add more and more parameters. It then stops making it clear to use without named parameter.

Going forward, if in this is critical I recommend indexing the scope column and trying to overload that with package.procedure.{code}. This is just an idea, not a final proposed solution for this issue.

Of course as @jeffreykemp also suggested you could fork this project. Please be aware we have no intention of supporting forked versions. We have a lot of interesting features planned for future versions of Logger which you many not merge well with a forked version.

[JuergenSchuster]

Thanks @martindsouza. Your explanation shows exactly what imho is not right with Open Source projects in general and Logger in particular:

The goal of logger is to be as simple as possible to install and use.

Not at all. There should be only one goal and that is: Fullfill all requirements of the customers. Or make the customer happy.

It is great if it is easy to use not so important that it is easy to install (you do this only once).

For the reason above I'm very hesitant about adding new parameters. It's not just this issue to deal with. What happens when another requirement comes up and we add more and more parameters. It then stops making it clear to use without named parameter.

I agree with @jeffreykemp to use named parameters but this is up to everybody, not worth to discuss further. BUT this personal liking can't be the reason why to stop enhancements to Logger.

Who cares about a 5th, 6th, 7th parameter? If they have default values nothing breaks, you can use Logger without even knowing them for the rest of your life. If an additional optional parameter brings great new functionality, give them to me. I can even count up to ten ;-)

I can not believe that I have this discussion it feels a little bit like candid camera. Please don't let your personal likings interfere with this product. Look at it as a product and listen to your customers, not to your techie friends ;-)

[eaolson]

With all due respect, @JuergenSchuster, I think you're being unreasonable here. I think you've lost sight of the fact that you are not the customer here, but rather one customer among many. @martindsouza disagrees that your suggestion matches with his vision of his project and has declined to implement it.

Personally, I don't see a lot of advantage in your suggestion. You already have three columns in the logs entirely under your control. I'm not sure how a fourth adds much value. I would think you should be able to construct the p_text parameter to be filterable or even add an Oracle Text index. Your need seems to be a very particular business requirement that isn't generally applicable.

Or, if you do think this is valuable and necessary, how about writing it as a Logger plugin? That's one of the great things about open-source software.

[JuergenSchuster]

@eaolson The problem is you guys don't see it from the perspective of the customers. You think you are the customers, but you don't. You understand and can do all the fancy stuff which 95% of all other PL/SQL developers out there never understood, never used. They don't know about Logger. They have their simple custom solutions which they love, because they understand them fully. They don't want to change, they just do their 9 to 5 job. You need to have very good reason to replace their custom solutions with something new AND it has to be easy to understand and easy to use AND it needs somebody who shows them how easy it is to use and the advantages they get out of it.

Logger is already VERY complicated to fully understand. At the end not so complicated to use, BUT when you look at it the first time you get overwhelmed by it's possiblities and use cases. But thats a problem of marketing and simple documentation not to scare away the customers. @martindsouza did already a good job with the API documentation and best practises documentation, but he will always be a nerd who can not think as easy as the regular guys out there. If you want to leverage this so far great solution and give it a chance to be used a thousand times more customers then it needs different documentation which should be leveled and grouped by use cases. But this skill is missing in a world where nerds create solutions for other nerds in a nerd environment without going through the filter of product management and marketing. Thats the reason why Logger is an amazing solution, but a very poor product and 90% of the PL/SQL developers don't even know about it. AND if they get ever in contact with it they don't persue the effort to undertand it because it's not presented as easy as it should be.

The requirement came from 2 different PL/SQL developers at a very large customer (100 billion revenue, 350K employees). I am the guy who tries to replace their long time used but different custom logging solutions with Logger. It's very hard for them to replace their loved personal solution with somehting new. But in the name of unification and simplification they finally agreed to have at least a look at it. When I can show them how to use it and the benefits they maybe consider it to use it too. Both solutions have this extra column where they store tags to filter out the result afterwards. They showed me the concrete use cases of it and asked me how they could do it with Logger. I had and still have no answer. So this was it for Logger! Lost 2 customers! And they will NEVER look at it again, because now they know that their own solution is better (fulfill ALL their requirements) and Logger can not replace it. I told them that I would talk to the programmer and that I would be sure that even we have already a solution for that or we will have in the next version.

I came up with another request years ago and was forced to enhance Logger by myself and avoided new versions like hell - not to reimplement my personal changes. The requirement was to be able to switch on log mode in production for just one single APEX application if it looks something is wrong with it and switch on logging for just one user in an APEX application if he calls about a problem (you don't have access to production so it has to be set by a setting through a program). I showed @martindsouza my personal solution and he came up with one that was twice a good as mine and could even be used with PL/SQL only the client_identifier parameter in set_level.

@SvenWeller had the same use case:

I had a similar scenario to what Jürgen described. Sometimes we just need an indexed column where we can put specific information in, just to be able to filter then on that column. For example "OrderID=123456789". So if we search through the logs, we can easily filter on the OrderID we are interested in. In another project we couldn't use logger because of a similiar thing. There we have several million plsql calls per second! If we turn tracing on, then of cause performance goes down considerably. But the problem is just to analyze such a large number of traceing information requires efficient access into structured log data. This is currently not easily done with logger.

So we are already 3! now of which we know in such a short time asking so few people. And yes I am THE customer. Because I look at Logger as a product and I am ONE of the simple PL/SQL devloper like the 90% out there. The crowd listening to you in conferences and the much larger crowd never attended a conference and maybe never will and you will never hear from them. This is a rare situation that a real customer is even talking to you. Knows about Logger, knows about Github, knows how to take part in such a discussion and not afraid talking to you because he is not as clever.

How can you say:

Personally, I don't see a lot of advantage in your suggestion. You already have three columns in the logs entirely under your control. I'm not sure how a fourth adds much value. I would think you should be able to construct the p_text parameter to be filterable or even add an Oracle Text index. Your need seems to be a very particular business requirement that isn't generally applicable.

Either you haven't really read the requirement (because right now there is no simple workaround with the current solution) or you simply deny a real existing customer requirement or you are also already in the world beyond reason, suggesting to drill a hole into the engine hood to store personal things, because you don't like golveboxes which a simple driver would expect in a $ 100,000 dollar car ;-) Adding Oracle Text for that, really?

It's so simple:

• Logger is an amazing logging solution, the best out there
• Logger is a poor product because of the lack of marketing and information (complexity) hiding and because the owner is also the developer which is also the guy who decides what will be implemented in future versions.
• Logger misses a simple but critical feature used by custom logging solutions out there preventing existing customers to use Logger. An extra column (that CAN be indexed by the customers) to analyze their massive logging outputs
• It is a simple enhancement. Just add another parameter, default null, nothing would break
• We probably won't get it because it does not fit to the personal liking of the developer. On Github there are no Product Managers who should made such decisions.
• And because the owner of Logger lives in this Github world together with a lot of other nerds who rather come up with ridiculous fancy workarounds instead of seeing this simple request and the simple solution for it, I stop promoting Logger and hope somebody will buy it, make a product out of it, sell it with the right marketing and customer focus

At least know I have the personal experience that you can NOT use Open Source solutions for real products where you need to rely on bug fixings and enhancements. You pay a much higher price in the end but buying a professional maintained solution from a company. Open Source is a playground for nerds and a marketing gag for big companies to pretend their software is now available for everybody... ;-)

[apexbine]

Just in case anyone is still interested after this discussion digressed a bit from the actual topic...

We have this requirement, too! (And I am working for a real world customer.)

[martindsouza]

@apexbine I still recommend looking at leveraging the p_scope variable to modify to what you need it to store for group level logging. Ex: package.procedure.{code/group}.

[martindsouza]

Note to future readers: This comment has gone a bit off-topic.

Closing as we won't implement as they're simple alternatives that can be used (see my previous comment).