Re: EXECUTE IMMEDIATE and dynamic inserts

In article <992a9b5a.0201110409.18769c60_at_posting.google.com>, ccote_msl_at_yahoo.com says...
>
>HI,
>I want to use NDS to execute this kind of DML :
>
>v_Fields VARCHAR2(25);
>v_Values VARCHAR2(50);
>v_SqlStmt VARCHAR2(100);
>acct_Rec SCOTT.Account%ROWTYPE;
>
>BEGIN
> v_Fields := 'BALANCE, ACCOUNTNO';
> v_Values := 'acct_Rec.BALANCE, acct_Rec.ACCOUNTNO';
>
> v_SqlStmt := 'INSERT INTO SCOTT.ACCOUNT ('||v_Fields||') VALUES
>('||v_Fields||')';
>
>EXECUTE IMMEDIATE v_SqlStmt;
>END;
 execute immediate 'insert into scott.account( ' || v_fields || ' ) values

                    ( :b1, :b2 )' using acct_rec.balance, acct_rec.accounting;

I beg of you -- USE BIND VARIABLES. See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2444907911913

and an example. depending on your needs, you MAY need to use DBMS_SQL (if you do not know the number of binds at COMPILE TIME). Using dbms_sql you can achieve two great gains:

1. you can parse the insert ONCE per session instead of once per insert
2. you can procedurally bind the values in at runtime without having to know the number or types of binds at compile time.

> I get the following error : "ORA-00984: column not allowed here"
>
>I need to dynamically generate the fields values and their values. I
>know that I can do it in Visual Basic easily. Is there a way to
>accomplish this in PL/SQL?
>
>Thank you for your help,
>Christian

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp