• Package om_tapigen
• Procedure compile_api
• Function compile_api_and_get_code
• Function get_code
• Procedure recreate_existing_apis
• Function view_existing_apis
• Function view_naming_conflicts
• Function util_get_column_data_default
• Function util_get_cons_search_condition
• Function util_split_to_table
• Function util_get_ora_max_name_len
• Procedure util_set_debug_on
• Procedure util_set_debug_off
• Function util_view_debug_log
• Function util_view_columns_array
• Function util_get_ddl

Package om_tapigen

This is an Oracle PL/SQL Table API Generator. It can be integrated in the Oracle SQL-Developer with an additional wrapper package for the oddgen extension.

The effort of generated API's is to reduce your PL/SQL code by calling standard procedures and functions for usual DML operations on tables. So the generated table APIs work as a logical layer between your business logic and the data. And by the way this logical layer enables you to easily seperate the data schema and the UI schema for your applications to improve security by granting only execute privs on table APIs to the application schema. In addition to that table APIs will speed up your development cycles because developers are able to set the focal point to the business logic instead of wasting time by manual creating boilerplate code for your tables.

Get Rid of Hard-Coding in PL/SQL (Steven Feuerstein)

FEATURES

• Generates small wrappers around your tables
• You only need to specify generation options once per table - parameters are saved in the package spec source and can be reused for regeneration
• Highly configurable
• Standard CRUD methods (column and row type based) and an additional create or update method
• Insert / Update / Delete of rows can be enabled or disabled
• Functions to check if a row exists (primary key based, returning boolean or varchar2)
• For each unique constraint a getter function to fetch the primary key
• Optional getter and setter for each column
• Optional generic logging (one log entry for each changed column over all API enabled tables in one generic log table - very handy to create a record history in the user interface)
• Checks for real changes during UPDATE operation and updates only if required
• Supports APEX automatic row processing by generation of an optional updatable view with an instead of trigger (which calls simply the API and, if enabled, the generic logging)

LICENSE

• The MIT License (MIT)
• Copyright (c) 2015-2018 André Borngräber, Ottmar Gobrecht

We give our best to produce clean and robust code, but we are NOT responsible, if you loose any code or data by using this API generator. By using it you accept the MIT license. As a best practice test the generator first in your development environment and decide after your tests, if you want to use it in production. If you miss any feature or find a bug, we are happy to hear from you via the GitHub Issues functionality.

DOCS

• Example API for the demo table HR.EMPLOYEES
• Getting started
• Detailed parameter descriptions
• Naming conventions
• SQL Developer integration
• Changelog

LINKS

• Project home page
• Download the latest version
• Issues

SIGNATURE

PACKAGE om_tapigen AUTHID CURRENT_USER IS 
c_generator         CONSTANT VARCHAR2(10 CHAR) := 'OM_TAPIGEN';
c_generator_version CONSTANT VARCHAR2(10 CHAR) := '0.5.0';

Procedure compile_api

Generates the code and compiles it. When the defaults are used you need only to provide the table name.

BEGIN
  om_tapigen.compile_api (p_table_name => 'EMP');
END;

SIGNATURE

PROCEDURE compile_api
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
  p_table_name                  IN all_objects.object_name%TYPE,
  p_owner                       IN all_users.username%TYPE DEFAULT USER,
  p_reuse_existing_api_params   IN BOOLEAN DEFAULT om_tapigen.c_true_reuse_existing_api_para, -- If true, all following params are ignored when API is already existing and params are extractable from spec source.
  p_enable_insertion_of_rows    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_insertion_of_row,
  p_enable_column_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_column_defaults, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN DEFAULT om_tapigen.c_true_enable_update_of_rows,
  p_enable_deletion_of_rows     IN BOOLEAN DEFAULT om_tapigen.c_false_enable_deletion_of_row,
  p_enable_parameter_prefixes   IN BOOLEAN DEFAULT om_tapigen.c_true_enable_parameter_prefix, -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN DEFAULT om_tapigen.c_true_enable_proc_with_out_pa, -- If true, a helper method with out params is generated - can be useful for managing session state (e.g. fetch process in APEX).
  p_enable_getter_and_setter    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_getter_and_sette, -- prefixedIf true, for each column get and set methods are created.
  p_col_prefix_in_method_names  IN BOOLEAN DEFAULT om_tapigen.c_true_col_prefix_in_method_na, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names
  p_return_row_instead_of_pk    IN BOOLEAN DEFAULT om_tapigen.c_false_return_row_instead_of_,
  p_enable_dml_view             IN BOOLEAN DEFAULT om_tapigen.c_false_enable_dml_view,
  p_enable_generic_change_log   IN BOOLEAN DEFAULT om_tapigen.c_false_enable_generic_change_,
  p_api_name                    IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the API - you can use substitution like #TABLE_NAME_4_20# (treated as substr(4,20))
  p_sequence_name               IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible
  p_exclude_column_list         IN VARCHAR2 DEFAULT NULL,                                     -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded)
  p_enable_custom_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_custom_defaults, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions)
  p_custom_default_values       IN xmltype DEFAULT NULL                                       -- Custom values in XML format for the previous option, if the generator provided defaults are not ok
);

