How to Generate Table API Package in Oracle SQL Developer?

by

In Oracle SQL Developer, there is an option to generate a Table API Package with which you can easily manage insert, update and delete for a table. Further, you can modify more to add your required changes.

Steps To Generate a Table API Package in Oracle SQL Developer (Version 18.1)

  1. In Oracle SQL Developer, connect to the database user and on the left side (connection panel) expand the table node.
  2. Select the table and do the right click on it to open the shortcut menu.
  3. From the menu, select the Generate Table API option and click on it.
  4. It will generate the package script for the selected table and will open in the SQL editor right side.
  5. Now you can execute the script to create the package in your database schema.

You can create the DML package online check this link: Create DML Package Online

Below is an example package script for Table API. Generated from the HR schema's Employee table.

Example Table API Script

CREATE OR REPLACE PACKAGE EMPLOYEES_tapi
IS
   TYPE EMPLOYEES_tapi_rec IS RECORD (
      JOB_ID           EMPLOYEES.JOB_ID%TYPE,
      EMPLOYEE_ID      EMPLOYEES.EMPLOYEE_ID%TYPE,
      SALARY           EMPLOYEES.SALARY%TYPE,
      HIRE_DATE        EMPLOYEES.HIRE_DATE%TYPE,
      DEPARTMENT_ID    EMPLOYEES.DEPARTMENT_ID%TYPE,
      LAST_NAME        EMPLOYEES.LAST_NAME%TYPE,
      EMAIL            EMPLOYEES.EMAIL%TYPE,
      PHONE_NUMBER     EMPLOYEES.PHONE_NUMBER%TYPE,
      FIRST_NAME       EMPLOYEES.FIRST_NAME%TYPE,
      COMMISSION_PCT   EMPLOYEES.COMMISSION_PCT%TYPE,
      MANAGER_ID       EMPLOYEES.MANAGER_ID%TYPE
   );

   TYPE EMPLOYEES_tapi_tab IS TABLE OF EMPLOYEES_tapi_rec;

   -- insert
   PROCEDURE ins (
      p_JOB_ID           IN EMPLOYEES.JOB_ID%TYPE,
      p_EMPLOYEE_ID      IN EMPLOYEES.EMPLOYEE_ID%TYPE,
      p_SALARY           IN EMPLOYEES.SALARY%TYPE DEFAULT NULL,
      p_HIRE_DATE        IN EMPLOYEES.HIRE_DATE%TYPE,
      p_DEPARTMENT_ID    IN EMPLOYEES.DEPARTMENT_ID%TYPE DEFAULT NULL,
      p_LAST_NAME        IN EMPLOYEES.LAST_NAME%TYPE,
      p_EMAIL            IN EMPLOYEES.EMAIL%TYPE,
      p_PHONE_NUMBER     IN EMPLOYEES.PHONE_NUMBER%TYPE DEFAULT NULL,
      p_FIRST_NAME       IN EMPLOYEES.FIRST_NAME%TYPE DEFAULT NULL,
      p_COMMISSION_PCT   IN EMPLOYEES.COMMISSION_PCT%TYPE DEFAULT NULL,
      p_MANAGER_ID       IN EMPLOYEES.MANAGER_ID%TYPE DEFAULT NULL);

   -- update
   PROCEDURE upd (
      p_JOB_ID           IN EMPLOYEES.JOB_ID%TYPE,
      p_EMPLOYEE_ID      IN EMPLOYEES.EMPLOYEE_ID%TYPE,
      p_SALARY           IN EMPLOYEES.SALARY%TYPE DEFAULT NULL,
      p_HIRE_DATE        IN EMPLOYEES.HIRE_DATE%TYPE,
      p_DEPARTMENT_ID    IN EMPLOYEES.DEPARTMENT_ID%TYPE DEFAULT NULL,
      p_LAST_NAME        IN EMPLOYEES.LAST_NAME%TYPE,
      p_EMAIL            IN EMPLOYEES.EMAIL%TYPE,
      p_PHONE_NUMBER     IN EMPLOYEES.PHONE_NUMBER%TYPE DEFAULT NULL,
      p_FIRST_NAME       IN EMPLOYEES.FIRST_NAME%TYPE DEFAULT NULL,
      p_COMMISSION_PCT   IN EMPLOYEES.COMMISSION_PCT%TYPE DEFAULT NULL,
      p_MANAGER_ID       IN EMPLOYEES.MANAGER_ID%TYPE DEFAULT NULL);

   -- delete
   PROCEDURE del (p_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE);
END EMPLOYEES_tapi;
/

CREATE OR REPLACE PACKAGE BODY EMPLOYEES_tapi
IS
   -- insert
   PROCEDURE ins (
      p_JOB_ID           IN EMPLOYEES.JOB_ID%TYPE,
      p_EMPLOYEE_ID      IN EMPLOYEES.EMPLOYEE_ID%TYPE,
      p_SALARY           IN EMPLOYEES.SALARY%TYPE DEFAULT NULL,
      p_HIRE_DATE        IN EMPLOYEES.HIRE_DATE%TYPE,
      p_DEPARTMENT_ID    IN EMPLOYEES.DEPARTMENT_ID%TYPE DEFAULT NULL,
      p_LAST_NAME        IN EMPLOYEES.LAST_NAME%TYPE,
      p_EMAIL            IN EMPLOYEES.EMAIL%TYPE,
      p_PHONE_NUMBER     IN EMPLOYEES.PHONE_NUMBER%TYPE DEFAULT NULL,
      p_FIRST_NAME       IN EMPLOYEES.FIRST_NAME%TYPE DEFAULT NULL,
      p_COMMISSION_PCT   IN EMPLOYEES.COMMISSION_PCT%TYPE DEFAULT NULL,
      p_MANAGER_ID       IN EMPLOYEES.MANAGER_ID%TYPE DEFAULT NULL)
   IS
   BEGIN
      INSERT INTO EMPLOYEES (JOB_ID,
                             EMPLOYEE_ID,
                             SALARY,
                             HIRE_DATE,
                             DEPARTMENT_ID,
                             LAST_NAME,
                             EMAIL,
                             PHONE_NUMBER,
                             FIRST_NAME,
                             COMMISSION_PCT,
                             MANAGER_ID)
          VALUES (p_JOB_ID,
                  p_EMPLOYEE_ID,
                  p_SALARY,
                  p_HIRE_DATE,
                  p_DEPARTMENT_ID,
                  p_LAST_NAME,
                  p_EMAIL,
                  p_PHONE_NUMBER,
                  p_FIRST_NAME,
                  p_COMMISSION_PCT,
                  p_MANAGER_ID);
   END;

   -- update
   PROCEDURE upd (
      p_JOB_ID           IN EMPLOYEES.JOB_ID%TYPE,
      p_EMPLOYEE_ID      IN EMPLOYEES.EMPLOYEE_ID%TYPE,
      p_SALARY           IN EMPLOYEES.SALARY%TYPE DEFAULT NULL,
      p_HIRE_DATE        IN EMPLOYEES.HIRE_DATE%TYPE,
      p_DEPARTMENT_ID    IN EMPLOYEES.DEPARTMENT_ID%TYPE DEFAULT NULL,
      p_LAST_NAME        IN EMPLOYEES.LAST_NAME%TYPE,
      p_EMAIL            IN EMPLOYEES.EMAIL%TYPE,
      p_PHONE_NUMBER     IN EMPLOYEES.PHONE_NUMBER%TYPE DEFAULT NULL,
      p_FIRST_NAME       IN EMPLOYEES.FIRST_NAME%TYPE DEFAULT NULL,
      p_COMMISSION_PCT   IN EMPLOYEES.COMMISSION_PCT%TYPE DEFAULT NULL,
      p_MANAGER_ID       IN EMPLOYEES.MANAGER_ID%TYPE DEFAULT NULL)
   IS
   BEGIN
      UPDATE EMPLOYEES
         SET JOB_ID = p_JOB_ID,
             SALARY = p_SALARY,
             HIRE_DATE = p_HIRE_DATE,
             DEPARTMENT_ID = p_DEPARTMENT_ID,
             LAST_NAME = p_LAST_NAME,
             EMAIL = p_EMAIL,
             PHONE_NUMBER = p_PHONE_NUMBER,
             FIRST_NAME = p_FIRST_NAME,
             COMMISSION_PCT = p_COMMISSION_PCT,
             MANAGER_ID = p_MANAGER_ID
       WHERE EMPLOYEE_ID = p_EMPLOYEE_ID;
   END;

   -- del
   PROCEDURE del (p_EMPLOYEE_ID IN EMPLOYEES.EMPLOYEE_ID%TYPE)
   IS
   BEGIN
      DELETE FROM EMPLOYEES
            WHERE EMPLOYEE_ID = p_EMPLOYEE_ID;
   END;
END EMPLOYEES_tapi;

How to Use the Table API?

Now suppose you want to delete an employee record from the Employees table, then use the package API procedure EMPLOYEES_TAPI.DEL as following:

DECLARE 
  P_EMPLOYEE_ID NUMBER;

BEGIN 
  P_EMPLOYEE_ID := 105;

  EMPLOYEES_TAPI.DEL ( P_EMPLOYEE_ID );
  COMMIT; 
END;

See also:

Leave a Comment