When getting the data from the temporary table of Oracle defined in the way as below, you may run into the error “ORA-08103: object no longer exists on using setfetchsize of Hirabenate”
CREATE GLOBAL TEMPORARY TABLE OMWB_TEST
(
COL01 VARCHAR2(255 BYTE)
,COL02 VARCHAR2(255 BYTE)
)
ON COMMIT DELETE ROWS;
Cause Analysis:
This issue is possibly caused by the architecture difference. In PowerBuilder application (client/server), once the client establishes the connection to the database, an unique session will be created in database and maps to the application session, this is so-called long connection. So it's the one-to-one relationship. However, on Web application (browser/server), it uses the connection cache to connect to the database, one Appeon IE session maps to more than one session in the database; when data retrieval or procedure is executed, the cache pool will automatically allocate a connection to connect to the database. When transaction is completed, the connection will be rolled back to the cache pool, this is so-called short connection. One Web application may have many transactions, so it's the one-to-many relationship.
Furthermore, the data in a global temporary table is private, such that the data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction. In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
Solution:
Please try to work around this issue by changing the definition of GLOBAL TEMPORARY TABLE OMWB_TEST as below:
CREATE GLOBAL TEMPORARY TABLE OMWB_TEST
(
COL01 VARCHAR2(255 BYTE)
,COL02 VARCHAR2(255 BYTE)
)
ON COMMIT PRESERVE ROWS;
If it cannot resolved by the above solution, kindly please avoid using the temporary table.