oramuc / table-api-generator

Oracle Table API Generator

Oracle PL/SQL Table API Generator

• Package om_tapigen
• Procedure compile_api
• Function compile_api_and_get_code
• Function get_code
• Function view_existing_apis
• Function view_naming_conflicts

Package om_tapigen

This table API generator needs an Oracle DB version 12.1 or higher and can be integrated in the Oracle SQL-Developer with an additional wrapper package for the SQL Developer extension oddgen.

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 separate the data schema and the UI schema for your applications to improve security by granting only execute rights 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
• Highly configurable
• You can enable or disable separately insert, update and delete functionality
• Standard CRUD methods (column and row type based) and an additional create or update method
• Set based methods for high performance DML processing
• For each unique constraint a read method and a getter to fetch the primary key
• Functions to check if a row exists (primary key based, returning boolean or varchar2)
• Support for audit columns
• Support for a row version column
• Optional getter and setter for each column
• Optional 1:1 view to support the separation of concerns (also known as ThickDB/SmartDB/PinkDB paradigm)
• Optional DML view with an instead of trigger to support low code tools like APEX

PREREQUISITES

• Oracle 12.1 or higher

LICENSE

• The MIT License (MIT)
• Copyright (c) 2015-2020 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

• Changelog
• Getting started
• Detailed parameter descriptions
• Bulk processing
• Example API
• SQL Developer integration

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.6.3';

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 VARCHAR2,
  p_owner                       IN VARCHAR2 DEFAULT USER,  -- The schema, in which the API should be generated.
  p_enable_insertion_of_rows    IN BOOLEAN  DEFAULT TRUE,  -- If true, create methods are generated.
  p_enable_column_defaults      IN BOOLEAN  DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN  DEFAULT TRUE,  -- If true, update methods are generated.
  p_enable_deletion_of_rows     IN BOOLEAN  DEFAULT FALSE, -- If true, delete methods are generated.
  p_enable_parameter_prefixes   IN BOOLEAN  DEFAULT TRUE,  -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN  DEFAULT TRUE,  -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
  p_enable_getter_and_setter    IN BOOLEAN  DEFAULT TRUE,  -- If true, getter and setter methods are created for each column.
  p_col_prefix_in_method_names  IN BOOLEAN  DEFAULT TRUE,  -- 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 FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
  p_double_quote_names          IN BOOLEAN  DEFAULT TRUE,  -- If true, object names (owner, table, columns) are placed in double quotes.
  p_default_bulk_limit          IN INTEGER  DEFAULT 1000,  -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
  p_enable_dml_view             IN BOOLEAN  DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
  p_dml_view_name               IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_dml_view_trigger_name       IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_enable_one_to_one_view      IN BOOLEAN  DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
  p_one_to_one_view_name        IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_api_name                    IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_sequence_name               IN VARCHAR2 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_audit_column_mappings       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
  p_audit_user_expression       IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
  p_row_version_column_mapping  IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
  p_tenant_column_mapping       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
  p_enable_custom_defaults      IN BOOLEAN  DEFAULT FALSE, -- 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_api_code CLOB;
BEGIN
  l_api_code := om_tapigen.compile_api_and_get_code (p_table_name => 'EMP');
  --> do something with the API code
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 VARCHAR2,
  p_owner                       IN VARCHAR2 DEFAULT USER,  -- The schema, in which the API should be generated.
  p_enable_insertion_of_rows    IN BOOLEAN  DEFAULT TRUE,  -- If true, create methods are generated.
  p_enable_column_defaults      IN BOOLEAN  DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN  DEFAULT TRUE,  -- If true, update methods are generated.
  p_enable_deletion_of_rows     IN BOOLEAN  DEFAULT FALSE, -- If true, delete methods are generated.
  p_enable_parameter_prefixes   IN BOOLEAN  DEFAULT TRUE,  -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN  DEFAULT TRUE,  -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
  p_enable_getter_and_setter    IN BOOLEAN  DEFAULT TRUE,  -- If true, getter and setter methods are created for each column.
  p_col_prefix_in_method_names  IN BOOLEAN  DEFAULT TRUE,  -- 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 FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
  p_double_quote_names          IN BOOLEAN  DEFAULT TRUE,  -- If true, object names (owner, table, columns) are placed in double quotes.
  p_default_bulk_limit          IN INTEGER  DEFAULT 1000,  -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
  p_enable_dml_view             IN BOOLEAN  DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
  p_dml_view_name               IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_dml_view_trigger_name       IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_enable_one_to_one_view      IN BOOLEAN  DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
  p_one_to_one_view_name        IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_api_name                    IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_sequence_name               IN VARCHAR2 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_audit_column_mappings       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
  p_audit_user_expression       IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
  p_row_version_column_mapping  IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
  p_tenant_column_mapping       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
  p_enable_custom_defaults      IN BOOLEAN  DEFAULT FALSE, -- 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_api_code CLOB;
