Re: Any PL/SQL guru can help me?

Hi Stefano,

First of all I d like to say, that I m not guru in PL/SQL. Even worse, this is my first attempt to use dynamic SQL package, that means - maybe I m not right ( sorry, if this is the case ).Because I found your problem very interesting, I tried to find the solution and here is what I found:

1. First problem which I had after simple copy and paste of your example ( and small change of the select statement for storing values to variable n ) :

ERROR at line 1:
ORA-06550: line 9, column 1:
PLS-00307: too many declarations of 'BIND_VARIABLE' match this call
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored

From this it seems that something is wrong with the call to BIND_VARIABLE procedure. According to the Oracle7 Server Documentation Addendum Release 7.1, the first parameter which is passed to BIND_VARIABLE procedure should be a number and not a string. This number is ID number of the cursor to which you wanna bind a value. But this is not the end of troubles. After some unsuccessful attempts to make your script work, I decided to use well-known method RTFM ( Read The F?*!ing Manual ).
According to the execution flow diagram and description of PARSE and BIND_VARIABLE phase on page 7-4 and 7-5 of Addendum Release 7.1, it's quite clear, that right now DDL commands are also executed in PARSE phase. ( you don't need execute phase for DDL commands) But BIND_VARIABLE phase happens only after PARSE phase, that means there is no way to use BIND_VARIABLE phase for DDL commands !!! There is a work around, which is ( I hope ) quite clear from the following example:

declare
  n number;
  str string(100);
  cur integer;
begin
  cur := dbms_sql.open_cursor;
  select to_number('500') into n from dual;   str := 'create sequence seq1 start with '||n ;   dbms_sql.parse(cur, str, dbms_sql.v7);   dbms_sql.close_cursor(cur);
exception
  when others then
  dbms_sql.close_cursor(cur);
end;
/

This script works quite well.

P.S. Of course I modified the select statement for storing value to the variable "n".

Your second problem is with dbms_sql.execute. Oracle is quite right, dbms_sql.execute is not a procedure. It's a FUNCTION !!! Following example proves, that for DML statements works everything - BIND_VARIABLE procedure and EXECUTE function as well.

(DB7A)-OPS$VALNY SQL > create table t ( col1 number );

Table created.

(DB7A)-OPS$VALNY SQL > insert into t values (100);

1 row created.

(DB7A)-OPS$VALNY SQL > c/100/200

  1* insert into t values (200)
(DB7A)-OPS$VALNY SQL > /
1 row created.

(DB7A)-OPS$VALNY SQL > commit;

Commit complete.

(DB7A)-OPS$VALNY SQL > create table t1 ( col1 number );

Table created.

(DB7A)-OPS$VALNY SQL > get tt

  1 declare
  2 n number;
  3 result number;
  4 str string(100);
  5 cur integer;
  6 begin
  7 cur := dbms_sql.open_cursor;
  8 select to_number('100') into n from dual;   9 str := 'insert into t1 select * from t where col1= :X' ;
 10 dbms_sql.parse(cur, str, dbms_sql.v7);  11 dbms_sql.bind_variable(cur,':X',n);  12 result:=dbms_sql.execute(cur);
 13 dbms_sql.close_cursor(cur);
 14 exception
 15 when others then
 16 dbms_sql.close_cursor(cur);
 17* end;
(DB7A)-OPS$VALNY SQL > /
PL/SQL procedure successfully completed.

(DB7A)-OPS$VALNY SQL > select * from t1;

      COL1

       100

(DB7A)-OPS$VALNY SQL >
I hope, this will help you. ( Sorry PL/SQL gurus if I'm not right. Don't be cross with me, it's really my first attempt to use dynamic SQL package )
Stefano, I'd be glad, if you let me know whether it helped you or not.

Brano Valny.

Email: valny_at_slovnaft.sk
Phone: +4272404796
Fax: +427244931

I would like to know God's thoughts ...
  the rest are details.

          Albert Einstein. Received on Mon Nov 27 1995 - 00:00:00 CET