SEO

Hello,
I want to implement a general logging framework for our development
team at our site, currently studying several samples and initiatives
around log4j, java.util.logging and log4plsql.

What I like to log in every invocation of a stored procedure is the
name and value of arguments that the procedure has been called with.
I can lookup the parameter names in e.g. [all|dba]_parameters.
Of course I can ask to explicitly write something like
(log_pck being some imagined utility):

log_pck.log_par(..., p_parameter_name => r_param.argument_name,
p_parameter_value => p_param1);
where p_param1 is the name of a parameter of the invoked procedure

However I am a programmer and looking for ways to reduce the number of
lines I write and the number of errors I make and also have seen e.g.
C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
convenently it would be to write in every module at line 1:

log_pck.log_pars(p_parameterlist => p_argv);

where p_argv is a system supplied local table of ANY or LONG where all
parameter values are listed.
Within log_pck.log_pars, I would use who_called_me to see who wants to
log, and then loop through the table_of and write the values.

However with getting the values dynamically I am completely stuck.
Throwing dynamic SQL in hasn't helped since the local variable space
of the dynamice context has no knowledge over my parameters.

I believe that execution time logging/instrumentation is immensly
important and often overlooked or under-implemented, and also that
having such a system supplied list as an enhancement in a future DBMS
release would be a great addition.
The issue of a list of values or a list of references (i.e. pointers)
in order to prevent data volume issues is of course to be addressed in
some clever way.

Any clues, hints are appreciated. Or perhaps it's just out there and I
didn't see it?


Regards,
Erik Ykema

On Jan 12, 3:11*pm, ErikYkema <erik.yk...@gmail.com> wrote:
> Hello,
> I want to implement a general logging framework for our development
> team at our site, currently studying several samples and initiatives
> around log4j, java.util.logging and log4plsql.
>
> What I like to log in every invocation of a stored procedure is the
> name and value of arguments that the procedure has been called with.
> I can lookup the parameter names in e.g. [all|dba]_parameters.
> Of course I can ask to explicitly write something like
> (log_pck being some imagined utility):
>
> * log_pck.log_par(..., p_parameter_name => r_param.argument_name,
> p_parameter_value => p_param1);
> where p_param1 is the name of a parameter of the invoked procedure
>
> However I am a programmer and looking for ways to reduce the number of
> lines I write and the number of errors I make and also have seen e.g.
> C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
> convenently it would be to write in every module at line 1:
>
> * log_pck.log_pars(p_parameterlist => p_argv);
>
> where p_argv is a system supplied local table of ANY or LONG where all
> parameter values are listed.
> Within log_pck.log_pars, I would use who_called_me to see who wants to
> log, and then loop through the table_of and write the values.
>
> However with getting the values dynamically I am completely stuck.
> Throwing dynamic SQL in hasn't helped since the local variable space
> of the dynamice context has no knowledge over my parameters.
>
> I believe that execution time logging/instrumentation is immensly
> important and often overlooked or under-implemented, and also that
> having such a system supplied list as an enhancement in a future DBMS
> release would be a great addition.
> The issue of a list of values or a list of references (i.e. pointers)
> in order to prevent data volume issues is of course to be addressed in
> some clever way.
>
> Any clues, hints are appreciated. Or perhaps it's just out there and I
> didn't see it?
>
> Regards,
> Erik Ykema

I do not think such a function is such a great idea since you are
going to be adding extra overhead and tracking information for every
call but look into the following supplied Oracle packages and
procedures to see what ideas you can come up with:

dbms_utility.format_call_stack
dbms_utility.format_error_stack
dbms_application_info

I do not think the actual parameter values are available via the call
stack but it definitely lets you find out who called the code in
question.

anonymous transactions - Write a logging routine that runs as an
anonymous transaction and call it when needed such as on error to log
useful information. It can also be called by debugging code, perhaps
even triggered by a debug parameter, so that the code could always be
present in the procedure/package. See PL/SQL manaul for example use
of anonymous transactions.

