MANAGING DEPENDENCY WITH PACKAGED SUBPROGRAM

MANAGING DEPENDENCY WITH PACKAGED SUBPROGRAM

In this article we will check how dependency works with packaged subprogram.

First, we will create a package and then we will call the packaged subprogram through a stand alone procedure.

CREATE OR REPLACE PACKAGE PKG_DEPEND
AS
  PROCEDURE PKG_PROC1(P_EMPID NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_DEPEND
IS
  PROCEDURE PKG_PROC1(P_EMPID NUMBER)
  IS
    V_EMP_FULL_NAME VARCHAR2(100);
  BEGIN
    SELECT FIRST_NAME||' '||LAST_NAME
    INTO V_EMP_FULL_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_FULL_NAME);
  END PKG_PROC1;
END PKG_DEPEND;
/

CREATE OR REPLACE PROCEDURE WRAP_PROC
IS
BEGIN
  PKG_DEPEND.PKG_PROC1(100);
END;
/

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('PKG_DEPEND', 'WRAP_PROC');

Now we will change the packaged procedure and will see the impact of this change on standalone procedure.

CREATE OR REPLACE PACKAGE BODY PKG_DEPEND
IS
  PROCEDURE PKG_PROC1(P_EMPID NUMBER)
  IS
    V_EMP_FULL_NAME VARCHAR2(100);
    V_EMP_SAL       NUMBER;
  BEGIN
    SELECT FIRST_NAME||' '||LAST_NAME, SALARY
    INTO V_EMP_FULL_NAME, V_EMP_SAL
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_FULL_NAME||' EMPLOYEE SALARY -> '||V_EMP_SAL);
  END PKG_PROC1;
END PKG_DEPEND;
/

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('PKG_DEPEND', 'WRAP_PROC');

So we can see that the changes in packaged procedure has no impact on standalone procedure. Now we will add another procedure in package and its impact on standalone procedure.

CREATE OR REPLACE PACKAGE PKG_DEPEND
AS
  PROCEDURE PKG_PROC1(P_EMPID NUMBER);
  PROCEDURE PKG_PROC2(P_EMPID NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_DEPEND
IS
  PROCEDURE PKG_PROC1(P_EMPID NUMBER)
  IS
    V_EMP_FULL_NAME VARCHAR2(100);
  BEGIN
    SELECT FIRST_NAME||' '||LAST_NAME
    INTO V_EMP_FULL_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_FULL_NAME);
  END PKG_PROC1;
  ---
  PROCEDURE PKG_PROC2(P_EMPID NUMBER)
  IS
    V_EMP_DETAILS VARCHAR2(1000);
  BEGIN
    SELECT 'EMPLOYEE_ID -> '||EMPLOYEE_ID||' EMPLOYEE NAME -> '||FIRST_NAME||' '||LAST_NAME
    INTO V_EMP_DETAILS
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    DBMS_OUTPUT.PUT_LINE(V_EMP_DETAILS);
  END PKG_PROC2;
END PKG_DEPEND;
/

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('PKG_DEPEND', 'WRAP_PROC');

So we can conclude that any change in packaged procedure has no impact on standalone procedure.

Now lets do the above operations on packaged function.

CREATE OR REPLACE PACKAGE PKG_DEPEND
AS
  FUNCTION PKG_FUN1(P_EMPID NUMBER) 
  RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_DEPEND
IS
  FUNCTION PKG_FUN1(P_EMPID NUMBER)
  RETURN VARCHAR2
  IS
    V_EMP_FULL_NAME VARCHAR2(100);
  BEGIN
    SELECT FIRST_NAME||' '||LAST_NAME
    INTO V_EMP_FULL_NAME
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME -> '||V_EMP_FULL_NAME);
    RETURN V_EMP_FULL_NAME;
  END PKG_FUN1;
END PKG_DEPEND;
/

CREATE OR REPLACE PROCEDURE WRAP_PROC
IS
  V_EMP_FULL_NAME VARCHAR2(100);
BEGIN
  V_EMP_FULL_NAME := PKG_DEPEND.PKG_FUN1(100);
END;
/

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('PKG_DEPEND', 'WRAP_PROC');

Now we will modify the packaged function to see the impact on standalone procedure which calls it.

CREATE OR REPLACE PACKAGE BODY PKG_DEPEND
IS
  FUNCTION PKG_FUN1(P_EMPID NUMBER)
  RETURN VARCHAR2
  IS
    V_EMP_FULL_NAME VARCHAR2(100);
    V_SAL           NUMBER;
    V_EMP_DETAILS   VARCHAR2(200);
  BEGIN
    SELECT FIRST_NAME||' '||LAST_NAME, SALARY
    INTO V_EMP_FULL_NAME, V_SAL
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = P_EMPID;
    --
    V_EMP_DETAILS := 'EMPLOYEE NAME -> '||V_EMP_FULL_NAME||' SALARY -> '||V_SAL;
    --
    DBMS_OUTPUT.PUT_LINE(V_EMP_DETAILS);
    RETURN V_EMP_DETAILS;
  END PKG_FUN1;
END PKG_DEPEND;
/

EXEC WRAP_PROC;

EMPLOYEE NAME -> Steven King SALARY -> 24000


PL/SQL procedure successfully completed.

SELECT * FROM USER_OBJECTS
WHERE OBJECT_NAME IN ('PKG_DEPEND', 'WRAP_PROC');

So we can conclude that changes in packaged function has no impact on standalone procedure which calls it.

RELATED TOPICS:

• MANAGING DEPENDENCIES IN ORACLE
• IMPACT OF CHANGING REFERENCED OBJECT ON VIEW
• IMPACT OF CHANGING REFERENCED OBJECT ON FUNCTIONS
• IMPACT OF CHANGING REFERENCED OBJECT ON PROCEDURE