BEGIN
  l_api_code := om_tapigen.get_code (p_table_name => 'EMP');
  --> do something with the API code
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 VARCHAR2,
  p_owner                       IN VARCHAR2 DEFAULT USER,  -- The schema, in which the API should be generated.
  p_enable_insertion_of_rows    IN BOOLEAN  DEFAULT TRUE,  -- If true, create methods are generated.
  p_enable_column_defaults      IN BOOLEAN  DEFAULT FALSE, -- If true, the data dictionary defaults of the columns are used for the create methods.
  p_enable_update_of_rows       IN BOOLEAN  DEFAULT TRUE,  -- If true, update methods are generated.
  p_enable_deletion_of_rows     IN BOOLEAN  DEFAULT FALSE, -- If true, delete methods are generated.
  p_enable_parameter_prefixes   IN BOOLEAN  DEFAULT TRUE,  -- If true, the param names of methods will be prefixed with 'p_'.
  p_enable_proc_with_out_params IN BOOLEAN  DEFAULT TRUE,  -- If true, a helper method with out parameters is generated - can be useful for low code frontends like APEX to manage session state.
  p_enable_getter_and_setter    IN BOOLEAN  DEFAULT TRUE,  -- If true, getter and setter methods are created for each column.
  p_col_prefix_in_method_names  IN BOOLEAN  DEFAULT TRUE,  -- 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 FALSE, -- If true, the whole row instead of the pk columns is returned on create methods.
  p_double_quote_names          IN BOOLEAN  DEFAULT TRUE,  -- If true, object names (owner, table, columns) are placed in double quotes.
  p_default_bulk_limit          IN INTEGER  DEFAULT 1000,  -- The default bulk size for the set based methods (create_rows, read_rows, update_rows)
  p_enable_dml_view             IN BOOLEAN  DEFAULT FALSE, -- If true, a view with an instead of trigger is generated, which simply calls the API methods - can be useful for low code frontends like APEX.
  p_dml_view_name               IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view - you can use substitutions like #TABLE_NAME# , #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_dml_view_trigger_name       IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the DML view trigger - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_enable_one_to_one_view      IN BOOLEAN  DEFAULT FALSE, -- If true, a 1:1 view with read only is generated - useful when you want to separate the tables into an own schema without direct user access.
  p_one_to_one_view_name        IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the 1:1 view - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_api_name                    IN VARCHAR2 DEFAULT NULL,  -- If not null, the given name is used for the API - you can use substitutions like #TABLE_NAME#, #TABLE_NAME_26# or #TABLE_NAME_4_20# (treated as substr(table_name, 4, 20)).
  p_sequence_name               IN VARCHAR2 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_audit_column_mappings       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided comma separated column names are excluded and populated by the API (you don't need a trigger for update_by, update_on...).
  p_audit_user_expression       IN VARCHAR2 DEFAULT c_audit_user_expression, -- You can overwrite here the expression to determine the user which created or updated the row (see also the parameter docs...).
  p_row_version_column_mapping  IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is excluded and populated by the API with the provided SQL expression (you don't need a trigger to provide a row version identifier).
  p_tenant_column_mapping       IN VARCHAR2 DEFAULT NULL,  -- If not null, the provided column name is hidden inside the API, populated with the provided SQL expression and used as a tenant_id in all relevant API methods.
  p_enable_custom_defaults      IN BOOLEAN  DEFAULT FALSE, -- 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 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 VARCHAR2 DEFAULT NULL,
  p_owner      VARCHAR2 DEFAULT USER)
RETURN t_tab_existing_apis PIPELINED;

Function view_naming_conflicts

Helper to check possible naming conflicts before the very 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 VARCHAR2 DEFAULT USER)
RETURN t_tab_naming_conflicts PIPELINED;

table-api-generator's Issues

Toggle not to use NotNull-Constraints

Hi, the api takes care of NotNull-Columns. But there exists some trigger-logik that will places the values to these columns. It would be fine to toggle this behaviour.

Use SYS_GUID instead of sequence

Hi, is it possible to use SYS_GUID for primary key instead of sequence? APEX QuickDDL has this option but I like your generated API much more.

Tenant column filter is missing from generated views

Good evening Ottmar,
I finally found sometime to retest the latest version of the generator and it seems to me that the multitenant support in the latest version is still incomplete, since when specifying the p_tenant_column_mapping parameter, the filter condition is correctly added to the generated API package, but it's missing from the generated views (both DML view and one to one view).

After our discussion in ticket #31 , I think you mentioned the fact that the tenant filter condition was supposed to be present also in the generated views (see #31 (comment)).

Is it possible to add it?

Thanks,
Paolo Marzucco

Add example of generated API to documentation

It would be great to provide an example of the API generated using the generator into the documentation.
Without that, I need to download, install and run it to see what it actually does and if it is what I need.

A simple hr.departments table from oracle examples would be god enough.

Error installing on oracle 23c free

Hello
During installing I got the error:

SQL> @om_tapigen_install

Install github.com/OraMUC/table-api-generator

Set compiler flags
declare
*
ERROR at line 1:
ORA-01422: exact fetch returned more than the requested number of rows
ORA-06512: at line 12

I have fixed this with execute immediate q'[
select replace(regexp_substr(min(version_full), '\d+.\d+'), '.', null) as db_version
from product_component_version
where product like 'Oracle Database%' ]'

( min function)

Maybe not the way to do it but it works.

Leon

Primary key not returned on create_row when XMLTYPE column is present

Hi Ottmar,
it seems I found a bug in the generated API package where in a very specific case the create_row API function is not returning the PK after successfully creating the record. The issue is present in version 0.5.0, but it worked fine in previous versions. Our database is version 12c (12.2.0.1.0).

Here is a quick test case:

CREATE TABLE test
  (
    table_id NUMBER NOT NULL CONSTRAINT table_id_pk PRIMARY KEY
  , xml_template XMLTYPE
  );

CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1;

BEGIN
  om_tapigen.compile_api(p_table_name => 'TEST', p_sequence_name => 'TEST_SEQ');
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  l_table_id TEST.table_id%TYPE;
BEGIN
  l_table_id := test_api.create_row(p_xml_template => XMLTYPE('<TEST/>'));
  -- verify that the function did not return the PK 
  dbms_output.put_line('l_table_id='||TO_CHAR(l_table_id));
END;
/
-- verify that the record was created successfully
SELECT * FROM test;

By looking at the generated code, it's easy to spot why the PK will not be correctly returned, since the sequence value is not considered at all:

  FUNCTION create_row (
    p_table_id     IN "TEST"."TABLE_ID"%TYPE DEFAULT NULL /*PK*/,
    p_xml_template IN "TEST"."XML_TEMPLATE"%TYPE )
  RETURN "TEST"."TABLE_ID"%TYPE IS
    v_return "TEST"."TABLE_ID"%TYPE;
  BEGIN
    INSERT INTO "TEST" (
      "TABLE_ID" /*PK*/,
      "XML_TEMPLATE" )
    VALUES (
      COALESCE( p_table_id, "TEST_SEQ".nextval ),
      p_xml_template );
    -- returning clause does not support XMLTYPE,so we do here an extra fetch
    v_return := read_row ( p_table_id => p_table_id )."TABLE_ID";
    RETURN v_return;
  END create_row;

Additionally I wonder why the returning clause is not used in this case, as the following amended code works fine in database 12c:

    INSERT INTO "TEST" (
      "TABLE_ID" /*PK*/,
      "XML_TEMPLATE" )
    VALUES (
      COALESCE( p_table_id, "TEST_SEQ".nextval ),
      p_xml_template )
      RETURN "TABLE_ID"
      INTO v_return;

new Parameters p_enable_col_camel and p_enable_prefic_fnc_prc

Hello,
I like the table-api-generator.
I added 2 new parameters:

1. p_enable_col_camel: we never user camelcase column names and i don't like the reading of all the double quotes. I added the parameter for generating the api whitout double quotes.

2. p_enable_prefix_fnc_prc is a parameter that concatinate a prefix fnc_ for all functions and a prefix prc_ fro all procedures in the tapi

Thank's

Read row by unique keys lists tenant column as a parameter

The tenant column should be handled internally.

Enhancement: QuickSQL-like validation and/or transformation

Simple rules like QuickSQL e.g. /upper /trim /regex /between /min /max
These rules can be explicitly sorted by order of declaration.
Rules can be defined as column comments prior to 23c or annotations 23c onwards.

p_enable_proc_with_out_params: No OUT Parameters being generated

Hello,

