Re: dynamic-sql question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 12 Nov 1998 16:35:27 GMT
Message-ID: <36520b8b.10743748@192.86.155.100>

A copy of this was sent to Dan Morgan <dmorgan_at_exesolutions.com> (if that email address didn't require changing) On Wed, 11 Nov 1998 11:05:03 -0800, you wrote:

>> >can anybody tell me when to use the bind_variable-procedure and when to
>> >use the concatenation-operator (double pipe ||).
>
>> Well, the || will *always* work.
>
>In my experience this is definitely not true in DSQL. Working on an Amdahl under VM

Dan--

it this wasn't true, if || didn't ALWAYS work, there would be no use for a product like SQLPlus or svrmgrl -- they would not be able to take various sql statements and run with them... They take strings typically (no bind variables although you could use a bind variable) and dynamically execute them.

I believe what happened to you was you didn't quote things correctly. For example, I see you are using a rowid below. Perhaps the string you concated together looked like the first update in the following:

SQL> select rowid, dummy from dual;

ROWID D

------------------ -

0000033D.0000.0001 X

SQL> update dual set dummy = 'Y' where rowid = 0000033D.0000.0001; update dual set dummy = 'Y' where rowid = 0000033D.0000.0001

ERROR at line 1:
ORA-00933: SQL command not properly ended

To fix this you could EITHER use a bind variable (which will not require the quotes) or you could quote the rowid as it would have to be in a SQL query without a bind variable. For example:

SQL> update dual set dummy = 'Y' where rowid = '0000033D.0000.0001'; 1 row updated.

works just dandy, as does:

SQL> variable x varchar2(20);
SQL> exec :x := '0000033D.0000.0001';

PL/SQL procedure successfully completed.

SQL> update dual set dummy = 'y' where rowid = :x; 1 row updated.

So, if you use concatenation you must quote strings -- if you use bind variables not so.

>with Oracle 7.1.4 and 7.1.6 there were times where concatenation worked and times
>that it didn't. And there were times that a bind variable would work and times that
>it didn't.
>
>The only way I found to tell what was happening was to build my SQL string, perform
>the bind, and then write it to a table where I could look at what was actually being
>executed. As I recall the general rule was that chars such as field names and table
>names could be concatenated but numbers had to be bound. Here is an example of the
>code that I found.
>
> sqlstr :=
> 'UPDATE mmo_customer_3 SET ' || FldName ||
> ' = ' || '(' || FldName || ' + :QVal) ' ||
> 'WHERE rowid = :RVal';
> tCursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(tCursor, sqlstr, dbms_sql.v7);
> DBMS_SQL.BIND_VARIABLE(tCursor,':QVal', ci_rec.pqty_num);
> DBMS_SQL.BIND_VARIABLE(tCursor,':RVal', cc_rowid);
> RetVal := DBMS_SQL.EXECUTE(tCursor);
> dbms_sql.CLOSE_CURSOR(tCursor);
>
>Any attempt to do it another way failed.
>

bug in you code then -- you didn't quote things properly. Believe me -- you can write it in a query (think sqlplus -- it can execute "update emp set empno = 5 where rownum = 1" proving you DON'T have to bind a number)....

Here is an example from 7.1.6 very similar to what you were doing above showing how to do it correctly:

Oracle7 Server Release 7.1.6.2.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.1.6.2.0 - Production

SQL> set echo on
SQL> @test
SQL> drop table test;

Table dropped.

SQL> create table test ( x int );
Table created.

SQL> insert into test values ( 1 );
1 row created.

SQL> declare

  2      exec_cursor     integer default dbms_sql.open_cursor;
  3      rows_processed  number  default 0;
  4          sql_stmt                        varchar2(255);
  5  
  5          l_x                             number;
  6          l_rowid                 rowid;
  7  begin
  8          select rowid, x into l_rowid, l_x from test;
  9  
  9          sql_stmt := 'update test set x = ' || to_char(l_x+55) ||
 10                                  ' where rowid = ''' || l_rowid || '''';
 11  
 11          dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
 12          rows_processed := dbms_sql.execute(exec_cursor);
 13          dbms_sql.close_cursor( exec_cursor );
 14          dbms_output.put_line( rows_processed || ' Rows Updated' );
 15  exception
 16      when others then
 17        if dbms_sql.is_open(exec_cursor) then
 18          dbms_sql.close_cursor(exec_cursor);
 19        end if;
 20        raise;

21 end;
22 /
1 Rows Updated

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test;

>Daniel A. Morgan

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 12 1998 - 10:35:27 CST