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;