First of all, thank you for the great piece of software!

I am trying to generate TAPI with p_enable_proc_with_out_params => true to use it with APEX.
The only OUT Parameters that are begin generated are in PROCEDURE read_row .

I would expect the PK in PROCEDURE create_row to be an IN OUT Parameter.
Is this a bug or am I misinterpreting the functionality?

More Background:
I want to use the generated DML View (p_enable_dml_view) in APEX for Creation of new Rows and Return Primary Key(s) after Insert active for the Standard DML Page Process. Right now it is generating ORA-22816: unsupported feature with RETURNING clause.

My Call:

 om_tapigen.compile_api(
      p_table_name => 'T_KONZERNFIRMA',
      p_owner                       => user,
      p_enable_insertion_of_rows    => true,
      p_enable_column_defaults      => false,
      p_enable_update_of_rows       => true,
      p_enable_deletion_of_rows     => true,
      p_enable_parameter_prefixes   => true,
      p_enable_proc_with_out_params => false,
      p_enable_getter_and_setter    => true,
      p_col_prefix_in_method_names  => true,
      p_return_row_instead_of_pk    => false,
      p_double_quote_names          => false,
      p_default_bulk_limit          => 1000,
      p_enable_dml_view             => true,
      p_enable_one_to_one_view      => true,
      p_api_name                    => null, -- defaults to #TABLE_NAME#_API
      p_sequence_name               => 'S#TABLE_NAME_2_50#',
      p_exclude_column_list         => null,
      p_audit_column_mappings       => 'created=ANGELEGT_AM, created_by=ANGELEGT_VON, updated=GEAENDERT_AM, updated_by=GEAENDERT_VON',
      p_audit_user_expression       => q'[coalesce(sys_context('apex$session','app_user'), sys_context('userenv','os_user'), sys_context('userenv','session_user'))]',
      p_row_version_column_mapping  => null,
      p_enable_custom_defaults      => false,
      p_custom_default_values       => null
    );

Parameter with PK is not used to insert

The generated code contains a bug:

  FUNCTION create_row( p_EMPLOYEE_ID IN EMPLOYEES_TEST."EMPLOYEE_ID"%TYPE DEFAULT NULL, p_COMMISSION_PCT IN EMPLOYEES_TEST."COMMISSION_PCT"%TYPE, p_SALARY IN EMPLOYEES_TEST."SALARY"%TYPE ) 
  RETURN EMPLOYEES_TEST."EMPLOYEE_ID"%TYPE IS
    v_pk EMPLOYEES_TEST."EMPLOYEE_ID"%TYPE;
  BEGIN
    INSERT INTO EMPLOYEES_TEST ( "EMPLOYEE_ID", "COMMISSION_PCT", "SALARY" )
      VALUES ( v_pk, p_COMMISSION_PCT, p_SALARY )
      RETURN "EMPLOYEE_ID" INTO v_pk; 
    RETURN v_pk;
  END create_row;

Parameter p_EMPLOYEE_ID is not used to insert.