Function compile_api_and_get_code

Generates the code, compiles and returns it as a CLOB. When the defaults are used you need only to provide the table name.

DECLARE
  l_clob CLOB;
BEGIN
  l_clob := om_tapigen.compile_api_and_get_code (p_table_name => 'EMP');
  --> do something with the CLOB
END;

SIGNATURE

FUNCTION compile_api_and_get_code
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
  p_table_name                  IN all_objects.object_name%TYPE,
  p_owner                       IN all_users.username%TYPE DEFAULT USER,
  p_reuse_existing_api_params   IN BOOLEAN DEFAULT om_tapigen.c_true_reuse_existing_api_para, -- If true, all following params are ignored when API is already existing and params are extractable from spec source.
  p_enable_insertion_of_rows    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_insertion_of_row,
  p_enable_column_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_column_defaults, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN DEFAULT om_tapigen.c_true_enable_update_of_rows,
  p_enable_deletion_of_rows     IN BOOLEAN DEFAULT om_tapigen.c_false_enable_deletion_of_row,
  p_enable_parameter_prefixes   IN BOOLEAN DEFAULT om_tapigen.c_true_enable_parameter_prefix, -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN DEFAULT om_tapigen.c_true_enable_proc_with_out_pa, -- If true, a helper method with out params is generated - can be useful for managing session state (e.g. fetch process in APEX).
  p_enable_getter_and_setter    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_getter_and_sette, -- prefixedIf true, for each column get and set methods are created.
  p_col_prefix_in_method_names  IN BOOLEAN DEFAULT om_tapigen.c_true_col_prefix_in_method_na, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names
  p_return_row_instead_of_pk    IN BOOLEAN DEFAULT om_tapigen.c_false_return_row_instead_of_,
  p_enable_dml_view             IN BOOLEAN DEFAULT om_tapigen.c_false_enable_dml_view,
  p_enable_generic_change_log   IN BOOLEAN DEFAULT om_tapigen.c_false_enable_generic_change_,
  p_api_name                    IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the API - you can use substitution like #TABLE_NAME_4_20# (treated as substr(4,20))
  p_sequence_name               IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible
  p_exclude_column_list         IN VARCHAR2 DEFAULT NULL,                                     -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded)
  p_enable_custom_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_custom_defaults, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions)
  p_custom_default_values       IN xmltype DEFAULT NULL                                       -- Custom values in XML format for the previous option, if the generator provided defaults are not ok
) RETURN CLOB;

Function get_code

Generates the code and returns it as a CLOB. When the defaults are used you need only to provide the table name.

This function is called by the oddgen wrapper for the SQL Developer integration.

DECLARE
  l_clob CLOB;
BEGIN
  l_clob := om_tapigen.get_code (p_table_name => 'EMP');
  --> do something with the CLOB
END;

SIGNATURE

FUNCTION get_code
( --> For detailed parameter descriptions see https://github.com/OraMUC/table-api-generator/blob/master/docs/parameters.md
  p_table_name                  IN all_objects.object_name%TYPE,
  p_owner                       IN all_users.username%TYPE DEFAULT USER,
  p_reuse_existing_api_params   IN BOOLEAN DEFAULT om_tapigen.c_true_reuse_existing_api_para, -- If true, all following params are ignored when API is already existing and params are extractable from spec source.
  p_enable_insertion_of_rows    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_insertion_of_row,
  p_enable_column_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_column_defaults, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN DEFAULT om_tapigen.c_true_enable_update_of_rows,
  p_enable_deletion_of_rows     IN BOOLEAN DEFAULT om_tapigen.c_false_enable_deletion_of_row,
  p_enable_parameter_prefixes   IN BOOLEAN DEFAULT om_tapigen.c_true_enable_parameter_prefix, -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN DEFAULT om_tapigen.c_true_enable_proc_with_out_pa, -- If true, a helper method with out params is generated - can be useful for managing session state (e.g. fetch process in APEX).
  p_enable_getter_and_setter    IN BOOLEAN DEFAULT om_tapigen.c_true_enable_getter_and_sette, -- prefixedIf true, for each column get and set methods are created.
  p_col_prefix_in_method_names  IN BOOLEAN DEFAULT om_tapigen.c_true_col_prefix_in_method_na, -- If true, a found unique column prefix is kept otherwise omitted in the getter and setter method names
  p_return_row_instead_of_pk    IN BOOLEAN DEFAULT om_tapigen.c_false_return_row_instead_of_,
  p_enable_dml_view             IN BOOLEAN DEFAULT om_tapigen.c_false_enable_dml_view,
  p_enable_generic_change_log   IN BOOLEAN DEFAULT om_tapigen.c_false_enable_generic_change_,
  p_api_name                    IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the API - you can use substitution like #TABLE_NAME_4_20# (treated as substr(4,20))
  p_sequence_name               IN all_objects.object_name%TYPE DEFAULT NULL,                 -- If not null, the given name is used for the create_row methods - same substitutions like with API name possible
  p_exclude_column_list         IN VARCHAR2 DEFAULT NULL,                                     -- If not null, the provided comma separated column names are excluded on inserts and updates (virtual columns are implicitly excluded)
  p_enable_custom_defaults      IN BOOLEAN DEFAULT om_tapigen.c_false_enable_custom_defaults, -- If true, additional methods are created (mainly for testing and dummy data creation, see full parameter descriptions)
  p_custom_default_values       IN xmltype DEFAULT NULL                                       -- Custom values in XML format for the previous option, if the generator provided defaults are not ok
) RETURN CLOB;

