Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Question Unanswered: alter stored procedure

    I want to alter a stored procedure which is in a package. But, the package also has many other stored procedures which I don't want to modify. How do I do this?

    Do I have to include all the stored procedures in the statement:
    ALTER PACKAGE A as
    PROCEDURE X(
    ...
    );
    END PACKAGE A;

    Any help would be appreciated. Thanks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If the procedure is part of a package, you MUST recompile the entire package using the ALTER PACKAGE command. In other words, using ALTER PROCEDURE won't work.

    I'd like to ask something, though ... what do you mean by "altering a stored procedure"? You modified its code or the declaration as well? If it's only the code, fine. You'll have to execute
    ALTER PACKAGE name_of_the_package COMPILE BODY;
    If it results in compilation errors, the body will be invalid but dependent objects will NOT be invalidated.

    But, if you changed declaration of the procedure, you'll have to change other program objects that call this procedure. They'll have to be recompiled as well, right? Therefore, execute
    ALTER PACKAGE name_of_the_package COMPILE PACKAGE;
    If it results in compilation errors, all dependent objects will also be invalidated.

    I don't know if you use TOAD (I'd suggest it) as it enables you to manipulate with packages and their objects easily.

Posting Permissions

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