Used generation parameters:

  /** 
   * This is the API for the table EMPLOYEES_TEST. 
   *
   * GENERATION OPTIONS 
   * - must be in the lines 5-25 to be reusable by the generator
   * - DO NOT TOUCH THIS until you know what you do - read the
   *   docs under github.com/OraMUC/table-api-generator ;-)
   * <options 
   *   generator="OM_TAPIGEN"
   *   generator_version="0.4.0"
   *   generator_action="GET_CODE"
   *   generated_at="2017-05-27 03:55:53"
   *   generated_by="..."
   *   p_table_name="EMPLOYEES_TEST"
   *   p_reuse_existing_api_params="TRUE"
   *   p_col_prefix_in_method_names="TRUE"
   *   p_enable_insertion_of_rows="TRUE"
   *   p_enable_update_of_rows="TRUE"
   *   p_enable_deletion_of_rows="FALSE"
   *   p_enable_generic_change_log="FALSE"
   *   p_enable_dml_view="FALSE"
   *   p_sequence_name=""/>

Generating default values for virtual columns

Hi
The system is generating default values for virtual columns.
II have created a workaround as follows:

FUNCTION list_rowcols_w_dict_defaults RETURN t_tab_list IS
v_result t_tab_list;
v_list_padding t_vc2_30;
v_index PLS_INTEGER;
v_operator_padding PLS_INTEGER;
BEGIN
v_list_padding := get_list_padding(4);
v_operator_padding := get_operator_padding;
FOR i IN 1 .. g_columns.count
LOOP
IF g_columns(i).data_default IS NOT NULL
AND g_columns(i).is_hidden_yn = 'N'
AND g_columns(i).is_virtual_yn = 'N' ---- ADJUSTMENT LVK

Usability/flexibility of API

I have few observations, nice to have features for the API.

1. the insert API should return whole row (rowtype), not just PK column. This way we allow API to be generated even if there is no PK or the PK is multi-column. When used with default values to insert record the default values, it gives additional benefit - you get the whole inserted row, so you don't need to cal the get_row API separately
2. what is the reason/value for using procedure with out %rowtype parameter? Having the function seems sufficient and much more readable when you use it in your code.
3. there is API created: get_pk_by_unique_cols, not sure what will happen if i have more than one set of unique cols. Can we have control over generation of those functions?
4. if i call the api with option p_col_prefix_in_method_names => FALSE causes the generator to with error:

Got the following error: ORA-20000: The prefix of your column names (example: prefix_rest_of_column_name) is not unique and you requested to cut off the prefix for method names. Please ensure either your column names have a unique prefix or switch the parameter p_col_prefix_in_method_names to true (SQL Developer oddgen integration: check option "Keep column prefix in method names").
ORA-06512: at "A571293.OM_TAPIGEN", line 1606
ORA-06512: at "A571293.OM_TAPIGEN", line 2310
ORA-06512: at "A571293.OM_TAPIGEN", line 2350
ORA-06512: at "A571293.OM_TAPIGEN", line 2566
ORA-06512: at "A571293.OM_TAPIGEN_ODDGEN_WRAPPER", line 124
ORA-06512: at line 13

You can reference procedure parameters with procedure name, that way you don't need to prefix column_name parameters. This will also let you avoid the common issue of variable name length > 30 char, when column name length = 30 char.

  PROCEDURE set_JOB_ID( EMPLOYEE_ID IN EMPLOYEES."EMPLOYEE_ID"%TYPE, JOB_ID IN EMPLOYEES."JOB_ID"%TYPE )
  IS
    v_row EMPLOYEES%ROWTYPE;
  BEGIN
    v_row := read_row ( EMPLOYEE_ID => set_JOB_ID.EMPLOYEE_ID );
    IF v_row."EMPLOYEE_ID" IS NOT NULL THEN
      -- update only, if the column value really differs
      IF COALESCE( v_row."JOB_ID", '@@@@@@@@@@@@@@@' ) <> COALESCE( set_JOB_ID.JOB_ID, '@@@@@@@@@@@@@@@' ) THEN
        UPDATE EMPLOYEES x
           SET x."JOB_ID" = set_JOB_ID.JOB_ID
         WHERE x."EMPLOYEE_ID" = set_JOB_ID.EMPLOYEE_ID;
      END IF;
    END IF;
  END set_JOB_ID;

Development branch: When parameter p_enable_column_defaults is set to true the compiled TAPI is sometimes invalid

• Found by @MaikMichel
• Generated example parameter: MY_TABLE_NAME.MY_COLUMN%TYP DEFAULT NULL (character E in TYPE is missing)

Default Values for Interval columns with Precision generates invalid package

When creating a table with a column of type Interval and specifying a default value with precision, the TAPI generator creates a non-compilable package.

Example code:

create sequence intervals_seq;

create table intervals(
  id number(9,0) default intervals_seq.nextval not null primary key,
  after_days INTERVAL DAY (4) TO SECOND (0) DEFAULT INTERVAL '360' DAY(3));
  
exec om_tapigen.compile_api(p_table_name => 'INTERVALS',p_enable_column_defaults => true);

This results in the following definition within the package:

FUNCTION create_row (
  p_id         IN "INTERVALS"."ID"%TYPE         DEFAULT "INTERVALS_SEQ"."NEXTVAL" /*PK*/,
  p_after_days IN "INTERVALS"."AFTER_DAYS"%TYPE DEFAULT INTERVAL '360' DAY(3) )
RETURN "INTERVALS"."ID"%TYPE;

However, it should read:

FUNCTION create_row (
  p_id         IN "INTERVALS"."ID"%TYPE         DEFAULT "INTERVALS_SEQ"."NEXTVAL" /*PK*/,
  p_after_days IN "INTERVALS"."AFTER_DAYS"%TYPE DEFAULT INTERVAL '360' DAY)
RETURN "INTERVALS"."ID"%TYPE;

We are using:
Database: Oracle Database 19c
OM_TAPIGEN Version: 0.6.3

