Re: Any PL/SQL guru can help me?

In article <DIJoqq.HrH_at_mecati.mecasoft.ch>, unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) writes:
|>
|> I need help with PL/SQL !
|> Yes I know, I have to attend a PL/SQL course... anyway:
|> Here is my problem: I'm trying to create a sequence starting with
|> vaule = max(current_value)+1.
|> This should be interesting for quite a lot of people...
|>
|> This is my last version, but still having errors:
|>
|> SQL> declare
|> 2 n number;
|> 3 str string(100);
|> 4 cur integer;
|> 5 begin
|> 6 cur := dbms_sql.open_cursor;
|> 7 str := 'create sequence seq1 start with :x';
|> 8 select max(pkey)+1 into n from pieces;
|> 9 dbms_sql.bind_variable(str, ':x', n);
|> 10 dbms_sql.parse(cur, str, dbms_sql.v7);
|> 11 dbms_sql.close_cursor(cur);
|> 12 exception
|> 13 when others then
|> 14 dbms_sql.close_cursor(cur);
|> 15 end;
|> 16 /
|>
|> This command gives an error in line 10, but I think it is enough to let you
|> understand what I'm trying to do...
|> I've also found in manual Oracle7 Server Documentation Addendum Release 7.1,
|> Chapter 7, a call to dbms_sql.execute, but this give me the error
|> PLS-00221: 'EXECUTE' is not a procedure or is undefined

Bind variables can only be used with DML statements (select, insert, update, delete). You need to do this with something like:

declare
  n number;
  str string(100);
  cur integer;
begin
  cur := dbms_sql.open_cursor;
  select max(pkey) + 1 into n from pieces;   str := 'create sequence seq1 start with ' || to_char(n);   dbms_sql.parse(cur, str, dbms_sql.v7);   dbms_sql.close_cursor(cur);
exception
  ...
end;

|>
|> Many thanks for any help or suggestions!
|> Stefano
|>
|> ------------------------ OUR INSTALLATION AT A GLANCE ------------------------
|>
|> ORACLE7 Server (RDBMS) 7.1.6.2.0 CORE Version 2.3.7.1.0
|> Oracle Server Manager 2.1.3.0.0 Oracle Network Manager 2.1.6.0.0
|> SQL*Net V2 2.1.6.1.0 TCP/IP Protocol Adapter (V2) 2.1.6.1.0
|> PL/SQL V2 2.1.6.2.0 SQLLIB 1.6.7.0.0
|> SQL*Plus 3.1.3.7.1 Pro*C 2.0.6.0.0
|> ------------------------------------------------------------------------------
|> Stefano Unternaehrer Mecasoft SA
|> Oracle DBA 6600 Muralto
|> PL/SQL, Pro*C, C, XWindow & Motif Switzerland Europe
|> Software Developer fax: +41 91 743 5507
|> email: dba,unter_at_mecasoft.ch voice: +41 91 743 7444
Received on Mon Nov 27 1995 - 00:00:00 CET