Re: HELP! DBMS_SQL.PARSE

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/23
Message-ID: <8dvn2f$h9t$1@nnrp1.deja.com>#1/1

In article <3900A515.53A859A0_at_wellesley.edu>, "John A. Pearson" <jpearson_at_wellesley.edu> wrote:
> Hello,
>
> I have been using this built-in package for a while
> to create and drop tables (DDL's) dynamically.
>
> However, now I want to use DBMS_SQL
> to pass an INSERT statement.
>
> I have had little luck.
>
> v_createstring := 'INSERT INTO TEMP
> ( FIELD_ONE,
> FIELD_TWO)
> VALUES ('||NUM_1||,
> ||NUM_2||' )'
>
> The values are variables within the package the this
> code is in.
>
> I have had luck with inserting only the first column.
>
> When I get the second column into the script
> I get the error message of ORA-00984: column not allowed here.
>
> Any ideas around this? ....
>
> If you do, please forward an example.
>
> Thanks in advance,
>
> john
>
>

You are missing quotes arounds the commas HOWEVER, your approach (concatenating in constants) is the *wrong* way to do this. It will not perform well, character strings will give you problems (quotes), it will not perform well, it is hard to do (the code to build these strings is hard to read), it will not perform well, you will limit the scalability of your application by trashing the shared pool, and lastly -- it will not perform well.

Here is an example showing your method and then an example showing the real way to do it with bind variables.

ops$tkyte_at_8i> create table t ( num1 int, num2 int );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2      cursor_name     pls_integer default dbms_sql.open_cursor;
  3      ignore          pls_integer;
  4
  5      l_num1        int default 5;
  6      l_num2      int default 10;

  7
  8 BEGIN
  9 dbms_sql.parse(cursor_name, 'insert into t values ( ' || l_num1 || ',' || l_num2 || ')', dbms_sql.native);

 10      ignore := dbms_sql.execute(cursor_name);
 11      dbms_sql.close_cursor(cursor_name);

12 END;
13 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2      cursor_name     pls_integer default dbms_sql.open_cursor;
  3      ignore          pls_integer;
  4
  5      l_num1        int default 50;
  6      l_num2      int default 100;

  7
  8 BEGIN
  9 dbms_sql.parse(cursor_name, 'insert into t values ( :bv1, :bv2 )', dbms_sql.native);

 10      dbms_sql.bind_variable( cursor_name, ':bv1', l_num1 );
 11      dbms_sql.bind_variable( cursor_name, ':bv2', l_num2 );
 12      ignore := dbms_sql.execute(cursor_name);
 13      dbms_sql.close_cursor(cursor_name);

14 END;
15 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> select * from t;

NUM1 NUM2
---------- ----------

         5         10
        50        100
--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sun Apr 23 2000 - 00:00:00 CDT