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

    Dynamic SQL insert and dbms_sql.last_row_count

    bencol
      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
        • 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
            bencol
            Thank you Kim, should have read the documentation for the stuff I thought I knew first!