Nullable columns do not have DEFAULT NULL in the generated API

Hi,
while using your generator (latest release 0.4.1) I noticed that nullable table columns do not get a DEFAULT NULL in the related parameter of the generated create_row API, so I am forced to declare all the parameters when calling the API instead of leaving out the nullable ones. I think this is not very user friendly, but maybe there is some other reason for this choice. Do you think you can change this behaviour?

Anyway I have modified the package locally to add the DEFAULT NULL string when a nullable column is detected. Are you interested to my small contribution?

Thanks,
Paolo

Support of multiple column PKs

Hi Ottmar,
i still lack the capability to generate an api for tables with a pk consisting of multiple columns. is there a chance this feature will be implemented?

Peter

Oracle 23c Free - Install-Script returns ORA-01422

The install script "om_tapigen_install.sql" shows "ORA-01422 exact fetch returns more"

Cause:

Very interesting - happy easter to you ;-)

Generate a function named get_default_row when parameter p_enable_column_defaults is set to true

• This idea is from @MaikMichel
• The function should return a row with all column defaults from the dictionary applied, so it is easier to create a new row with minimal value assignments

Specifying shorter table name substitution variables in p_one_to_one_view_name generates corrupted view name

Hi Ottmar,
I found a small issue in the usage of shorter table name substitution variables in p_one_to_one_view_name parameter, where when we specify for example a substitution like '#TABLE_NAME_20#_V', the generated view name is corrupted (that is we get only the first character of the table name followed by the trailing string, like X_V), while if we use #TABLE_NAME#_V, the expected view name is generated correctly.

Thanks,
Paolo Marzucco

P%COLUMN_MAPPING parameters are case sensitive

Hi Ottmar,
I am not sure if this is a real bug or not, but it bit me when I started testing the latest version and I could not understand why the parameter I just populated was not working. In summary I noticed that if I pass for example the following to the table generator:

p_row_version_column_mapping => 'Version_id=global_version_sequence.nextval'

it will not consider the parameter without raising any error, because the column name is not all uppercase. This behaviour affects all the column mapping parameters.

It's true that the example in the documentation shows the column name in uppercase, but if I specify for example a non existing column name, then no error is raised. Wouldn't it be more user friendly to raise an error if the specified column name is not found in the table definition, instead of failing silently?

Thanks,
Paolo

More control over package names

It would be great to be able to define a pattern/name for generated API .
That way any project could make the API conform to their naming standards/requirements.
In my case I'd like those to be named u3_api_#table_name#. It would be great if name would be parameter-driven.

Add default values for all attributes from cursor

I see great and ability to use this API as part of utPLSQL v3 to generate setup/cleanup API's for unit tests.
The only thing that is missing is ability to pass default values for all attributes for insert API.

I'm looking for an API that would insert a row and return inserted data.
The insert row API should require 0 parameters (insert a default dummy row into specific table).
It should also expose all attributes of row to be set if needed.
Example of generated API signature I'm thinking of:

function insert_emp(
id integer := null -- PK -> (could be also emp_id_seq.nextval or -1)
first_name varchar2 := 'Chuck',
last_name varchar2 := 'Norris'
) return emp%rowtye;

So then in my Unit Test I can call:

  ...insert_emp(first_name=>'Daddy', last_name=>'Cool');

--and test my PLSQL code 
  ut.expect( count_emp_by_name('Dady Cool').count ).to_equal(1);

I was thinking that your generator API could be extended to accept additional parameter p_default_values_crsr sys_refcursor so that it could be used like this:

DECLARE
  l_crsr SYS_REFCURSR;
