5 Replies Latest reply on Jul 19, 2013 10:52 AM by Mahir M. Quluzade

    Rows affected from Dynamic SQL

    AbhiShah


      Hi,

      VERSION - Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

      I want the count of rows affected in Dynamic SQL as shown in below code where insert statement will be repeated for each record in CURSOR.

       

      for rec in c1
         loop

         
           abc:='insert into test
           select a.*,'||rec.OP_ID||','''||rec.OP_NAME||''' from BL_testI'||rec.OP_ID ||' a where      START_DATE>=trunc(sysdate-2) and START_DATE < trunc(sysdate-1)' ;

           insert into str_test values (abc);

           execute immediate abc ;

           commit;
          
           
           exit when c1%notfound;
         end loop;

       

      In normal query I would do it by SQL%COUNT but can't use it here.

       

      Thnx in advance

        • 1. Re: Rows affected from Dynamic SQL
          Mahir M. Quluzade

          Please try

           

           

          declare
            l_sql varchar2(1000);
            l_cnt number;
            ....
          
             begin
            ...
          
             for rec in c1
             loop
            
               abc:='insert into test select a.*,'||rec.OP_ID||','''||rec.OP_NAME||''' from BL_testI'||rec.OP_ID ||' a where  START_DATE>=trunc(sysdate-2) and START_DATE < trunc(sysdate-1)' ;
               execute immediate abc;
               dbms_output.put_line('Rows = '||TO_CHAR(SQL%ROWCOUNT));
          
               --- or
              
               execute immediate 'begin ' || l_sql || '; :x = sql%rowcount; end;' using OUT l_cnt;
               dbms_output.put_line('Returned Rows = '||l_cnt);
             end;
          

           

           

          Regards

          Mahir M. Quluzade

          • 2. Re: Rows affected from Dynamic SQL
            Karthick2003

            Just use SQL%ROWCOUNT. Whats the problem with that?

             

            Here is an example.

             

            SQL> declare
              2    l_sql varchar2(4000);
              3  begin
              4    l_sql := 'insert into t select * from emp';
              5    execute immediate l_sql;
              6    dbms_output.put_line(l_sql || ' : Number of row Inserted = ' || to_char(sql%rowcount));
              7  end;
              8  /


            insert into t select * from emp : Number of row Inserted = 11

             

            PL/SQL procedure successfully completed.

             

            SQL>

            • 3. Re: Rows affected from Dynamic SQL
              pollywog

              also unsure if you are just testing but if not get that commit out of your loop

              • 4. Re: Rows affected from Dynamic SQL
                AbhiShah

                Threw run time error

                 

                BEGIN i_sms3; END;
                Error at line 1
                ORA-06550: line 1, column 7:
                PLS-00103: Encountered the symbol ";" when expecting one of the following:

                   begin case declare exit for goto if loop mod null pragma
                   raise return select update while with <an identifier>
                   <a double-quoted delimited-identifier> <a bind variable> <<
                   close current delete fetch lock insert open rollback
                   savepoint set sql execute commit forall merge
                   <a single-quoted SQL string> pipe
                The symbol "case was inserted before ";" to continue.
                ORA-06550: line 1, column 26:
                PLS-00103: Encountered the symbol ";" when expecting one of the following:

                   . ( * @ % & - + / at mod rem when <an exponent (**)> and or
                   ||
                ORA-06512: at "ICB.I_SMS3", line 41
                ORA-06512: at line 1

                • 5. Re: Rows affected from Dynamic SQL
                  Mahir M. Quluzade

                  Can you  paste here your  PL/SQL code full?