8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: build_api.sql
( Download Script )
-- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/script_creation/build_api.sql -- Author : Tim Hall -- Description : Generates a basic API package for the specific table. -- Requirements : USER_% and ALL_% views. -- Call Syntax : @build_api (table-name) (schema) -- Last Modified: 08/01/2002 -- ----------------------------------------------------------------------------------- SET SERVEROUTPUT ON SET VERIFY OFF SET ECHO OFF SET TERMOUT OFF SET FEEDBACK OFF SPOOL Package.pkh DECLARE v_table_name VARCHAR2(30) := Upper('&1'); v_owner VARCHAR2(30) := Upper('&2'); CURSOR c_pk_columns IS SELECT a.position, a.column_name FROM all_cons_columns a, all_constraints b WHERE a.owner = v_owner AND a.table_name = v_table_name AND a.constraint_name = b.constraint_name AND b.constraint_type = 'P' AND b.owner = a.owner AND b.table_name = a.table_name ORDER BY position; CURSOR c_columns IS SELECT atc.column_name FROM all_tab_columns atc WHERE atc.owner = v_owner AND atc.table_name = v_table_name; CURSOR c_non_pk_columns (p_nullable IN VARCHAR2) IS SELECT atc.column_name FROM all_tab_columns atc WHERE atc.owner = v_owner AND atc.table_name = v_table_name AND atc.nullable = p_nullable AND atc.column_name NOT IN (SELECT a.column_name FROM all_cons_columns a, all_constraints b WHERE a. owner = v_owner AND a.table_name = v_table_name AND a.constraint_name = b.constraint_name AND b.constraint_type = 'P' AND b.owner = a.owner AND b.table_name = a.table_name); PROCEDURE GetParameterList IS BEGIN FOR cur_col IN c_pk_columns LOOP DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || ' IN ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,'); END LOOP; FOR cur_col IN c_non_pk_columns('N') LOOP DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || ' IN ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,'); END LOOP; FOR cur_col IN c_non_pk_columns('Y') LOOP DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || ' IN ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE DEFAULT NULL,'); END LOOP; DBMS_Output.Put(Chr(9) || 'p_' || RPad('commit', 30, ' ') || ' IN VARCHAR2 DEFAULT ''Y'''); END; PROCEDURE GetPKParameterList IS BEGIN FOR cur_col IN c_pk_columns LOOP DBMS_Output.Put_Line(Chr(9) || 'p_' || RPad(Lower(cur_col.column_name), 30, ' ') || ' IN ' || Lower(v_table_name) || '.' || Lower(cur_col.column_name) || '%TYPE,'); END LOOP; DBMS_Output.Put(Chr(9) || 'p_' || RPad('commit', 30, ' ') || ' IN VARCHAR2 DEFAULT ''Y'''); END; PROCEDURE GetInsertColumnList IS BEGIN FOR cur_col IN c_columns LOOP IF c_columns%ROWCOUNT != 1 THEN DBMS_Output.Put_Line(','); END IF; DBMS_Output.Put(Chr(9) || Chr(9) || Lower(cur_col.column_name)); END LOOP; DBMS_Output.New_Line; END; PROCEDURE GetInsertValueList IS BEGIN FOR cur_col IN c_columns LOOP IF c_columns%ROWCOUNT != 1 THEN DBMS_Output.Put_Line(','); END IF; DBMS_Output.Put(Chr(9) || Chr(9) || 'p_' || Lower(cur_col.column_name)); END LOOP; DBMS_Output.New_Line; END; PROCEDURE GetUpdateSetList IS BEGIN FOR cur_col IN c_columns LOOP IF c_columns%ROWCOUNT != 1 THEN DBMS_Output.Put_Line(','); DBMS_Output.Put(Chr(9) || Chr(9) || Chr(9) || Chr(9)); ELSE DBMS_Output.Put(Chr(9) || 'SET '); END IF; DBMS_Output.Put(RPad(Lower(cur_col.column_name), 30, ' ') || ' = p_' || Lower(cur_col.column_name)); END LOOP; DBMS_Output.New_Line; END; PROCEDURE GetPKWhere (p_for_update IN VARCHAR2 DEFAULT NULL) IS BEGIN FOR cur_col IN c_pk_columns LOOP IF c_pk_columns%ROWCOUNT = 1 THEN DBMS_Output.Put(Chr(9) || 'WHERE '); ELSE DBMS_Output.New_Line; DBMS_Output.Put(Chr(9) || 'AND '); END IF; DBMS_Output.Put(RPad(Lower(cur_col.column_name), 30, ' ') || ' = p_' || Lower(cur_col.column_name)); END LOOP; IF p_for_update = 'Y' THEN DBMS_Output.New_Line; DBMS_Output.Put(Chr(9) || 'FOR UPDATE'); END IF; DBMS_Output.Put_Line(';'); END; PROCEDURE GetCommit IS BEGIN DBMS_Output.Put_Line(Chr(9) || 'IF p_commit = ''Y'' THEN'); DBMS_Output.Put_Line(Chr(9) || Chr(9) || 'COMMIT;'); DBMS_Output.Put_Line(Chr(9) || 'END IF;'); DBMS_Output.New_Line; END; PROCEDURE GetSeparator IS BEGIN DBMS_Output.Put_Line('-- -----------------------------------------------------------------------'); END; BEGIN DBMS_Output.Enable(1000000); -- --------------------- -- Package Specification -- --------------------- DBMS_Output.Put_Line('-- -----------------------------------------------------------------------'); DBMS_Output.Put_Line('-- Name : ' || Lower(v_table_name) || '_api.pkh'); DBMS_Output.Put_Line('-- Created By : Tim Hall'); DBMS_Output.Put_Line('-- Created Date: ' || To_Char(Sysdate, 'DD-Mon-YYYY')); DBMS_Output.Put_Line('-- Description : API procedures for the ' || v_table_name || ' table.'); DBMS_Output.Put_Line('-- Ammendments :'); DBMS_Output.Put_Line('-- ' || To_Char(Sysdate, 'DD-Mon-YYYY') || ' TSH Initial Creation'); DBMS_Output.Put_Line('-- -----------------------------------------------------------------------'); DBMS_Output.Put_Line('CREATE OR REPLACE PACKAGE ' || Lower(v_table_name) || '_api AS'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('TYPE cursor_type IS REF CURSOR;'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('PROCEDURE Sel ('); GetPKParameterList; DBMS_Output.New_Line; DBMS_Output.Put_Line(Chr(9) || RPad('p_recordset', 32, ' ') || ' OUT cursor_type'); DBMS_Output.Put_Line(');'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('PROCEDURE Ins ('); GetParameterList; DBMS_Output.Put_Line(');'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('PROCEDURE Upd ('); GetParameterList; DBMS_Output.Put_Line(');'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('PROCEDURE Del ('); GetPKParameterList; DBMS_Output.Put_Line(');'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('END ' || Lower(v_table_name) || '_api;'); DBMS_Output.Put_Line('/'); -- ------------ -- Package Body -- ------------ DBMS_Output.Put_Line('-- -----------------------------------------------------------------------'); DBMS_Output.Put_Line('-- Name : ' || Lower(v_table_name) || '_api.pkg'); DBMS_Output.Put_Line('-- Created By : Tim Hall'); DBMS_Output.Put_Line('-- Created Date: ' || To_Char(Sysdate, 'DD-Mon-YYYY')); DBMS_Output.Put_Line('-- Description : API procedures for the ' || v_table_name || ' table.'); DBMS_Output.Put_Line('-- Ammendments :'); DBMS_Output.Put_Line('-- ' || To_Char(Sysdate, 'DD-Mon-YYYY') || ' TSH Initial Creation'); DBMS_Output.Put_Line('-- -----------------------------------------------------------------------'); DBMS_Output.Put_Line('CREATE OR REPLACE PACKAGE BODY ' || Lower(v_table_name) || '_api AS'); DBMS_Output.Put_Line(Chr(9)); -- Select GetSeparator; DBMS_Output.Put_Line('PROCEDURE Sel ('); GetPKParameterList; DBMS_Output.New_Line; DBMS_Output.Put_Line(Chr(9) || RPad('p_recordset', 32, ' ') || ' OUT cursor_type'); DBMS_Output.Put_Line(') IS'); GetSeparator; DBMS_Output.Put_Line('BEGIN'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line(Chr(9) || 'OPEN p_recordset FOR'); DBMS_Output.Put_Line(Chr(9) || 'SELECT'); GetInsertColumnList; DBMS_Output.Put_Line(Chr(9) || 'FROM ' || Lower(v_table_name)); GetPKWhere; DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('END Sel;'); GetSeparator; DBMS_Output.Put_Line(Chr(9)); -- Insert GetSeparator; DBMS_Output.Put_Line('PROCEDURE Ins ('); GetParameterList; DBMS_Output.Put_Line(') IS'); GetSeparator; DBMS_Output.Put_Line('BEGIN'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line(Chr(9) || 'INSERT INTO ' || Lower(v_table_name)); DBMS_Output.Put_Line(Chr(9) || '('); GetInsertColumnList; DBMS_Output.Put_Line(Chr(9) || ')'); DBMS_Output.Put_Line(Chr(9) || 'VALUES'); DBMS_Output.Put_Line(Chr(9) || '('); GetInsertValueList; DBMS_Output.Put_Line(Chr(9) || ');'); DBMS_Output.Put_Line(Chr(9)); GetCommit; DBMS_Output.Put_Line('END Ins;'); GetSeparator; DBMS_Output.Put_Line(Chr(9)); -- Update GetSeparator; DBMS_Output.Put_Line('PROCEDURE Upd ('); GetParameterList; DBMS_Output.Put_Line(') IS'); GetSeparator; DBMS_Output.Put_Line('BEGIN'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line(Chr(9) || 'UPDATE ' || Lower(v_table_name)); GetUpdateSetList; GetPKWhere; DBMS_Output.Put_Line(Chr(9)); GetCommit; DBMS_Output.Put_Line('END Upd;'); GetSeparator; DBMS_Output.Put_Line(Chr(9)); -- Delete GetSeparator; DBMS_Output.Put_Line('PROCEDURE Del ('); GetPKParameterList; DBMS_Output.Put_Line(') IS'); GetSeparator; DBMS_Output.Put_Line('BEGIN'); DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line(Chr(9) || 'DELETE FROM ' || Lower(v_table_name)); GetPKWhere; DBMS_Output.Put_Line(Chr(9)); GetCommit; DBMS_Output.Put_Line('END Del;'); GetSeparator; DBMS_Output.Put_Line(Chr(9)); DBMS_Output.Put_Line('END ' || Lower(v_table_name) || '_api;'); DBMS_Output.Put_Line('/'); END; / SPOOL OFF SET ECHO ON SET TERMOUT ON SET FEEDBACK ON