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 ?
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
Other related posts:
