Press "Enter" to skip to content

How to Generate Table API Package in Oracle SQL Developer?

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:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *