Sayantan's Blog On Oracle

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:

• Working With Packages
• ORACLE SUPPLIED PACKAGES
• DBMS_UTILITY PACKAGE
• DBMS_LOCK PACKAGE
• UTL_FILE PACKAGE
• DBMS_OUTPUT PACKAGE
• DBMS_SQL PACKAGE