Not Connected to <Schema>@DB, reconnect? Message again and again

SQL Navigator
1

I am using SQL Nav 5.1 and I started getting problem of late.

Whenever I try to compile/save something (create an index, compile a package) with SQLNav, it asks the question:
Not Connected to @DB, reconnect ?

I say ‘Yes’, the cursor turns busy and after some time it becomes normal. I try to run a SQl, it goes fine, I come back to Stored procedure editor and save the package, get the same error again. I can never save the package no matter howmany times I try to reconnect.

But this issue happens randomly (70% of the time) and sometimes it just works and other times not. I am not able to observe the pattern of when it works and when it does not.

Do you have any clues?

2

I had similar situations, it was because Oracle bugs, when I tryed to compile some code in stored objects, session was lost, sqlnav showed reconnect dialog. As I said it was Oracle errors.
Could you attach ddl of code with these errors, and what version of database you are using.

3

Thanks for your reply.

If it’s an Oracle bug, why would the same object/package get compiled withoutissues in SQL* Plus or SQL Developer?

I am in Oracle 9.2.0.

It does not happen with a particular package. When it happens, it happens withall the packages.

4

Krish, yes this doesn’t look like Oracle bug, in my sitution session was disconnected in one specified package.
Please turn on sqlnav tracing (preferences/session/tracing) and try to simulate this sitation, when this error occurs, please attache navXXXX.log from your profile directory. (Doc&Settings/Application Data/Quest …/Sqlnav…/navXXX.log)

Regards

5

Hi Krish,

In attachement navlog from Krish.
So i tryed to create this body on my database, an it created (i have 10g), of course without compilation. What can i say, as you saw, at the beginning of the log there is alter which enables plsql debugging, sqlnav has that error that enables debugging ,after connecting,even if you don’t want it. So try to disable this option using:

ALTER SESSION SET PLSQL_DEBUG=False

and then try create this package.

Also try following , connect sing sqlplus and enable debuging:

ALTER SESSION SET PLSQL_DEBUG=TRUE
and then try to create this package in sqlplus.

If your session will lost - this will be oracle bug. But now its to early to say :wink:

I will be waiting for results,

Regards Piter
navlog0000.zip (83 KB)

6

After setting ALTER SESSION SET PLSQL_DEBUG=False, and compiling the package in SQL Navigator, the issue disappeared.

Also setting ALTER SESSION SET PLSQL_DEBUG=true and compiling the package in Sql*Plus resulted in connection to database getting terminated.

7

Hi Krish,
It looks like it’s Oracle bug.
You can post service request on metalink.oracle.com , maybe they could help with that. I think they suggest patch database first - its normal for them :wink:

Regards!

8

Subject: ORA-3113 Unable To Compile Procedure With Debug Option
Doc ID: Note:1066326.6 Type: PROBLEM
Last Revision Date: 24-JUN-2005 Status: PUBLISHED

Problem Description:

In Procedure Builder, run a procedure or package not getting => showing uhichp
in interpreter at break point or any stack information, e.g., variable values
Stack reads something like : [1] Program Unit line 8

Reproduces with a basic procedure which is on the server.
A local procedure does not reproduce the problem and shows all stack information.

If this is a private procedure in a package then see Bug 669489

Try executing in SQL*Plus:
SQL> ALTER PACKAGE <package_name> COMPILE DEBUG;

Causes ORA-3113: END-OF-FILE ON COMMUNICATION CHANNEL

Problem Explanation:

Executed the following SQL statement in SQL*Plus to prevent the ORA-3113 error:

SQL> ALTER SESSION SET plsql_debug=FALSE;

This means that the procedure/package gets compiled without the debug option.
Without debug no stack information is generated and therefore Procedure Builder
does not show it.

The ORA-3113 error is generated because of Bug 593630, which is resolved by
application of Server Patch 8.0.5.1.

Additional problems may also affect a final solution:

For example:-

  1. The compiler may successfully compile some syntactically incorrect
    code. Moving code locally may help solve this problem.
    e.g., If the variable is not null and it is a complex datatype, then
    initialize the variable as “variable.item != 0”
    Reference: PLSQL Manual; chapter 8; page 4-30
  2. The compiler accepts undeclared variables
  3. It seems that in Procedure Builder, doing a Compile->Compile All does not
    save stack information while Save does.

Search Words:

PROCEDURE BUILDER STACK ORA-3113 COMPILE PACKAGE DEBUG

Solution Description:

After applying patch for 8.0.5.1.

Execute the following in SQL*Plus:
SQL> ALTER PACKAGE COMPILE DEBUG;

for every procedure or package.

This can be done by creating a script using user_objects

or

Resave every package in Procedure Builder.

Note: The user logged into the database must be the owner of the procedure
or package to see the stack information.