Visit http://asktom.oracle.com and try a seach on who_called_me. If
that does not find an example of using the format_call_stack then try
searching on "format_call_stack". I am sure I have seen the routine
used by some of Tom's code.

HTH -- Mark D Powell --

On Jan 13, 11:39 pm, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On Jan 12, 3:11 pm, ErikYkema <erik.yk...@gmail.com> wrote:
>
>
>
> > Hello,
> > I want to implement a general logging framework for our development
> > team at our site, currently studying several samples and initiatives
> > around log4j, java.util.logging and log4plsql.
>
> > What I like to log in every invocation of a stored procedure is the
> > name and value of arguments that the procedure has been called with.
> > I can lookup the parameter names in e.g. [all|dba]_parameters.
> > Of course I can ask to explicitly write something like
> > (log_pck being some imagined utility):
>
> > log_pck.log_par(..., p_parameter_name => r_param.argument_name,
> > p_parameter_value => p_param1);
> > where p_param1 is the name of a parameter of the invoked procedure
>
> > However I am a programmer and looking for ways to reduce the number of
> > lines I write and the number of errors I make and also have seen e.g.
> > C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
> > convenently it would be to write in every module at line 1:
>
> > log_pck.log_pars(p_parameterlist => p_argv);
>
> > where p_argv is a system supplied local table of ANY or LONG where all
> > parameter values are listed.
> > Within log_pck.log_pars, I would use who_called_me to see who wants to
> > log, and then loop through the table_of and write the values.
>
> > However with getting the values dynamically I am completely stuck.
> > Throwing dynamic SQL in hasn't helped since the local variable space
> > of the dynamice context has no knowledge over my parameters.
>
> > I believe that execution time logging/instrumentation is immensly
> > important and often overlooked or under-implemented, and also that
> > having such a system supplied list as an enhancement in a future DBMS
> > release would be a great addition.
> > The issue of a list of values or a list of references (i.e. pointers)
> > in order to prevent data volume issues is of course to be addressed in
> > some clever way.
>
> > Any clues, hints are appreciated. Or perhaps it's just out there and I
> > didn't see it?
>
> > Regards,
> > Erik Ykema
>
> I do not think such a function is such a great idea since you are
> going to be adding extra overhead and tracking information for every
> call but look into the following supplied Oracle packages and
> procedures to see what ideas you can come up with:
>
> dbms_utility.format_call_stack
> dbms_utility.format_error_stack
> dbms_application_info
>
> I do not think the actual parameter values are available via the call
> stack but it definitely lets you find out who called the code in
> question.
>
> anonymous transactions - Write a logging routine that runs as an
> anonymous transaction and call it when needed such as on error to log
> useful information. It can also be called by debugging code, perhaps
> even triggered by a debug parameter, so that the code could always be
> present in the procedure/package. See PL/SQL manaul for example use
> of anonymous transactions.
>
> Visithttp://asktom.oracle.comand try a seach on who_called_me. If
> that does not find an example of using the format_call_stack then try
> searching on "format_call_stack". I am sure I have seen the routine
> used by some of Tom's code.
>
> HTH -- Mark D Powell --

Hi Mark,Thanks for the reply.
I am not worried about the overhead, the sample is mostly for making
clear what I look for as an option, not to actually do this in every
call. (mostly OLTP batch, ETL/OWB mappings.)
I may conclude that you do not think argv[] is such a good idea? I am
sorry to hear that.
The refs to Tom are fine but besides the point.
Regards,
Erik

