CODEWITHSUNDEEP
stored-proceduresplsql
Johanna
Johanna

Reputation: 151

How to Create a new Stored Procedure in an Existing Package

I am new to using PL/SQL and I am trying to create a new stored procedure in an existing package. I realize that there is "CREATE OR REPLACE PACKAGE BODY," but does this mean that the entire body will be deleted once I add my new proc, or will it just append it on? (My proc name does not have the same name as any of the current procs).

Upvotes: 10

Views: 30843

Answers (2)

Kislay Sinha
Kislay Sinha

Reputation: 337

CREATE OR REPLACE PACKAGE emp_actions AS  -- spec
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
CURSOR desc_salary RETURN EmpRecTyp;
PROCEDURE hire_employee (
  ename  VARCHAR2,
  job    VARCHAR2,
  mgr    NUMBER,
  sal    NUMBER,
  comm   NUMBER,
  deptno NUMBER);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- body
CURSOR desc_salary RETURN EmpRecTyp IS
  SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
  ename  VARCHAR2,
  job    VARCHAR2,
  mgr    NUMBER,
  sal    NUMBER,
  comm   NUMBER,
  deptno NUMBER) IS
BEGIN
  INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,
     mgr, SYSDATE, sal, comm, deptno);
END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
  DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;

I you first define the function with its type and later write full body of one or more function which is to be included in a package. Things should work good.

Upvotes: 2

Bueller
Bueller

Reputation: 2344

Unfortunately, to add a new procudure or function to an existing package, you must modify the original package and resubmit as a create or replace.

there is no method currently for adding to or removing from a package without modifying and resubmitting the whole package code.

http://forums.oracle.com/forums/thread.jspa?threadID=380898

Upvotes: 16

Related Questions