Link to home
Start Free TrialLog in
Avatar of mikha
mikhaFlag for United States of America

asked on

how to add/update procedure in oracle

I am new to oracle. I have to update a procedure (see sample below) , which is within a package, say abc_pkg.

I want to write sql scripts to update existing procedure, below. also, if i want to add another procedure to the same package, how do i do that.

I want to write sql scripts , which i can execute to update the database.

Procedure get_info (
   id IN Number
)
IS
BEGIN
    select * from TABLE where id = id..
Avatar of OMC2000
OMC2000
Flag of Russian Federation image

To add or edit procedure or function, included into a package, you always  have to update package itself.

Change package [defintion] when you add new procedure or function or change their specification:
CREATE OR REPLACE PACKAGE ADM_PACKAGE IS
and then change package body
CREATE OR REPLACE PACKAGE BODY ADM_PACKAGE IS


And change only package body when you edit existing procedure or function:
CREATE OR REPLACE PACKAGE BODY ADM_PACKAGE IS
ASKER CERTIFIED SOLUTION
Avatar of Dmitry Kurashkin
Dmitry Kurashkin
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mikha

ASKER

@Dmitry - thanks for your comment. I am using oracle sql developer.

When u say compile , what exactly do I need to do in the sql developer.

Also , to release this in production , will I be able to just run some scripts to compile in production or how would  that work ?
Actually you need only to execute CREATE OR REPLACE ... command.

alternative instrument is command alter package:

ALTER PACKAGE [schema.]package_name COMPILE
[DEBUG PACKAGE|SPECIFICATION|BODY];
When u say compile , what exactly do I need to do in the sql developer.
Here is a place where you can find a list of packages (on the bottom of the image):

User generated image
Left click on a package opens its declaration, or click inside a package tree on a package body to see the body.
It will be opened in the editor. To compile, click on the button with gears:

User generated image
Also , to release this in production , will I be able to just run some scripts to compile in production or how would  that work ?
Yes, you can execute the same "create or replace package ..." script. You need to have enough permissions to execute, and also, you need to make sure that nobody executes the package during compilation (it could provoke locks and errors on a client side).
You shouldn't need to compile.  The CREATE OR REPLACE handles that.  Altering the package to compile it is really only needed if a dependent object is changed, and even then it will happen automatically when the object is accessed.