On Jan 12, 12:11*pm, ErikYkema <erik.yk...@gmail.com> wrote:
> Hello,
> I want to implement a general logging framework for our development
> team at our site, currently studying several samples and initiatives
> around log4j, java.util.logging and log4plsql.
>
> What I like to log in every invocation of a stored procedure is the
> name and value of arguments that the procedure has been called with.
> I can lookup the parameter names in e.g. [all|dba]_parameters.
> Of course I can ask to explicitly write something like
> (log_pck being some imagined utility):
>
> * log_pck.log_par(..., p_parameter_name => r_param.argument_name,
> p_parameter_value => p_param1);
> where p_param1 is the name of a parameter of the invoked procedure
>
> However I am a programmer and looking for ways to reduce the number of
> lines I write and the number of errors I make and also have seen e.g.
> C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
> convenently it would be to write in every module at line 1:
>
> * log_pck.log_pars(p_parameterlist => p_argv);
>
> where p_argv is a system supplied local table of ANY or LONG where all
> parameter values are listed.
> Within log_pck.log_pars, I would use who_called_me to see who wants to
> log, and then loop through the table_of and write the values.
>
> However with getting the values dynamically I am completely stuck.
> Throwing dynamic SQL in hasn't helped since the local variable space
> of the dynamice context has no knowledge over my parameters.
>
> I believe that execution time logging/instrumentation is immensly
> important and often overlooked or under-implemented, and also that
> having such a system supplied list as an enhancement in a future DBMS
> release would be a great addition.
> The issue of a list of values or a list of references (i.e. pointers)
> in order to prevent data volume issues is of course to be addressed in
> some clever way.
>
> Any clues, hints are appreciated. Or perhaps it's just out there and I
> didn't see it?
>
> Regards,
> Erik Ykema

