Re: Any PL/SQL guru can help me?

In article <DIJoqq.HrH_at_mecati.mecasoft.ch> unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) writes:
>From: unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER)
>Subject: Any PL/SQL guru can help me?
>Date: Fri, 24 Nov 1995 11:18:25 GMT
 

>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

That's because dbms_sql.execute is actually a function, not a procedure. I assume that you used the procedure calling convention when you attempted to call dbms_sql.execute...

Still, since the statement you're trying to execute is DDL, you really don't need to call "execute"--the statement is execute at parse time (but I'm sure you knew that.) As to the overall problem, and without knowing the exact error being generated, could it be possible that the sequence that you're trying to create already exists? I don't see an attempt to drop the sequence before creating it.

>Many thanks for any help or suggestions!
>Stefano

Hope that helps some. Good luck to you, Stefano.

Mike Received on Fri Nov 24 1995 - 00:00:00 CET