2 Replies Latest reply: Oct 9, 2012 5:38 AM by bencol RSS

    Dynamic SQL insert and dbms_sql.last_row_count

      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)) 
        nSqlCursor PLS_INTEGER; 
        nReturn    PLS_INTEGER; 
        sSql       VARCHAR2(100); 
        sSql := 'INSERT INTO bc_test_gtt SELECT 1 FROM dual'; 
        nSqlCursor := dbms_sql.open_cursor; 
        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'); 
      1 last_row_count rows 
      sql%rowcount rows 
      select * from v$version; 
      Oracle Database 11g Enterprise Edition Release - 64bit Production 
      PL/SQL Release - Production 
      CORE ;     Production 
      TNS for 64-bit Windows: Version - Production 
      NLSRTL Version - 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,

        • 1. Re: Dynamic SQL insert and dbms_sql.last_row_count
          Kim Berg Hansen
          True, documentation states last_row_count it should not be used after execute. So probably not safe to rely on this behaviour.

          But the docs for execute statement says:

          This function executes a given cursor. This function accepts the ID number of the cursor and returns the number of rows processed. The return value is only valid for INSERT, UPDATE, and DELETE statements; for other types of statements, including DDL, the return value is undefined and should be ignored.
          Returns number of rows processed

          And this is from the package spec:
            function execute(c in integer) return integer;
            --  Execute the given cursor and return the number of rows processed
            --  (valid and meaningful only for INSERT, DELETE or UPDATE statements;
            --  for other types of statements, the return value is undefined and
            --  should be ignored).
            --  Input parameters:
            --    c
            --      Cursor id number of the cursor to execute.
            --  Return value:
            --    Number of rows processed if the statement in the cursor is
            --    either an INSERT, DELETE or UPDATE statement or undefined otherwise.
          So it should be documented that you simply can use the nReturn value ;-)
          • 2. Re: Dynamic SQL insert and dbms_sql.last_row_count
            Thank you Kim, should have read the documentation for the stuff I thought I knew first!