Re: using execute immediate for create table and then insert
- From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Mon, 2 Mar 2009 13:45:30 +0100
Yes: also embed the insert-statement inside a execute immediate.
On Mon, Mar 2, 2009 at 1:38 PM, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
> 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
>
--
Toon Koppelaars
RuleGen BV
+31-615907269
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
(co)Author: "Applied Mathematics for Database Professionals"
Other related posts:
