Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_UTILITY.FORMAT_CALL_STACK length (Oracle 10.2G)
icon5.gif  DBMS_UTILITY.FORMAT_CALL_STACK length [message #377048] Fri, 19 December 2008 11:07 Go to next message
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 #377052 is a reply to message #377048] Fri, 19 December 2008 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58913
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) As per documentation VARCHAR2(2000)
2) No where in documentation
3) No
4) SQLERRM gives the last message, FORMAT_ERROR_BACKTRACE gives the whole error stack
5) Only through FORMAT_CALL_STACK

Regards
Michel
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377064 is a reply to message #377048] Fri, 19 December 2008 13:47 Go to previous messageGo to next message
rputtagunta
Messages: 5
Registered: March 2008
Location: Texas, USA
Junior Member
This link would be of interest.

http://www.orafaq.com/forum/t/60583/0/

Please note the Tom Kyte link that Barbara has given you.
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377369 is a reply to message #377048] Mon, 22 December 2008 09:51 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Thanks for your answers.
It seems that I'm forced to use hard-coded type - what seems to be a sad information.
For the call stack I am currently using my own code (I need to call initialize/finalize procedures at the start/end of every module what is bug-prone solution). Maybe one day Oracle would implement better solution for the things. Confused
Re: DBMS_UTILITY.FORMAT_CALL_STACK length [message #377477 is a reply to message #377369] Tue, 23 December 2008 03:40 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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 Smile
Previous Topic: Instance lockup on GRANT to table
Next Topic: ORA-00933 error message
Goto Forum:
  


Current Time: Wed Aug 27 20:34:48 CDT 2014

Total time taken to generate the page: 0.13471 seconds