Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using procedures instead of coding update/insert
Chris,
It is not that difficult to generate the code.
I recently wrote a procedure that generates code to insert into a table, you provide the owner and table name. I wanted to create generic audit triggers ... but that is too much of work, so I wrote some code that would generate the audit trigger.
Then you call GENAUDPROC procedure with name of the intended procedure and it generates generic procedure code. This you can then tweak to suit your needs. This actually will reduce a whole lot of work for developers.
Our idea is to audit DML and DDL on core tables for the applications. The audit table will be owned by a special audit_user. Everyone else will have select and insert privilege on the audit tables (no update and delete privileges will be granted).
If anyone would like to test this, let me know, that would help me too to improve my code. In the next release of this code I am planning to provide some checks that can identify if the table structure has been modified so appropriate trigger and associated audit procedure can be regenerated to accommodate the table change.
Cheers
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Tuesday, January 15, 2002 1:20 PM
To: Multiple recipients of list ORACLE-L
A small con for the PL/SQL procedures is that I have to rebuild the procedure for a table when the table structure changes(column added/dropped). Granted, I could probably build a more dynamic upd/del/ins procedure for each table, but then I am trying to keep the procedure code tight.
Also, I noticed that the current version does some basic checks before executing the INSERT/UPDATE/DELETE SQL. For example, it checks to see if the record exists before executing the INSERT sql.
*********************************************************************2
This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
*********************************************************************2
Received on Tue Jan 15 2002 - 14:55:05 CST