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: