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)
- In Oracle SQL Developer, connect to the database user and on the left side (connection panel) expand the table node.
- Select the table and do the right click on it to open the shortcut menu.
- From the menu, select the Generate Table API option and click on it.
- It will generate the package script for the selected table and will open in the SQL editor right side.
- 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;