Adding procedures to an existing package

Posted on 2007-07-25
Last Modified: 2012-05-05

I have an Oracle package thats in a live environment, I need to add three extra procedures to the package, how can I do this...? I am thinking along the lines...

Update package mypackage

       /* Used by the tactical application dirty prices to get inflation linked bonds. */
   PROCEDURE get_ilbX (p_cur OUT ref_cur);

      /* Used by the tactical application dirty prices to get latest
     factor for a security for date passed in. */
   PROCEDURE get_ilbX ( (
      p_secid      IN       VARCHAR2,
      p_asofdate   IN       NUMBER,
      p_cur        OUT      ref_cur

   /* Used by the tactical application dirty prices to get inflation linked bonds. */
   PROCEDURE get_ilbX (p_cur OUT ref_cur)
      OPEN p_cur FOR
         SELECT DISTINCT (secid)
                    FROM inflinkgmasdata;
         raise_application_error (-20001,
                                     'Error=get_ilb '
                                  || SQLCODE
                                  || ' Description='
                                  || SQLERRM
end package;

Question by:ISC
    LVL 47

    Accepted Solution

    You have first to upgrade the package specification


    After that you can upgrade package body


    It is not simple to add new procedure to a package, so try to
    experiment the package (possibly under other name) to make sure that everything runs fine.
    LVL 28

    Assisted Solution

    put all your procedures/functions/type declarations
    in the package specification and code in the body.

    create or replace package mypackage is
     procedure p1(..);
     function f1(..) return number;
    end mypackage;

    create or replace package body mypackage is
     procedure p1(..) is
       ----  procedure code goes here
     function f1(..) return number is
       ----  function code goes here

    end mypackage;

    Run the above code and use show errors to see any compilation errors if any.

    if you want to compile the existing package in the database , then

    alter package mypackage compile; -- to compile both body and spec

    alter package mypackage compile body;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now