Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic SQL insert and dbms_sql.last_row_count

bencolOct 9 2012 — edited Oct 9 2012
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
This post has been answered by Kim Berg Hansen on Oct 9 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 6 2012
Added on Oct 9 2012
2 comments
1,962 views