Re: dynamic-sql question

> >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 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.

Daniel A. Morgan Received on Wed Nov 11 1998 - 13:05:03 CST