using execute immediate for create table and then insert

  • From: Kumar Madduri <ksmadduri@xxxxxxxxx>
  • To: Oracle-L@xxxxxxxxxxxxx
  • Date: Mon, 2 Mar 2009 04:38:51 -0800

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 ?

 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;
 --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
      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;

Thank you
- Kumar

Other related posts: