Hi ,
I have a procedure for transaction search which will be called from .NET. The data should be taken from remote database.
We are storing the rowset from remote table in GTT and then get the data from GTT.
First I created GTT using option
CREATE GLOBAL TEMPORARY TABLE FT_HDR_GTT (
_DIVN_NBR NUMBER(5) ,
DEPT_NBR NUMBER(6) ,
VND_NBR NUMBER(5) ,
NUM_AUDITS NUMBER(6),
NUM_AUDITS_WHEN_REMOVED NUMBER(6)
)
on commit delete rows;
PROCEDURE GET_ACC_LIST(
parameters )
AS
V_SQL CLOB;
V_SQL_GTT CLOB;
V_CURSOR_GTT BINARY_INTEGER;
V_EXECUTE_GTT BINARY_INTEGER;
V_CURSOR BINARY_INTEGER;
V_EXECUTE BINARY_INTEGER;
BEGIN
V_SQL_GTT := ' INSERT INTO FT_HDR_GTT select * from FT_HDR@databaselink ';
V_CURSOR_GTT := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(V_CURSOR_GTT, V_SQL_GTT, DBMS_SQL.NATIVE);
/* bind variables */
V_EXECUTE_GTT := DBMS_SQL.EXECUTE(V_CURSOR_GTT);
V_SQL := ' select data from FT_HDR_GTT and local tables WHERE 1=1 ' ;
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
/* Dynamically specify the Bind Variables based on which input values the user wants to search on: */
v_execute := DBMS_SQL.EXECUTE(v_cursor);
o_accuracy_cur := DBMS_SQL.TO_REFCURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
/* raise exception*/
END GET_ACC_LIST;
When this procedure is called from .NET , it was raising error ORA-08103 object no longer exists. .NET side they are not doing database commit. They are just closing the connection after use.
Is this a normal behaviour? Or am I doing anything wrong?Please help me on this
To fix the issue, changed the GTT definition to
CREATE GLOBAL TEMPORARY TABLE FT_HDR_GTT (
_DIVN_NBR NUMBER(5) ,
DEPT_NBR NUMBER(6) ,
VND_NBR NUMBER(5) ,
NUM_AUDITS NUMBER(6),
NUM_AUDITS_WHEN_REMOVED NUMBER(6)
)
on commit preserve rows;
It worked fine. But i had to delete the rows from GTT every time the procedure is called to delete the rows from previous call.