Add support for Audit Columns

[softinn72]

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

[ogobrecht]

Hi Paolo,

this is a very useful feature request – thanks for the question.

As you can see in the commit history there was not so much time in the last months to work on the project, but now the winter is coming with more spare time...

Are you testing with the latest release 0.4.1 or with the current development branch 0.5?

We will keep you updated, when the feature is available in the dev branch for testing.

Best regards Ottmar

[softinn72]

Ottmar, I am testing the latest stable release, even though in production we are still at version 0.3.0. I will wait for a new dev branch to test, but in the meantime I have modified the local package to skip my audit columns.

Thanks, Paolo

[softinn72]

Ottmar, I would like to add a small update to the enhancement request: if you will implement a mechanism to somehow exclude the Audit Columns from the generated API package, is it possible to keep them instead in the generated DML view?

When building a form on that DML view it's very likely that the user wants to show also the data from those fields in the generated Apex form...

Thanks, Paolo

[ogobrecht]

Hi Paolo,

the current 0.5-dev branch is merged in the 0.5 branch. We are not finished with all the todos but you should be able to start the testing. Please read the first sections To Do and Done - there was a lot ongoing.

Attention: The SQL Developer integration is currently missing/not working, documentation is unfinished and the example-api.md is not updated yet.

But all your enhancement requests should (hopefully) work - please start testing and let us know what do you think.

The 0.5 branch will be untouched until we finished the next todos in the 0.5-dev branch. Please keep in mind that 0.5 branch is still in beta - do not use it in production.

Best regards Ottmar

[softinn72]

Thanks Ottmar, I have tried compiling the 0.5 branch, but it fails on an 11g database (ver_le_12_1 does not exists...). I compiled it successfully on a 12.2 database, but the example with minimal parameters I tried fails as follows:

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

ORA-06502: PL/SQL: numeric or value error
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HR.OM_TAPIGEN", line 4049
ORA-06512: at line 1
ORA-06512: at "HR.OM_TAPIGEN", line 2649
ORA-06512: at "HR.OM_TAPIGEN", line 3728
ORA-06512: at line 3

Hope it helps, Paolo

[ogobrecht]

Hi Paolo,

thanks for your info - I could reproduce it: This is the case when no API is existing for this table and is raised by the pipelined function view_existing_apis - I had reworked this function and obviously had always an existing API and so I had never this error. I am currently reworking again the custom defaults for Jacek and have to fix the problem with the conditional compilation and ver_le_12_1. You will have (hopefully) a new version tomorrow - I will let you know.

Thanks again and best regards Ottmar

[ogobrecht]

Hi Paolo,

the fixes are merged into the 0.5 branch. Please try again. I was able to compile on a 11gR2 XE. Sorry, the conditional compilation was not aligned to run on a 11g and I was implementing on a 12.1 instance...

If you have any questions or comments, please let us know...

Best regards Ottmar

[softinn72]

Ottmar, it seems the generator does even more than I asked (didn't think about the getters for the audit columns...). Additionally the generated code is more readable now (no more long lines for the parameter list). I did not yet have the time to actually test the generated code, but I hope to give it a try soon.

Thanks a lot for the great work!

[ogobrecht]

Hi Paolo,

thank you for your feedback. This helps to stay motivated :-)

Best regards Ottmar

[ogobrecht]

Hi Paolo,

the new version 0.5.0 is out. It supports the exclusion of columns as requested. Therefore I close now this issue.

Please let us know, if you have any further questions.

Best regards Ottmar