Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: command alter in procedure

    my question is: can I use command alter in procedure?If YES, how I can do this?

    I created a package:
    Code:
    CREATE OR REPLACE PACKAGE p_licznik AS 
    
    PROCEDURE proc_licznik;
    
    END p_licznik;
    /
    
    CREATE OR REPLACE PACKAGE BODY p_licznik IS
    
    PROCEDURE proc_licznik is
    	
    begin
    	
    	ALTER SEQUENCE licz increment by -14;
    	DBMS_OUTPUT.PUT_LINE('LICZNIK set zero');	
    end;
    
    END p_licznik;
    /
    when I compile it I get a message
    Code:
    package body created with compilation errors
    when I remove command alter everything's ok

    do somebody know what's the problem??

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALTER command is DDL & can only be done within a procedure using EXECUTE IMMEDIATE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2004
    Posts
    370
    As anacedent has said it is a DDL statement so you should use EXECUTE IMMEDIATE or DBMS_SQL built in package BUT I can't understand why do you try to alter a sequence! Do you want to REuse it? therefor I think it is better to create a new sequence for this purpose because it has not such an overhead and also does not mess everything in my opinion.Any other ideas please?
    -Good luck!

  4. #4
    Join Date
    Nov 2005
    Posts
    4
    I know that, I can't understand that to, but my friend wanted to set new value of sequence in this way, so I did't argue with him I was only loking for a solution

    the final code is
    Code:
    EXECUTE IMMEDIATE ' ALTER SEQUENCE licz increment by -14';
    thank you for help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •