Hi

I have seen this in several occasions and the same solution worked for me. I was unable to report this to Oracle because problem is not re-creatable on demand.

BR

Savinda

Hi Savinda,

Thanks for your reply. What version of Oracle are you using?

Regards,

Erik

Hi,

It's 12.2.0.1.0 with WINDOWS DB BUNDLE PATCH 12.2.0.1.200114(64bit):30446296

/Savinda

Hi, 

we have experienced same and we were likewise informed by support to recreate the package which solved the problem. 
 

have not encountered it since. 

Hi Julian,

Thanks for your response. We have only just tried the re compilation so to early to say if it has offered a permanent fix. Up until now it has been intermittently been appearing over a period of 6 months across 3 different databases…

We are soon planning to upgrade to Oracle 19, maybe that will help.

We basically did the Oracle 19c upgrade immediatly after. 

We reported the bug to IFS and got this response in minutes.  We had the patch in 2 days.  We are running IFS Apps10 UPD6 (this problem only started after our UPD6 upgrade) running “Product: Oracle database - (enterprise edition) Product Version : (12.2.0.1.0) Product language: (English) Operation system/version: Microsoft Windows Server 2016 (64 bit) version 1607 (OS Build 14393.3504)” at the time

Thank you for contacting IFS Global Support and requesting our assistance.
This is caused by a bug in Oracle database. You need to apply a Windows DB Bundle Patch to correct it. Could you please send me the following details? see the example below. 

Product: Oracle database -( enterprise edition/Standard Edition)
Product Version : (12.1.0.1)
Product language: (English)
Operation system/version : (Microsoft Windows x64 (64 bit) 2008 R2)

Hi Erik  (@magnum80 ),

I have worked in a similar case which was reported to IFS. I think commenting my detail findings will be helpful. Some of the wording little bit similar to the answer you have already received. 

The “ORA-64610: bad depth indicator” error was recreated using a custom menu and “Execute online SQL” event action in one of the IFS internal environment. After around 3 hours the oracle error was unabled to recreate. It’s hard to say how the issue was resolved. The same steps were followed to recreate the issue in the different IFS internal environments. But I was unable to recreate the issue as on demand. 

First part of this reply I write summary of my investigation. Latter part of this reply I write little bit more detail.

------------------ Summary of My investigation ------------------ 

After discussing with the IFS experts who have seen this issue and with my own investigations, I can summarize the investigations to the below point. 

• Our aim was to find a workaround without applying the oracle patch for this issue since the oracle patch introduces side effects.
• Some scenarios the developers could be able to avoid the bad depth error by creating two custom events. The first custom event to implement the required functionality and the second custom event to post a background job to execute the first custom event. But if you wish to make a pop-up message like error message or warning then this method is not suitable. (Ex: This is not suitable for below PLSQL block)
• Some scenarios the developers could be able to avoid the error by recompiling the affected IFS package in the database.
• Some scenarios the developers could be able to avoid the error by dropping and re-deploying the affected IFS package in the database.  But this may revoke the granted permissions to the package and may introduce additional work.
• Some scenarios the developers mentioned that the issue is solved without doing any additional work. (Error has disappeared without any known reason)
• Most of the cases the developers could be able to avoid the error by adding the debug information to the affected IFS package in the database. This is the suggested solution, since there is not a permanent fix at the moment. 
• The nature of the error is intermittent. Hence, it’s impossible to report to Oracle support, as there is not a proper test plan to recreate the issue. 
• There is a 9-digit, or 10-digit number is shown in the error stack by Oracle which is incorrect (Ex: ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 2005732968). This incorrect value line number changes when executed from a different Oracle session, but it is still either a 9-digit or 10-digit number according to what we have seen so far. It seems that sometimes metadata related to these errors are getting saved into unreachable memory locations (Stack) in Oracle database. That seems to be the root cause for this error.

The “ORA-64610: bad depth indicator” error was recreated using a custom menu and “Execute online SQL” event action in one of the IFS internal environment. 

------------------ Below SQL block used to recreate the issue ------------------

DECLARE BEGIN Error_SYS.Appl_General('CustomerOrderLine', 'WARNING: This is a warning message.'); END;

 
Then Application throw below oracle exception. 

------------------ Error ------------------
ORA-64610: bad depth indicator ORA-06512: at line 16 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2184 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2304 ORA-06512: at "IFSAPP.ERROR_SYS", line 136 ORA-06512: at "SYS.UTL_CALL_STACK", line 99 ORA-06512: at "IFSAPP.ERROR_SYS", line 121 ORA-06512: at "IFSAPP.ERROR_SYS", line 127 ORA-06512: at "IFSAPP.ERROR_SYS", line 331 ORA-06512: at line 3 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2300 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2309 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2159 ORA-06512: at "IFSAPP.FND_EVENT_ACTION_API", line 2192 ORA-06512: at "IFSAPP.EVENT_SYS", line 199 ORA-06512: at "IFSAPP.EVENT_SYS", line 199 ORA-06512: at "IFSAPP.SANMLK_CUST_ORDER_LINE_EVU", line 10 ORA-04088: error during execution of trigger 'IFSAPP.SANMLK_CUST_ORDER_LINE_EVU' ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 2005732968 ORA-06512: at "IFSAPP.CUSTOMER_ ORDER_LINE_API", line 29405 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 30369 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 22358 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 22405 ORA-06512: at "IFSAPP.CUSTOMER_ORDER_LINE_API", line 22420 ORA-06512: at line 10

------------------ Explanation of the error ------------------
Error_sys package (which is IFS code) uses 3 methods in Utl_Call_Stack dictionary package (which is Oracle code) namely UTL_Call_Stack.Unit_Line, UTL_Call_Stack.Subprogram & Utl_Call_Stack.Dynamic_Depth to format error texts in IFS application. ORA-64610 error is thrown when one of the above Utl_Call_Stack procedures try to access a non-existent stack position. 

As mentioned in the above error, the error stack shows a 10-digit number as the line number. CUSTOMER_ORDER_LINE_API has nearly 43400 lines (by 11th-June-2020) of code so line number 2005732968 in the above stack shown by Oracle is incorrect. This incorrect value line number changes when executed from a different Oracle session but it is still either a 9-digit or 10-digit number according to what we have seen so far. It seems that sometimes metadata related to these errors are getting saved into unreachable memory locations (Stack) in Oracle database. That seems to be the root cause for this error.

------------------ What was the side effect introduced by the Oracle patch ------------------

"The oracle error "ORA-64610: bad depth indicator" can be solved by applying the " windows bundled patch 12.2.0.1.190716WINDBBP" which was suggested by the solution 264924.

But after applying the above fix as a side effect "Database processes has stopped".

Example scenario: The issue reported by the same customer. 

• Bad depth error reported by the case : G2061955. 
• Database processes has stopped, reported by the case : G2128097

------------------ Suggested Solutions ------------------

Showing this 9-digit or 10-digit number in error stack seems to be having some dynamic behavior.

• The issue seems to disappear when the package is recreated by dropping and re-deploying the affected IFS package in the database. It seems that recreating affected IFS package is solved the issue as it recreates packages in different memory locations.
• Adding debug information seems to be doing this without dropping and recreating the package and that is the solution we suggest trying.

If the same error can be seen for any other package or in other databases, please try adding debug info to those packages.
 

------------------ Why IFS unable to request a fix from Oracle ------------------
Both the Customers and IFS also experienced, the error is intermittent. It appears in a database, continues for some time and suddenly disappears without a known reason.  

When requesting a patch from Oracle Support, they always request a test plan to reproduce the issue in their own environments. It seems to be a mandatory thing when they report it to their development team to implement a patch. And the test plan should be valid (should reproduce the issue) for the whole time period when the SR is continuing. So, in this case we are getting stuck when preparing such a test plan since we currently do not know how to reproduce 10 digits number in the error stack. Further we do not have any internal database where this error can be seen on request.

As our R&D team has informed us, some time ago, they have found an internal database where this error can be seen. They have reported it to Oracle support. Without having a test plan to reproduce the issue, Oracle support has agreed to investigate the error within the R&D’s database via a Webinar. During the first session of the Webinar, issue has reproduced but not during the second session. (Error has disappeared without any known reason) The SR has not been continued further.

So, we are getting stuck when requesting a patch/permanent solution from Oracle Support and hope the situation can be understood. Currently we do not have an option other than recompiling the package or recreating package or adding debug information to the package to solve the error. Some scenarios adding two custom events solves the issue, but this is not suitable for throwing popup messages like errors or warnings. 
 

If anyone can add more inputs to reproduce the issue (10 digits number in error stack) it would be highly appreciated to continue this.

Thanks.
Kind regards,
Sandun Madola.

I’ve got similar case at customer’s test installation. Unfortunately adding debug info to packages did not help . I’ve also registered case (G2179884) but first answer was “contact you partner” so to solve problem I have to contact myself .

Are there any other possible solutions?

If necessary it’s possible to use test installation as reference for demonstrating problem to Oracle support.

Regards,

/PC

The only thing that helped us in the end was to upgrade to Oracle 19.

The “depth” is the the depth of the call stack. IFS uses this depth so security checks aren’t unnecessarily made at every level. Recompiling the packages rebuilds this ordering.

As a side note somewhat related to this issue, for custom packages, I recommend you do not set PLSQL_OPTIMIZE_LEVEL any higher than the default value of 2, since at level 3, the inlining of packages flattens the call stack and makes debugging far more difficult.

D

You can refer to below link for the original service request I reported to Oracle where you can get more info on this. (in which they included in the patch 27529661 in their BP 12.2.0.1.190716WINDBBP)

https://support.oracle.com/epmos/faces/SrDetail?srNumber=3-19511884961

Test case was in Oracle 12.2.0.1 on Microsoft Windows x64 (2016 Server)
 

The “ORA-64610: bad depth indicator” issue was resolved after applying the below oracle patches and compiled the objects.

Oracle Database Patch (30446296) version 12.2.0.1.200114Skip Headers
Oracle Database Oracle JavaVM Component 12.2.0.1.200114 on Windows 64bit (30525838)

Patches 30446296, 30525838 have been outdated with the APR2021 release. It is recommendable to apply the latest one (APR2021):

Patch 32392089:
https://updates.oracle.com/download/32392089.html

Patch 32427674:
https://updates.oracle.com/download/32427674.html

I ran into this issue while attempting to upgrade from 12.1.0.2.171017 to 12.1.0.2.190416 on Windows 64-bit, going from bundle patch 26720785 to bundle patch 29413116.

Running datapatch in the database itself after upgrading the software makes the error go away.

:: Assumptions:
:: Oracle is installed to D:\ORACLE\PRODUCT\12.1.0\DBHOME_1
:: The instance and service are called IFS9DEV4
:: The patch is unzipped to C:\DOWNLOADS\ORACLE_DATABASE\


set ORACLE_BASE=d:\oracle
set ORACLE_HOME=%ORACLE_BASE%\product\12.1.0\dbhome_1
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set PATH=%ORACLE_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch;%PATH%

net stop msdtc
net stop OracleServiceIFS9DEV4
net stop OracleVssWriterIFS9DEV4
net stop OracleOraDB12Home1TNSListener

cd /d "c:\Downloads\oracle_database\p29413116_121020_MSWIN-x86-64\29413116"

call opatch apply

net start OracleOraDB12Home1TNSListener
net start OracleVssWriterIFS9DEV4
net start OracleServiceIFS9DEV4
net start msdtc

cd /d "D:\oracle\product\12.1.0\dbhome_1\OPatch"
set ORACLE_SID=ifs9dev4
datapatch

We came across same Error after upgrading from Apps8 to Apps10 UPD9.

This was from a custom Event. Disabling it was getting rid of error message so it was surely repeatable.

Did not get much help from IFS Support on it as it was Custom Event.

Modified the custom event to use Error_Sys.Record_General to Command_SYS.Mail 

Solution is weird. We got this as a workaround by getting an email in place of an Error Alert. But ok for the timebeing until we find the root cause.