Re: inserting rows within cursor loop

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 05 Feb 1999 13:52:42 GMT
Message-ID: <36baf6f5.7730766@192.86.155.100>

A copy of this was sent to "Stephen" <poffs_at_tteam.com> (if that email address didn't require changing) On Thu, 4 Feb 1999 14:27:23 -0500, you wrote:

>the result variable will hold the names of tables in my schema. in the code
>below it hoses up at the insert portion. it doesn't recognize RESULT as a
>variable it thinks RESULT is a table name. is there anything I can do?
>thanks. poffs_at_tteam.com
>
>
>Declare
>RESULT user_objects.object_name%type;
>result2 user_objects.object_name%type;
>
>
>cursor c1 is
> select substr(object_name,2,5) as plcde, object_name from user_objects
>where object_name like 'T%';
>
>begin
>
> for i in c1 loop
> result := i.object_name;
>result2:=i.plcde;
>insert into RESULT(read_date,planetcode,postnet_zipcode)
>select read_date,planetcode,postnet_zipcode
>from ext_report_t
>where substr(planetcode,3,5) = result2;
>end loop;
>commit;

You have to use dynamic sql to do this. An example might be:

declare
  RESULT user_objects.object_name%type;   result2 user_objects.object_name%type;

  cursor c1 is
   select substr(object_name,2,5) as plcde, object_name      from user_objects
    where object_name like 'T%';

  procedure execute_immediate( sql_stmt in varchar2 )   as
    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
  begin

    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

  end;   

begin

  for i in c1 loop
    result := i.object_name;
    result2:=i.plcde;
    execute_immediate( 'insert into ' || result || '

                        ( read_date,planetcode,postnet_zipcode)
                        select read_date,planetcode,postnet_zipcode
                          from ext_report_t
                         where substr(planetcode,3,5) = ''' || result2 || ''''
                      );

  end loop;
  commit;
end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Feb 05 1999 - 07:52:42 CST