Table of Contents
Queries on Package in Oracle:
How to call one package from another package in PL SQL?
We will create two packages to test the calling.
1st Package: PKG_EMP_DATA
This package will extract information about employees of a specific department.
--Queries on Package in Oracle : Example CREATE OR REPLACE PACKAGE PKG_EMP_DATA IS FUNCTION FUN_GET_DEPT_INFO(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) RETURN VARCHAR2; PROCEDURE SP_GET_EMP_DATA(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE); -- V_DEPT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE; END; /
--Queries on Package in Oracle : Example CREATE OR REPLACE PACKAGE BODY PKG_EMP_DATA IS FUNCTION FUN_GET_DEPT_INFO(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) RETURN VARCHAR2 IS BEGIN SELECT DEPARTMENT_NAME INTO V_DEPT_NAME FROM DEPARTMENTS WHERE DEPARTMENT_ID = P_DEPTID; -- RETURN V_DEPT_NAME; EXCEPTION WHEN OTHERS THEN V_DEPT_NAME := ' '; RETURN V_DEPT_NAME; END; -- PROCEDURE SP_GET_EMP_DATA(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) IS BEGIN FOR I IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = P_DEPTID ) LOOP V_DEPT_NAME := FUN_GET_DEPT_INFO(P_DEPTID); DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> EMPLOYEE_ID -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> JOB_ID -> '||I.JOB_ID||' -> SALARY -> '||I.SALARY||' -> DEPARTMENT -> '||V_DEPT_NAME); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM); END; END; /
2nd Package: PKG_EMP_DATA_CALL
This package will call the 1st Package PKG_EMP_DATA
--Queries on Package in Oracle : Example CREATE OR REPLACE PACKAGE PKG_EMP_DATA_CALL IS PROCEDURE SP_GET_EMP_DATA_CALL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE); END; /
--Queries on Package in Oracle : Example CREATE OR REPLACE PACKAGE BODY PKG_EMP_DATA_CALL IS PROCEDURE SP_GET_EMP_DATA_CALL(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) IS BEGIN PKG_EMP_DATA.SP_GET_EMP_DATA(P_DEPTID); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR IN CALLING PACKAGE PKG_EMP_DATA - > '||SQLERRM); END; END; /
Calling part:
--Queries on Package in Oracle : Example execution SET SERVEROUTPUT ON; EXEC PKG_EMP_DATA_CALL.SP_GET_EMP_DATA_CALL(30);
In the above example we are calling the procedure SP_GET_EMP_DATA_CALL of package PKG_EMP_DATA_CALL which in turn calling the SP_GET_EMP_DATA procedure of package PKG_EMP_DATA.
Query to get package specification in Oracle:
We will use DBMS_METADAT.GET_DDL system defined package to extract the DDL script of a package.
For package specification we will pass 'PAKAGE_SPEC'.
Likewise we will pass 'PACKAGE_BODY' for package body.
We will pass 'PACKAGE' for both PACKAGE SPEC and BODY.
So the complete package code will be displayed.
Accessing Package Specification only:
SELECT DBMS_METADATA.GET_DDL('PACKAGE_SPEC', 'PKG_EMP_DATA', 'HR') PACKAGE_SPEC FROM DUAL;
Package Body only:
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY', 'PKG_EMP_DATA', 'HR') PACKAGE_BODY FROM DUAL;
Accessing complete package:
SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'PKG_EMP_DATA', 'HR') COMPLETE_PACKAGE FROM DUAL;
How to check execution time of package in Oracle?
We will use GET_TIME procedure of DBMS_UTILITY package to check the execution time of a package.
But one thing to remember here.
Time difference is measured in 100th's of a second.
So we need to divide the same with 100 the get the result in seconds.
--Queries on Package in Oracle : Example calling SET SERVEROUTPUT ON SIZE 1000000; -- DECLARE V_START PLS_INTEGER; V_END PLS_INTEGER; BEGIN V_START := DBMS_UTILITY.GET_CPU_TIME; DBMS_OUTPUT.PUT_LINE('START TIME - > '||V_START); FOR I IN 1..170 LOOP PKG_EMP_DATA_CALL.SP_GET_EMP_DATA_CALL(50); END LOOP; V_END := DBMS_UTILITY.GET_CPU_TIME; DBMS_OUTPUT.PUT_LINE('END TIME - > '||V_END); DBMS_OUTPUT.PUT_LINE('TOTAL TIME - > '||(DBMS_UTILITY.GET_CPU_TIME - V_START)/100); END; /
In the above example, we have loop through the same package calling to involve more data in the process.
So that it will help to get larger time difference. Also we will divide the difference with 100 to get the output in second.
How to check last execution time of package in Oracle?
No direct way to get the last execution time of package. Specially from Data Dictionary level. However there is an workaround. We will create an audit table to store the execution time.
CREATE SEQUENCE PKG_EXE_SEQ;
CREATE TABLE AUDIT_PKG_EXE_TAB ( AUDITID NUMBER, PKG_NAME VARCHAR2(132), OBJECT_NAME VARCHAR2(132), EXE_TIME TIMESTAMP );
CREATE OR REPLACE PROCEDURE SP_STORE_EXE_TIME(P_PACKAGE_NAME VARCHAR2, P_OBJECT_NAME VARCHAR2, P_SUCCESS_FLAG VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO AUDIT_PKG_EXE_TAB(AUDITID, PKG_NAME, OBJECT_NAME, EXE_TIME, SUCCESS_FLAG) VALUES(PKG_EXE_SEQ.NEXTVAL, P_PACKAGE_NAME, P_OBJECT_NAME, SYSTIMESTAMP, P_SUCCESS_FLAG); -- COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; /
In the above example we have created one procedure.
It will input the name of the package and its objects and will store the execution time.
We are using AUTONOMOUS TRANSACTION. So that It will not impact the normal execution of the package.
Now we will revised our code in package PKG_EMP_DATA. We will call the procedure SP_STORE_EXE_TIME to store the execution time.
CREATE OR REPLACE PACKAGE BODY PKG_EMP_DATA IS FUNCTION FUN_GET_DEPT_INFO(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) RETURN VARCHAR2 IS BEGIN SELECT DEPARTMENT_NAME INTO V_DEPT_NAME FROM DEPARTMENTS WHERE DEPARTMENT_ID = P_DEPTID; -- SP_STORE_EXE_TIME('PKG_EMP_DATA', 'FUN_GET_DEPT_INFO', 'S'); RETURN V_DEPT_NAME; EXCEPTION WHEN OTHERS THEN V_DEPT_NAME := ' '; SP_STORE_EXE_TIME('PKG_EMP_DATA', 'FUN_GET_DEPT_INFO', 'F'); RETURN V_DEPT_NAME; END; -- PROCEDURE SP_GET_EMP_DATA(P_DEPTID DEPARTMENTS.DEPARTMENT_ID%TYPE) IS BEGIN FOR I IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = P_DEPTID ) LOOP V_DEPT_NAME := FUN_GET_DEPT_INFO(P_DEPTID); DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> EMPLOYEE_ID -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' -> JOB_ID -> '||I.JOB_ID||' -> SALARY -> '||I.SALARY||' -> DEPARTMENT -> '||V_DEPT_NAME); END LOOP; SP_STORE_EXE_TIME('PKG_EMP_DATA', 'SP_GET_EMP_DATA', 'S'); EXCEPTION WHEN OTHERS THEN SP_STORE_EXE_TIME('PKG_EMP_DATA', 'FUN_GET_DEPT_INFO', 'F'); DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM); END; END; /
SET SERVEROUTPUT ON; EXEC PKG_EMP_DATA.SP_GET_EMP_DATA(30);
Now we will query the AUDIT_PKG_EXE_TAB table.
SELECT * FROM AUDIT_PKG_EXE_TAB;
How to access package from another schema in Oracle?
CONN HR/HR@ORCL;
GRANT EXECUTE ON PKG_EMP_DATA TO SCOTT;
CONN SCOTT/TIGER@ORCL;
SET SERVEROUTPUT ON; EXEC HR.PKG_EMP_DATA.SP_GET_EMP_DATA(30);
How to grant package body to user in Oracle?
In the previous example we have given execution grant on package PKG_EMP_DATA to user SCOTT. So if we query the ALL_SOURCE data dictionary view we will be able to access the source code of specification of package PKG_EMP_DATA.
CONN SCOTT/TIGER@ORCL;
SELECT * FROM ALL_SOURCE WHERE NAME = 'PKG_EMP_DATA';
To get the access on source code of package body we need to provide grant on DBMS_DEBUG to user SCOTT.
CONN HR/HR@ORCL;
GRANT DEBUG ON PKG_EMP_DATA TO SCOTT;
Now we are checking the access on both the package specification and body.
CONN SCOTT/TIGER;
SELECT * FROM ALL_SOURCE WHERE NAME = 'PKG_EMP_DATA';
RELATED TOPICS: