Skip to Main Content
  • Questions

  • ORA-64610: bad depth indicator with Utl_Call_Stack

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Navinth.

Asked: November 14, 2018 - 9:51 am UTC

Last updated: November 26, 2018 - 9:32 am UTC

Version: Oracle 12.2

Viewed 1000+ times

You Asked

Hi,


I have a database in Oracle 12.2.0


There I have deployed a PL/SQL logic which is called from an update trigger.

From this logic, following code segment is called to get the format call stack.


FUNCTION Format_Stack___ RETURN VARCHAR2

   IS

      call_stack_ VARCHAR2(32000);

      depth_ PLS_INTEGER := Utl_Call_Stack.Dynamic_Depth();

   BEGIN

      FOR i_ IN REVERSE 1 .. depth_ LOOP

         call_stack_ := call_stack_ || UTL_Call_Stack.Concatenate_Subprogram(UTL_Call_Stack.Subprogram(i_)) || ' at line ' || To_Char(UTL_Call_Stack.Unit_Line(i_)) || chr(10);

      END LOOP;

      RETURN stack_;

   END Format_Stack___;



Running this code segment raises the ORA-64610: bad depth indicator error with an error stack similar to below.

With the error:

ORA-64610: bad depth indicator
ORA-06512: at line 16
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2186
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2304
ORA-06512: at "APPOWN.ERROR_SYS", line 128
ORA-06512: at "SYS.UTL_CALL_STACK", line 99
ORA-06512: at "APPOWN.ERROR_SYS", line 113
ORA-06512: at "APPOWN.ERROR_SYS", line 119
ORA-06512: at "APPOWN.ERROR_SYS", line 323
ORA-06512: at line 3
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2300
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2309
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2159
ORA-06512: at "APPOWN.EVENT_ACTION_API", line 2192
ORA-06512: at "APPOWN.EVENT_SYS", line 199
ORA-06512: at "APPOWN.EVENT_SYS", line 199
ORA-06512: at "APPOWN.ORD_LINE_TR1", line 14
ORA-04088: error during execution of trigger 'APPOWN.ORD_LINE_TR1'
ORA-06512: at "APPOWN.ORD_LINES_API", line 4208131679
ORA-06512: at "APPOWN.ORD_LINES_API", line 29721
ORA-06512: at "APPOWN.ORD_LINES_API", line 30769
ORA-06512: at "APPOWN.ORD_LINES_API", line 30778
ORA-06512: at "APPOWN.ORD_LINES_API", line 22813
ORA-06512: at "APPOWN.ORD_LINES_API", line 22857
ORA-06512: at "APPOWN.ORD_LINES_API", line 22864


Quite strangely, sometimes issue gets solved when the code segment is changed to below (only sometimes)

(only change here is call stack is merged in two lines)


FUNCTION Format_Stack___ RETURN VARCHAR2

   IS

      call_stack_ VARCHAR2(32000);

      depth_ PLS_INTEGER := Utl_Call_Stack.Dynamic_Depth();

   BEGIN

      FOR i_ IN REVERSE 1 .. depth_ LOOP

         call_stack_ := call_stack_ || UTL_Call_Stack.Concatenate_Subprogram(UTL_Call_Stack.Subprogram(i_)) ;

         call_stack_ := call_stack_ || ' at line ' || To_Char(UTL_Call_Stack.Unit_Line(i_)) || chr(10);

      END LOOP;

      RETURN stack_;

   END Format_Stack___;




I tried different articles in Oracle community & support. Most of them with this bad depth error was related UTL_SMTP.

Didn't find a good article on an error similar to this in UTL_Call_Stack.

Your valuable thoughts on this are welcome.


Thank You.

Navinth

and Chris said...

You get the ORA-64610 error when the utl_call_stack procedures try to access a non-existent stack position. For example:

begin
  dbms_output.put_line ( 
    utl_call_stack.unit_line ( 100 )
  );
end;
/

ORA-64610: bad depth indicator


There is a bug in 12.2 that raises this error (See MOS 2419817.1). But I'm not convinced this is the cause of your issue, as it relates to calling backtrace_unit.

We need a test case to see exactly when you get this exception though.

It's worth checking with support in any case.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Navinth Bakmeedeniya, November 26, 2018 - 4:43 am UTC

Thanks for the reply.

We are in the process of creating a test case which seems bit hard.

One more observation we have seen is, sometimes the issue gets solved when we recreate the package object in a new memory location. Not by simply recreating it using create or replace statement but using the following steps.

Rename the package to something else -> create the package with the original name -> drop the renamed package.

Do you have any comment on this behavior?

Thanks & Best Regards,
Navinth
Chris Saxon
November 26, 2018 - 9:32 am UTC

I'm not sure what's going on here. You'll have to take this up with support.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library