Procedure recreate_existing_apis

Helper to recreate all APIs in the current (or another) schema with the original call parameters (read from the package specs).

BEGIN
  om_tapigen.recreate_existing_apis;
END;

SIGNATURE

PROCEDURE recreate_existing_apis(p_owner IN all_users.username%TYPE DEFAULT USER);

Function view_existing_apis

Helper function (pipelined) to list all APIs generated by om_tapigen.

SELECT * FROM TABLE (om_tapigen.view_existing_apis);

SIGNATURE

FUNCTION view_existing_apis
(
  p_table_name all_tables.table_name%TYPE DEFAULT NULL,
  p_owner      all_users.username%TYPE DEFAULT USER
) RETURN t_tab_existing_apis
  PIPELINED;

Function view_naming_conflicts

Helper to check possible naming conflicts before the first usage of the API generator.

Also see the naming conventions of the generator.

SELECT * FROM TABLE (om_tapigen.view_naming_conflicts);
-- No rows expected. After you generated some APIs there will be results ;-)

SIGNATURE

FUNCTION view_naming_conflicts(p_owner all_users.username%TYPE DEFAULT USER)
  RETURN t_tab_naming_conflicts
  PIPELINED;

Function util_get_column_data_default

Helper to read a column data default from the dictionary. Working with long columns.

SIGNATURE

FUNCTION util_get_column_data_default
(
  p_table_name  IN VARCHAR2,
  p_column_name IN VARCHAR2,
  p_owner       VARCHAR2 DEFAULT USER
) RETURN VARCHAR2;

Function util_get_cons_search_condition

Helper to read a constraint search condition from the dictionary (not needed in 12cR1 and above, there we have a column search_condition_vc in user_constraints).

SIGNATURE

FUNCTION util_get_cons_search_condition
(
  p_constraint_name IN VARCHAR2,
  p_owner           IN VARCHAR2 DEFAULT USER
) RETURN VARCHAR2;

Function util_split_to_table

Helper function to split a string to a selectable table.

SELECT column_value FROM TABLE (om_tapigen.util_split_to_table('1,2,3,test'));

SIGNATURE

FUNCTION util_split_to_table
(
  p_string    IN VARCHAR2,
  p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN t_tab_vc2_4k
  PIPELINED;

Function util_get_ora_max_name_len

Helper function to determine the maximum length for an identifier name (e.g. column name). Returns the package constant c_ora_max_name_len, which is determined by a conditional compilation.

SIGNATURE

FUNCTION util_get_ora_max_name_len RETURN INTEGER;

Procedure util_set_debug_on

Enable (and reset) the debugging (previous debug data will be lost)

BEGIN 
  om_tapigen.util_set_debug_on;
END;

SIGNATURE

PROCEDURE util_set_debug_on;

Procedure util_set_debug_off

Disable the debugging

BEGIN 
  om_tapigen.util_set_debug_off;
END;

SIGNATURE

PROCEDURE util_set_debug_off;

Function util_view_debug_log

View the debug details. Maximum 999 API creations are captured for memory reasons. You can reset the debugging by calling om_tapigen.util_set_debug_on.

SELECT * FROM TABLE(om_tapigen.util_view_debug_log);

SIGNATURE

FUNCTION util_view_debug_log RETURN t_tab_debug_data
  PIPELINED;

Function util_view_columns_array

View the internal columns array from the last API generation. This view is independend from the debug mode, because this array is resetted for each API generation.

SELECT * FROM TABLE(om_tapigen.util_view_columns_array);

SIGNATURE

FUNCTION util_view_columns_array RETURN t_tab_debug_columns
  PIPELINED;

Function util_get_ddl

Helper for testing to get the DDL of generated objects.

SIGNATURE

FUNCTION util_get_ddl
(
  p_object_type VARCHAR2,
  p_object_name VARCHAR2,
  p_owner       VARCHAR2 DEFAULT USER
) RETURN CLOB;