DBMS_UTILITY.FORMAT_CALL_STACK length [message #377048] |
Fri, 19 December 2008 11:07  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
Hi,
I'm using Oracle 10.2G and I'm using the DBMS_UTILITY.FORMAT_CALL_STACK (and error backtrace as well) for some logging stuff.
The thing is that my current code looks like (simplified version shown):
PROCEDURE p IS
l_stack VARCHAR2(4000); -- I don't like that line
l_something table_name.column_name%TYPE;
BEGIN
l_stack := DBMS_UTILITY.FORMAT_CALL_STACK;
-- here comes the part to use the l_stack...
END p;
What I do not like about that code is the l_stack definition. I would prefer to avoid hardcoded type definition to VARCHAR2(4000).
I did some tests and it seems that format_call_stack is limited to 1900 bytes on my system. According to Oracle this is limited to 2000 bytes.
Questions:
1) Where I can get the (dynamic) type definition that FORMAT_CALL_STACK is returning.
2) Is there any document describing the dbms_utility in details (giving more details than this oracle page)?
3) Can I get rid of the header part of this call stack string (I don't like the idea of selecting a SUBSTR from it)
4) What is the difference between SQLERRM and FORMAT_ERROR_BACKTRACE ("The output is similar to the output of the SQLERRM function, but not subject to the same size limitation." is not telling me much)
5) Can I access the call stack directly? (I did some implementation that holds the collection used as FILO but I need to handle the calls - what is a bad thing)
|
|
|
|
|
|
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377477 is a reply to message #377369] |
Tue, 23 December 2008 03:40   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I really don't see where you're coming from here.
Do you feel Oracle should provide a subtype for every single value returned by a function or procedure owned by SYS?
The return length is documented as being up to 2000 chrs.
If you need references to this retun size in multiple places and are worried about the overhead of amending them then create your own subtype.
If your solution is bug-prone, I'd venture to suggest that Oracle cannot be the only people responsible for this.
|
|
|
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377545 is a reply to message #377477] |
Tue, 23 December 2008 07:31   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
JRowbottom wrote on Tue, 23 December 2008 10:40 |
Do you feel Oracle should provide a subtype for every single value returned by a function or procedure owned by SYS?
|
Would be nice to define local_variable DBMS_SQL.FORMAT_CALL_STACK%RETURNTYPE.
JRowbottom wrote on Tue, 23 December 2008 10:40 | If your solution is bug-prone, I'd venture to suggest that Oracle cannot be the only people responsible for this.
|
My code is bug-prone because I need to add a call to "init" and "finalize" within every module. If there would be a way to add them automatically - I would skip that "bug-prone" label.
Example problem that occured: "finalize" was not called when an exception occured (that kind of problems is usually easy to find). Some example of my code:
PROCEDURE p IS
BEGIN
init('p'); -- BUG1 BUG2
(some code)
finalize; -- BUG3
WHEN OTHERS THEN finalize;raise; -- BUG4
END p;
Possible BUG1: developer can forget about that part
Possible BUG2: developer needs to specify name of that procedure every time (easy to forget when you do copy/paste)
Possible BUG3: developer can forget about that part
Possible BUG4: developer can forget about that part
For example possible BUG2 is easy to solve in PERL - as you can get the name of current/caller module.
Other problems are difficult to skip - you would solve them in a different way in languages other than Oracle.
Please correct me if I'm wrong or give me a better solution to track the code (note that Oracle's call stack does not specify the module name - it specifies only the "global object" name and code line)
|
|
|
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377557 is a reply to message #377545] |
Tue, 23 December 2008 08:32   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you want named subtypes to use in pl/sql, just create a typres package header like this:create or replace package pkg_types as
subtype dbms_utility_return_type is varchar2(2000);
end pkg_types;;
/
declare
v_string pkg_types.dbms_utility_return_type;
begin
null;
end;
/
As to your developers memory problems, have to tried using code tamplates(as TOAD calls them) or snippets (as SQL Developer calls them). Create a template Function and Procedure, with the calls to init and finalize already in it, in such a fashion that it won't compile until it's been modified with the correct name in the call to INIT
You could also create a view on USER_SOURCE to show you which function/procedures in packages are missing calls to INIT or FINALIZE. Here's a sample to show how you might do it:select prev_proc_start procedure
,max(got_init) got_init
,max(got_finalize) got_finalize
from (
select line,prev_proc_start
,max(case when regexp_like(text,'INIT\('''||prev_proc_start||'''\)') then 1 else 0 end) over (partition by prev_proc_start order by line) got_init
,max(case when regexp_like(text,'FINALIZE;') then 1 else 0 end) over (partition by prev_proc_start order by line) got_finalize
from (
select line
,upper(text) text
,last_value(case when regexp_like(upper(Text),'PROCEDURE|FUNCTION') then trim(regexp_substr(upper(trim(text)),' [^ ]+')) else null end ignore nulls) over (order by line) prev_proc_start
from user_source
where name = 'PKG_TYPES'
and type = 'PACKAGE BODY' ))
group by prev_proc_start;
|
|
|
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377597 is a reply to message #377557] |
Tue, 23 December 2008 14:49  |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
I was not aware that I can define a subtype like this. Although this seems to be a big overhead - where my conclusion is that PL/SQL is not complete. Many things are missing.
I am using templates. But parsing the code every time is an overhead again.
Thanks for your answers
|
|
|