Erik,
I have developed a logging/call stack feature that displays arguments
in PL/SQL and java stored procs too. I could not figure out how to get
at the argv[] equiv, so I took the tedious path of explicitly putting
args in logging procedure calls. It does not sound so great, however,
I use an IDE that provides template usage that makes it convenient
(Allroundautomation's PL/SQL Developer). With those templates, I
provide procedure/function name and argument names + data types.
Within the template, I can then create procedures that not only list
the arguments, but makes certain calls. These templates allow for any
number of arguments and correctly places them inside parens.

Template: prompts for procedure name, argument names, types and data
types, generates a code block where my cursor is placed (I have
simplified this only showing a single argument, but my real template
allows for more):

PROCEDURE [Procedure Name]( [Arg 1 Name ] [Arg 1 Type] [Arg 1
DataType])
IS

lc_proc CONSTANT VARCHAR2(61) := gc_package || '.[Procedure Name]';

BEGIN

logger.bproc( lc_proc, util.parms2str('[Arg 1 Name]',[Arg 1
Name]) );

logger.eproc( lc_proc );

EXCEPTION
WHEN OTHERS THEN
logger.fproc( lc_proc, SQLCODE, SQLERRM );
RAISE;

END [Procedure Name]

Example procedure Name="run_queued", argument(single for example) =
"p_group_id", "IN", "PLS_INTEGER" would look like this:

PROCEDURE run_queued( p_group_id IN PLS_INTEGER )
IS

lc_proc CONSTANT VARCHAR2(61) := gc_package || '.run_queued';

BEGIN

logger.bproc( lc_proc, util.parms2str('p_group_id',p_group_id) );

logger.eproc( lc_proc );

EXCEPTION
WHEN OTHERS THEN
logger.fproc( lc_proc, SQLCODE, SQLERRM );
RAISE;

END run_queued;

broc = logger's "begin a procecedure"
eproc = logger's "end a procedure", takes an optional arg for a
message, such as a return value
fproc = logger's "end a procedure with a failure"

util.parms2str procedure takes up to 8 name value pairs of a varchar2.
Anything that cannot be implied as char must be explicitly converted
( i.e. dates), this allows me to place dates in whatever position is
warrented, without creating separate overloads that have every
possible combination of char parms and date parms.

After my creation of the code in the appropriate location within a
package, I just start coding (between the bproc and eproc calls). I
know this is not exactly what you are looking for, but this shows what
can be done, with minimal extra coding.

The resulting logging display looks something like this:

07:11:15.123 Starting JOBS_PKG.run_reports
07:11:15.123 ..Starting REPORTS_PKG.run_queued with group_id=1234
...
07:11:15.201 ..Ending REPORTS_PKG.run_queued
07:11:15.201 Ending JOBS_PKG.run_reports

I use these calls throughout all my code and allows for easy debugging
and problem solving in addition to pinpointing specific code that
takes longer than expected, etc. The key is that for most of the
calls, I dont have to type any code, the template places the code for
me.

Vince

On Jan 15, 4:25 pm, Vince <vinn...@yahoo.com> wrote:
> On Jan 12, 12:11 pm, ErikYkema <erik.yk...@gmail.com> wrote:
>
>
>
> > Hello,
> > I want to implement a general logging framework for our development
> > team at our site, currently studying several samples and initiatives
> > around log4j, java.util.logging and log4plsql.
>
> > What I like to log in every invocation of a stored procedure is the
> > name and value of arguments that the procedure has been called with.
> > I can lookup the parameter names in e.g. [all|dba]_parameters.
> > Of course I can ask to explicitly write something like
> > (log_pck being some imagined utility):
>
> > log_pck.log_par(..., p_parameter_name => r_param.argument_name,
> > p_parameter_value => p_param1);
> > where p_param1 is the name of a parameter of the invoked procedure
>
> > However I am a programmer and looking for ways to reduce the number of
> > lines I write and the number of errors I make and also have seen e.g.
> > C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
> > convenently it would be to write in every module at line 1:
>
> > log_pck.log_pars(p_parameterlist => p_argv);
>
> > where p_argv is a system supplied local table of ANY or LONG where all
> > parameter values are listed.
> > Within log_pck.log_pars, I would use who_called_me to see who wants to
> > log, and then loop through the table_of and write the values.
>
> > However with getting the values dynamically I am completely stuck.
> > Throwing dynamic SQL in hasn't helped since the local variable space
> > of the dynamice context has no knowledge over my parameters.
>
> > I believe that execution time logging/instrumentation is immensly
> > important and often overlooked or under-implemented, and also that
> > having such a system supplied list as an enhancement in a future DBMS
> > release would be a great addition.
> > The issue of a list of values or a list of references (i.e. pointers)
> > in order to prevent data volume issues is of course to be addressed in
> > some clever way.
>
> > Any clues, hints are appreciated. Or perhaps it's just out there and I
> > didn't see it?
>
> > Regards,
> > Erik Ykema
>
> Erik,
> I have developed a logging/call stack feature that displays arguments
> in PL/SQL and java stored procs too. I could not figure out how to get
> at the argv[] equiv, so I took the tedious path of explicitly putting
> args in logging procedure calls. It does not sound so great, however,
> I use an IDE that provides template usage that makes it convenient
> (Allroundautomation's PL/SQL Developer). With those templates, I
> provide procedure/function name and argument names + data types.
> Within the template, I can then create procedures that not only list
> the arguments, but makes certain calls. These templates allow for any
> number of arguments and correctly places them inside parens.
>
> Template: prompts for procedure name, argument names, types and data
> types, generates a code block where my cursor is placed (I have
> simplified this only showing a single argument, but my real template
> allows for more):
>
> PROCEDURE [Procedure Name]( [Arg 1 Name ] [Arg 1 Type] [Arg 1
> DataType])
> IS
>
> lc_proc CONSTANT VARCHAR2(61) := gc_package || '.[Procedure Name]';
>
> BEGIN
>
> logger.bproc( lc_proc, util.parms2str('[Arg 1 Name]',[Arg 1
> Name]) );
>
> logger.eproc( lc_proc );
>
> EXCEPTION
> WHEN OTHERS THEN
> logger.fproc( lc_proc, SQLCODE, SQLERRM );
> RAISE;
>
> END [Procedure Name]
>
> Example procedure Name="run_queued", argument(single for example) =
> "p_group_id", "IN", "PLS_INTEGER" would look like this:
>
> PROCEDURE run_queued( p_group_id IN PLS_INTEGER )
> IS
>
> lc_proc CONSTANT VARCHAR2(61) := gc_package || '.run_queued';
>
> BEGIN
>
> logger.bproc( lc_proc, util.parms2str('p_group_id',p_group_id) );
>
> logger.eproc( lc_proc );
>
> EXCEPTION
> WHEN OTHERS THEN
> logger.fproc( lc_proc, SQLCODE, SQLERRM );
> RAISE;
>
> END run_queued;
>
> broc = logger's "begin a procecedure"
> eproc = logger's "end a procedure", takes an optional arg for a
> message, such as a return value
> fproc = logger's "end a procedure with a failure"
>
> util.parms2str procedure takes up to 8 name value pairs of a varchar2.
> Anything that cannot be implied as char must be explicitly converted
> ( i.e. dates), this allows me to place dates in whatever position is
> warrented, without creating separate overloads that have every
> possible combination of char parms and date parms.
>
> After my creation of the code in the appropriate location within a
> package, I just start coding (between the bproc and eproc calls). I
> know this is not exactly what you are looking for, but this shows what
> can be done, with minimal extra coding.
>
> The resulting logging display looks something like this:
>
> 07:11:15.123 Starting JOBS_PKG.run_reports
> 07:11:15.123 ..Starting REPORTS_PKG.run_queued with group_id=1234
> ...
> 07:11:15.201 ..Ending REPORTS_PKG.run_queued
> 07:11:15.201 Ending JOBS_PKG.run_reports
>
> I use these calls throughout all my code and allows for easy debugging
> and problem solving in addition to pinpointing specific code that
> takes longer than expected, etc. The key is that for most of the
> calls, I dont have to type any code, the template places the code for
> me.
>
> Vince

Hi Vince,
Thanks for the pragmatic suggestions, I will experiment with them. And
file a feature request with Oracle, if I can find the address for
that.
Regards,
Erik

"ErikYkema" <erik.ykema@gmail.com> wrote in message
news:a253fa1c-8936-444f-85a6-40b04cadd631@f10g2000hsf.googlegroups.com...
> On Jan 15, 4:25 pm, Vince <vinn...@yahoo.com> wrote:
>> On Jan 12, 12:11 pm, ErikYkema <erik.yk...@gmail.com> wrote:
>>
>>
>>
>> > Hello,
>> > I want to implement a general logging framework for our development
>> > team at our site, currently studying several samples and initiatives
>> > around log4j, java.util.logging and log4plsql.
>>
>> > What I like to log in every invocation of a stored procedure is the
>> > name and value of arguments that the procedure has been called with.
>> > I can lookup the parameter names in e.g. [all|dba]_parameters.
>> > Of course I can ask to explicitly write something like
>> > (log_pck being some imagined utility):
>>
>> > log_pck.log_par(..., p_parameter_name => r_param.argument_name,
>> > p_parameter_value => p_param1);
>> > where p_param1 is the name of a parameter of the invoked procedure
>>
>> > However I am a programmer and looking for ways to reduce the number of
>> > lines I write and the number of errors I make and also have seen e.g.
>> > C's and ksh's arrays of parameters: argv[0..argc-1], or $#. How
>> > convenently it would be to write in every module at line 1:
>>
>> > log_pck.log_pars(p_parameterlist => p_argv);
>>
>> > where p_argv is a system supplied local table of ANY or LONG where all
>> > parameter values are listed.
>> > Within log_pck.log_pars, I would use who_called_me to see who wants to
>> > log, and then loop through the table_of and write the values.
>>
>> > However with getting the values dynamically I am completely stuck.
>> > Throwing dynamic SQL in hasn't helped since the local variable space
>> > of the dynamice context has no knowledge over my parameters.
>>
>> > I believe that execution time logging/instrumentation is immensly
>> > important and often overlooked or under-implemented, and also that
>> > having such a system supplied list as an enhancement in a future DBMS
>> > release would be a great addition.
>> > The issue of a list of values or a list of references (i.e. pointers)
>> > in order to prevent data volume issues is of course to be addressed in
>> > some clever way.
>>
>> > Any clues, hints are appreciated. Or perhaps it's just out there and I
>> > didn't see it?
>>
>> > Regards,
>> > Erik Ykema
>>
>> Erik,
>> I have developed a logging/call stack feature that displays arguments
>> in PL/SQL and java stored procs too. I could not figure out how to get
>> at the argv[] equiv, so I took the tedious path of explicitly putting
>> args in logging procedure calls. It does not sound so great, however,
>> I use an IDE that provides template usage that makes it convenient
>> (Allroundautomation's PL/SQL Developer). With those templates, I
>> provide procedure/function name and argument names + data types.
>> Within the template, I can then create procedures that not only list
>> the arguments, but makes certain calls. These templates allow for any
>> number of arguments and correctly places them inside parens.
>>
>> Template: prompts for procedure name, argument names, types and data
>> types, generates a code block where my cursor is placed (I have
>> simplified this only showing a single argument, but my real template
>> allows for more):
>>
>> PROCEDURE [Procedure Name]( [Arg 1 Name ] [Arg 1 Type] [Arg 1
>> DataType])
>> IS
>>
>> lc_proc CONSTANT VARCHAR2(61) := gc_package || '.[Procedure Name]';
>>
>> BEGIN
>>
>> logger.bproc( lc_proc, util.parms2str('[Arg 1 Name]',[Arg 1
>> Name]) );
>>
>> logger.eproc( lc_proc );
>>
>> EXCEPTION
>> WHEN OTHERS THEN
>> logger.fproc( lc_proc, SQLCODE, SQLERRM );
>> RAISE;
>>
>> END [Procedure Name]
>>
>> Example procedure Name="run_queued", argument(single for example) =
>> "p_group_id", "IN", "PLS_INTEGER" would look like this:
>>
>> PROCEDURE run_queued( p_group_id IN PLS_INTEGER )
>> IS
>>
>> lc_proc CONSTANT VARCHAR2(61) := gc_package || '.run_queued';
>>
>> BEGIN
>>
>> logger.bproc( lc_proc, util.parms2str('p_group_id',p_group_id) );
>>
>> logger.eproc( lc_proc );
>>
>> EXCEPTION
>> WHEN OTHERS THEN
>> logger.fproc( lc_proc, SQLCODE, SQLERRM );
>> RAISE;
>>
>> END run_queued;
>>
>> broc = logger's "begin a procecedure"
>> eproc = logger's "end a procedure", takes an optional arg for a
>> message, such as a return value
>> fproc = logger's "end a procedure with a failure"
>>
>> util.parms2str procedure takes up to 8 name value pairs of a varchar2.
>> Anything that cannot be implied as char must be explicitly converted
>> ( i.e. dates), this allows me to place dates in whatever position is
>> warrented, without creating separate overloads that have every
>> possible combination of char parms and date parms.
>>
>> After my creation of the code in the appropriate location within a
>> package, I just start coding (between the bproc and eproc calls). I
>> know this is not exactly what you are looking for, but this shows what
>> can be done, with minimal extra coding.
>>
>> The resulting logging display looks something like this:
>>
>> 07:11:15.123 Starting JOBS_PKG.run_reports
>> 07:11:15.123 ..Starting REPORTS_PKG.run_queued with group_id=1234
>> ...
>> 07:11:15.201 ..Ending REPORTS_PKG.run_queued
>> 07:11:15.201 Ending JOBS_PKG.run_reports
>>
>> I use these calls throughout all my code and allows for easy debugging
>> and problem solving in addition to pinpointing specific code that
>> takes longer than expected, etc. The key is that for most of the
>> calls, I dont have to type any code, the template places the code for
>> me.
>>
>> Vince
>
> Hi Vince,
> Thanks for the pragmatic suggestions, I will experiment with them. And
> file a feature request with Oracle, if I can find the address for
> that.
> Regards,
> Erik
Just file an SR on Metalink.
Jim