Re: Dynamic sql in inserting data !!!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 22 Jan 2000 12:24:53 -0500
Message-ID: <jjpj8s0s93jtgdkche7ibulql9kp5uj3nk@4ax.com>

A copy of this was sent to kamlesh.kumar_at_churchill.co.uk (if that email address didn't require changing) On Sat, 22 Jan 2000 14:58:37 GMT, you wrote:

>Hi All
>
>I have some problem with dynamic sql in case off inserting data in
>a table . I wants to use a ver.
>But giving error like this :-
>
>DECLARE
>str VARCHAR2(100);
>cid number ;
>feed number;
>field varchar2(20);
>begin
>field := 'Kamalesh';
> str:= 'insert into tmp values ('||field||')';
>cid := DBMS_SQL.OPEN_CURSOR;
>DBMS_SQL.PARSE(cid,str ,dbms_sql.v7);
>feed := DBMS_SQL.EXECUTE(cid);
>EXCEPTION
>WHEN OTHERS THEN
>DBMS_OUTPUT.PUT_LINE('Err '||sqlerrm);
>end;
>

that builds the insert statement:

insert into tmp values ( Kamalesh );

It needs to either

build the insert statement "insert into tmp values ( :1 )" and then bind Kamalesh to the bind variable (PREFERED, CORRECT method)
build the insert statment "insert into tmp values ( 'Kamalesh' )" (suboptimal, not recommended method)...

This is the way to code this:

ops$tkyte_at_8i> DECLARE

  2          str                VARCHAR2(100);
  3          cid                number ;
  4          feed               number;
  5          field              varchar2(20);
  6  begin
  7          field := 'Kamalesh';
  8          str:= 'insert into tmp values (:bv1)';
  9          cid := DBMS_SQL.OPEN_CURSOR;
 10          DBMS_SQL.PARSE(cid,str ,dbms_sql.v7);
 11          dbms_sql.bind_variable( cid, 'bv1', field );
 12          feed := DBMS_SQL.EXECUTE(cid);
 13  EXCEPTION
 14          WHEN OTHERS THEN
 15          DBMS_OUTPUT.PUT_LINE('Err '||sqlerrm);

16 end;
17 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> select * from tmp;

Kamalesh

>
>
>Err ORA-00984: column not allowed here
>
>Please let me know what can i do in this case .
>
>
>Thanks
>Kamalesh
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jan 22 2000 - 11:24:53 CST