Problems reconnecting thru embedded SQL statements after a kill session is issued.

From: <mgresham_at_vnet.ibm.com>
Date: 1995/07/17
Message-ID: <3ue99i$v9g_at_locutus.rchland.ibm.com>#1/1

We are trying to reconnect to Oracle thru embedded SQL statements after doing an alter system kill session and appear to be having problems. First, I initially connect to the database by issuing the following SQL statements:

        EXEC SQL DECLARE dbname DATABASE;
        EXEC SQL CONNECT :userid IDENTIFIED BY :passwd AT dbname USING :dbstring;

Upon completion SQLCODE = 0 and we are connected. From an administrative sessIon I can issue a SELECT from V$SESSION and see my session. I then issue the alter system kill session command. The SELECT will come back and tell me the session has a status of killed. I then try to perform another SQL command to Query:

        EXEC SQL AT dbname DECLARE QRYSTMT STATEMENT;
        EXEC SQL PREPARE QRYSTMT FROM:querytabs;

I receive what I expect which is first the ORA-00028: your session has been killed, then a ORA-01012: not logged on. From the V$SESSION I can see the row is removed for the killed session.
From here we issue the same CONNECT statements to RE-Connect to the database:

        EXEC SQL DECLARE dbname DATABASE;
        EXEC SQL CONNECT :userid IDENTIFIED BY :passwd AT dbname USING :dbstring;

For this operation and all subsequent calls to CONNECT the SQLCODE = -1075, currently logged on. However, when I try to do another query I receive the same error message from above telling me we are not logged on (ORA-01012). The CONNECT will continually issue the "currently logged on" and the QUERY will continually issue the "not logged on".

Is there something I need to do inorder to RE-Connect this way? Or to get the CONNECT to realize I am no longer logged on?

Any help would be appreciated!, Thanks in advance. Marie Gresham: IBM   Received on Mon Jul 17 1995 - 00:00:00 CEST