Re: using execute immediate for create table and then insert

On Mon, Mar 2, 2009 at 1:38 PM, Kumar Madduri <ksmadduri_at_gmail.com> 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_at_RuleGen.com
www.RuleGen.com

(co)Author: "Applied Mathematics for Database Professionals"

--
http://www.freelists.org/webpage/oracle-l