Re: EXECUTE IMMEDIATE and dynamic inserts

Hi,

Your statement is not correct, as you use twice v_Fields, which contains the names of the column. You should have typed v_Values instead. But even with this, it won't work, as the variable names have no meaning when the SQL is parsed before execution. You should instead: - use parameters, with the form execute immediate '....' using ... - or place in your SQL statement the values (and not the names of the variable where the values are) you wish to insert, but take care of the type of data (you may need to insert ' in your string).

Regards,

"Christian" <ccote_msl_at_yahoo.com> wrote in message news:992a9b5a.0201110409.18769c60_at_posting.google.com...
> 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;
> 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
Received on Fri Jan 11 2002 - 06:24:38 CST