I'm writing a procedure to that takes a varying number of parameters and populates a global temporary table with the results if a dynamic query. I want to know how many rows are in the GTT after the insert, done using dbms_sql.execute. Using dbms_sql.last_row_count seems to work, but the documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sql.htm#i1026354 says "Call this function after a FETCH_ROWS or an EXECUTE_AND_FETCH call. If called after an EXECUTE call, then the value returned is zero."
A much simplified test case is:
CREATE GLOBAL temporary TABLE bc_test_gtt
(col1 NUMBER(1))
ON COMMIT DELETE ROWS;
DECLARE
nSqlCursor PLS_INTEGER;
nReturn PLS_INTEGER;
sSql VARCHAR2(100);
BEGIN
sSql := 'INSERT INTO bc_test_gtt SELECT 1 FROM dual';
nSqlCursor := dbms_sql.open_cursor;
dbms_sql.parse
(nSqlCursor
,sSql
,dbms_sql.native
);
nReturn := dbms_sql.execute(nSqlCursor);
dbms_output.put_line(TO_CHAR(dbms_sql.last_row_count)||' last_row_count rows');
dbms_output.put_line(TO_CHAR(SQL%ROWCOUNT)||' sql%rowcount rows');
dbms_sql.close_cursor(nSqlCursor);
END;
/
1 last_row_count rows
sql%rowcount rows
select * from v$version;
BANNER
________________________________________________________________________________
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 ; Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I would like to know if it is OK to use last_row_count in this scenario? Could its behaviour change in the future, leading to misleading results. Due to the doubts I have I might have to just to SELECT COUNT(*) from bc_test_gtt; to get my result, but that seems like unnecessary use of SQL
Background: I'm storing a copy of contact information in a denormalised, cleaned up structure. When a user enters a new client in the application, my procedure will return any potential duplicates, based on the contact information that the user has entered.
Thank you,
Ben