Re: using execute immediate for create table and then insert

From: Slawomir Cichy <slawas_at_wp-sa.pl>
Date: Mon, 02 Mar 2009 16:25:09 +0100
Message-id: <49ABFA55.2030207_at_wp-sa.pl>



Kumar Madduri pisze:
> Hi All
> This does not work when I put the execute immediate and insert in the
> same block (which I hashed). But if I execute the execute immediate
> portion in another anyonymous block, it works fine (both the create
> and the insert). When I try to put them to gether like below, the
> insert tries to use xxdl_po_vendors_enc and says it cannot find the
> table. Is it because the pl/sql block does not know about the table at
> compile time? Is there any workaround for this ?
>
>
> declare
> v_create_table varchar2(4000);
> cursor po_vendor_enc is
> select vendor_id, vendor_name, nvl(num_1099,'99-9999999')num_1099,
> attribute15,abs(dbms_crypto.RANDOMINTEGER) random_num_1099
> from po_vendors
> where attribute15 is null;
> begin
> --execute immediate 'create table XXDL.xxdl_po_vendors_enc (vendor_id
> number, vendor_name VARCHAR2(240), NUM_1099 varchar2(30),
> NUM_1099_enc varchar2(30),attribute15 varchar2(150))'||' tablespace
> xxdld ';
> for i in po_vendor_enc
> loop
> insert into xxdl.xxdl_po_vendors_enc(vendor_id, vendor_name,
> num_1099, num_1099_enc, attribute15)
> values ( i.vendor_id, i.vendor_name, i.num_1099,
> i.random_num_1099, i.attribute15);
> end loop;
> end;
>
> Thank you
> - Kumar

 declare
 v_create_table varchar2(4000);
 cursor po_vendor_enc is
 select vendor_id, vendor_name, nvl(num_1099,'99-9999999')num_1099, attribute15,abs(dbms_crypto.RANDOMINTEGER) random_num_1099  from po_vendors
 where attribute15 is null;
  begin

     execute immediate 'create table XXDL.xxdl_po_vendors_enc (vendor_id number, vendor_name VARCHAR2(240), NUM_1099 varchar2(30), NUM_1099_enc varchar2(30),attribute15 varchar2(150))'||' tablespace xxdld ';   for i in po_vendor_enc
    loop
      execute immediate 'insert into xxdl.xxdl_po_vendors_enc(vendor_id, vendor_name, num_1099, num_1099_enc, attribute15)

    values ( :vendor_id, :vendor_name, :num_1099, :random_num_1099, :attribute15)' using i.vendor_id, i.vendor_name, i.num_1099, i.random_num_1099, i.attribute15;   end loop;
  end;

Slawas

"WIRTUALNA POLSKA" Spolka Akcyjna z siedziba w Gdansku przy ul. Traugutta 115 C, wpisana do Krajowego Rejestru Sadowego - Rejestru Przedsiebiorcow prowadzonego przez Sad Rejonowy Gdansk - Polnoc w Gdansku pod numerem KRS 0000068548, o kapitale zakladowym 67.980.024,00 zlotych oplaconym w calosci oraz Numerze Identyfikacji Podatkowej 957-07-51-216.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 02 2009 - 09:25:09 CST

Original text of this message