Herşey ilk adımlarla başlar...

Monday, January 31, 2011

Step By Step PL/SQL Tutorial ( FouthStep : How to use DDL )

1/31/2011 Posted by Nurettin Onur YAKIT , , , , , , , , No comments
You want to use ALTER function inside your PL/SQL Function.. Tried to write something like this ;


CREATE OR REPLACE FUNCTION my_funct
RETURN number
       IS
        mynumber number ;
       BEGIN
       mynumber:=2;
       EXECUTE IMMEDIATE 'ALTER SEQUENCE  SEQ_ALI   INCREMENT BY 1';
      SELECT SEQ_ALI.NEXTVAL INTO mynumber FROM DUAL;
         RETURN mynumber;
       END my_funct;
/

But you are getting an Oracle Error : ORA-14552 ?!?

This is what you should do ;


CREATE OR REPLACE FUNCTION my_funct
RETURN number
       IS
       PRAGMA AUTONOMOUS_TRANSACTION;
         mynumber number ;
       BEGIN
       mynumber:=2;
       EXECUTE IMMEDIATE 'ALTER SEQUENCE  SEQ_ALI   INCREMENT BY 1';
      SELECT SEQ_ALI.NEXTVAL INTO mynumber FROM DUAL;
         RETURN mynumber;
       END my_funct;

0 comments: