[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Adding procedures to an existing package

Posted on 2007-07-25
2
Medium Priority
?
3,457 Views
Last Modified: 2012-05-05
Hi,

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)
   IS
   BEGIN
      OPEN p_cur FOR
         SELECT DISTINCT (secid)
                    FROM inflinkgmasdata;
   EXCEPTION
      WHEN OTHERS
      THEN
         raise_application_error (-20001,
                                     'Error=get_ilb '
                                  || SQLCODE
                                  || ' Description='
                                  || SQLERRM
                                 );
   END;
end package;
/


0
Comment
Question by:ISC
2 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 750 total points
ID: 19563430
You have first to upgrade the package specification

CREATE OR REPLACE PACKAGE ....

After that you can upgrade package body

CREATE OR REPLACE 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.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 750 total points
ID: 19565573
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
 begin
   ----  procedure code goes here
 end;
 
 function f1(..) return number is
 begin
   ----  function code goes here
 end;

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;

Thanks
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

872 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