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