mikha
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..
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..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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 ];
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):
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:
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.
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