Re: using execute immediate for create table and then insert
- From: Slawomir Cichy <slawas@xxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Mon, 02 Mar 2009 16:25:09 +0100
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