BEGIN
  FOR i IN (SELECT table_name FROM user_tables /*WHERE...*/) LOOP
    open l_crsr for `SELECT * FROM '||i.table_name;
    your_install_schema.om_tapigen.compile_api(
      p_table_name                 => i.table_name,
      p_reuse_existing_api_params  => FALSE,
      p_col_prefix_in_method_names => TRUE,
	    p_enable_insertion_of_rows   => TRUE,
      p_default_values_for_insert  => l_crsr,
	    p_enable_update_of_rows      => TRUE,
      p_enable_deletion_of_rows    => FALSE,
      p_enable_generic_change_log  => FALSE,
	    p_enable_dml_view            => FALSE,
      p_sequence_name              => NULL);  
  END LOOP;
END;

Do you think it would be something fitting into your API project?

Primary key missing from create_row when identity column is used as PK

Good evening Ottmar,
it seems I found another bug in the generated table API where an identity column is used as PK of the table.
The issue seems to be present both in version 0.5.0 and 0.5.1, but it's not present in version 0.4.3.
Again our database is version 12c (12.2.0.1.0).

Here is a quick test case:

CREATE TABLE xxtest
  (
    table_id    NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT table_id_pk PRIMARY KEY
  , description VARCHAR2(40)
  );

BEGIN
  om_tapigen.compile_api(p_table_name => 'XXTEST', p_enable_dml_view => TRUE);
END;
/

-- verify that the DML trigger was created and it's invalid
 SELECT *
   FROM user_objects
  WHERE object_type = 'TRIGGER'
  AND status = 'INVALID';

By looking at the generated code, we can see that the create_row procedure call in XXTEST_IOIUD trigger makes reference to p_table_id as PK:

create or replace TRIGGER "INTERF"."XXTEST_IOIUD"
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON "XXTEST_DML_V"
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    "XXTEST_API".create_row (
      p_table_id    => :new."TABLE_ID" /*PK*/,
      p_description => :new."DESCRIPTION" );
  ELSIF UPDATING THEN
    "XXTEST_API".update_row (
      p_table_id    => :new."TABLE_ID" /*PK*/,
      p_description => :new."DESCRIPTION" );
  ELSIF DELETING THEN
    raise_application_error (-20000, 'Deletion of a row is not allowed.');
  END IF;
END "XXTEST_IOIUD";

but the PK identity column is not present in the create_row() calls in the generated table API package:

create or replace PACKAGE          "XXTEST_API" IS
  /*
  This is the API for the table "XXTEST".
...
  FUNCTION create_row (
    p_description IN "XXTEST"."DESCRIPTION"%TYPE )
  RETURN "XXTEST"."TABLE_ID"%TYPE;

  PROCEDURE create_row (
    p_description IN "XXTEST"."DESCRIPTION"%TYPE );
...

The mismatch will thus render the trigger invalid.

Problem when using identity column

At the moment we are using an identity column as primary key in each of our tables.
This identity column is configured as "generated always"

By this, it is not allowed to enter a value for this column.
It's always generated by an interal sequence.

When we use the table-api for such a table, the insert goes wrong since it tries to insert NULL here.
Is there any way this can be fixed ?

compile error

Hello,

OWNER

NAME

TYPE SEQUENCE LINE POSITION

---

TEXT

ATTRIBUTE MESSAGE_NUMBER

---

OM
OM_TAPIGEN
PACKAGE BODY 1 483 32
PLS-00201: identifier 'V' must be declared
ERROR 201

OM
OM_TAPIGEN
PACKAGE BODY 2 483 5
PL/SQL: Statement ignored
ERROR 0

This is what I get as I run the install.sql on Oracle 19.3 ( win 64 bit ) whitout apex . . . . .. .

User I install is OM granted DBA rights . . . . . ( I guess that's enough ? )

Add returning into all non-blob-columns

Hi, instead of returning just the primary-key you can return all non-blob-columns into record. And that when insert AND update is used. Why? Because that would show the results of all triggers fired on that row. In my case that is the filling of audit-columns.

Add support for multitenant applications

Hello Ottmar,
I'd like to propose an enhancement request, which I believe may be useful also for other users of the TAPI generator project in case they are working on multitenant applications.

As I've mentioned before we have standardized ourselves on Apex QuickSQL to generate the table structures for our internal projects and very often we rely on the "Security Group ID" option of QuickSQL to define the table structures for our multitenant apex applications.

We use the security_group_id field generated by QuickSQL to discriminate by tenant the data visible in our Apex application, so we can easily implement secured views which will filter the visible data based on the value of the security group we have stored in an application context (we are on 12c Standard edition hence we cannot use the VPD functionality).

Some time ago I decided to slightly modify the TAPI generator to add a new option called p_enable_secure_view, which allows the generation of an optional secure view (tablename_SV) which can be also used by the DML view created by the TAPI generator. Here is a simple example of the code we generate with the new p_enable_secure_view parameter:

CREATE TABLE xxtest
  (
    table_id    NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT table_id_pk PRIMARY KEY
  , security_group_id NUMBER
  , description VARCHAR2(40)
  );

BEGIN
  om_tapigen.compile_api(p_table_name => 'XXTEST', p_enable_secure_view => TRUE, p_enable_dml_view => TRUE);
END;
/

The resulting generated code for the TAPI package is unchanged, so I'll show here only the parts which are different:

CREATE OR REPLACE VIEW "MYSCHEMA"."XXTEST_SV" AS
SELECT "TABLE_ID" /*PK*/,
       "DESCRIPTION"
  FROM XXTEST
WHERE SECURITY_GROUP_ID = TO_NUMBER(SYS_CONTEXT('MY_SEC_CTX','MY_SECURITY_GROUP_ID'))
WITH CHECK OPTION
/

CREATE OR REPLACE VIEW "MYSCHEMA"."XXTEST_DML_V" AS
SELECT "TABLE_ID" /*PK*/,
       "DESCRIPTION"
  FROM XXTEST_SV
/

CREATE OR REPLACE TRIGGER "MYSCHEMA"."XXTEST_IOIUD"
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON "XXTEST_DML_V"
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    "XXTEST_API".create_row (
      p_table_id          => :new."TABLE_ID" /*PK*/,
      p_security_group_id => TO_NUMBER(SYS_CONTEXT('MY_SEC_CTX','MY_SECURITY_GROUP_ID')),
      p_description       => :new."DESCRIPTION" );
  ELSIF UPDATING THEN
    "XXTEST_API".update_row (
      p_table_id          => :new."TABLE_ID" /*PK*/,
      p_security_group_id => TO_NUMBER(SYS_CONTEXT('MY_SEC_CTX','MY_SECURITY_GROUP_ID')),
      p_description       => :new."DESCRIPTION" );
  ELSIF DELETING THEN
    raise_application_error (-20000, 'Deletion of a row is not allowed.');
  END IF;
END "XXTEST_IOIUD";
/

The code I implemented for the above functionality works fine for us already since a couple of years, but I wondered if you would be interested in my contributions to add such functionality to your project (of course only if you see a use for it or for the other users). I suppose that there could be other developers working on multitenant applications which may benefit from the above functionality.

Currently my code is a little project specific in a couple of places, but I believe it should be possible to make it more generic by adding a couple of new options to specify the name of the tenant related field (maybe defaulted to SECURITY_GROUP_ID as defined in QuickSQL) and to specify how to retrieve the value of the security group id to be used in the above code. What do you think about it?

Any suggestion or improvement is very welcome.

Thanks,
Paolo Marzucco

Add support for Audit Columns

Hello,
I am a new user of your table API generator and I think it's really useful. Anyway while testing it, I have found a little inconvenience that may well become an enhancement request, as I believe it could be useful also for other users.

Currently I am using QuickSQL to generate the tables for my project with the Audit Columns option (which in theory should be updated only via a database trigger generated by QuickSQL), but the generator exposes also those columns in the generated API.

Right now if I want to exclude those audit columns from the API, I should either modify the generated APIs to manually remove them (hence nullifying the possibility to regenerate them on the fly) or customize the package only for my needs.

Would it be an idea to add an additional parameter to the generator to skip or "hide" a specified set of columns, so that they can only updated via a database trigger?

Thanks a lot,
Paolo

P.S.: I am on Oracle 11g

Exception Handling

Is there any reason why there is no exception handling in this entire template ?

Boolean support Oracle 23c

HI

I have adjust the following, for boolean support

IF cols.aud_data_type IS NULL
THEN
IF regexp_replace(cols.data_type
,'(.*)') IN
('CLOB'
,'NCLOB'
,'DATE'
,'TIMESTAMP'
,'ROWID'
,'BLOB'
,'INTERVAL DAY'
,'INTERVAL YEAR TO MONTH'
,'TIMESTAMP WITH TIME ZONE'
,'TIMESTAMP WITH LOCAL TIME ZONE'
,'BOOLEAN') -- <<<<<<<
THEN

Api Generator nicht sichtbar SQLDev oddgen

Hallo Ottmar,
nach der tollen Vorstellung beim Meetup in Frankfurt habe ich die Bereitstellung auf GITHub sehnsüchtig erwartet. Heute Morgen auch gleich ausprobiert. Leider findet der ODDGen im SQLDev den TAPI Genearator nicht immer.
Das Szenario:
TAPI Generator ist im Schema A (einem Tools Schema) installiert
Grant Execute grant execute on OM_TAPIGEN_ODDGEN_WRAPPER to public;
grant execute on OM_TAPIGEN to public;
Im Schema A wird der Generator auch korrekt im SQLDev angezeigt
Aber im Schema B, dem Applikationsschema, keine Spur vom Generator

Eine Idee, was ich vergessen habe?

Gruß
Peter

CI/CD for project?

If you're interested, you can easily set up a continuous integration/testing for your project in the cloud using Travis and utPLSQL v3. If you would be interested I can help you with that.