Re: procedure and ddl commands?

From: dead <OVOIZBACI-vlasic_at_vodatel.net>
Date: Thu, 29 Jul 2004 10:46:32 +0200
Message-ID: <ceadhc$jch$1@sunce.iskon.hr>

thank you for sugesstions, but this all was not code from procedure, I just send stupid mix of code what I need; part of index is not in this procedure,
and I saw in messages before that
all DDL commands have to be in ' EXECUTE IMMEDIATE' statement (this index is only why I use value from table into sequence) I know for procedure exception, but this was not what I was interested

I tryed my start procedure like :

Procedure PRTEST

nNewSNo number;
BEGIN SELECT max(SNo)+1 INTO nNewSNo FROM testtable1;

EXECUTE IMMEDIATE 'CREATE SEQUENCE testsequence

    INCREMENT BY 1
    START WITH :1
    MINVALUE 1
    MAXVALUE 999999
    CYCLE
    NOORDER
    NOCACHE'
    using nNewSNo;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

and start it with :

BEGIN
  prtest;
  COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM); RAISE;
END;

but I had exception
ORA-20001: An error was encountered - -1722 -ERROR- ORA-01722: invalid number ORA-06512: at line 8
I change select statement with "SELECT 1 into nNewSNo FROM dual;" and had same exception

Where is problem?

thanks Received on Thu Jul 29 2004 - 03:46:32 CDT