Create Trigger with stored procedures by making dy

Question

Im create new trigger audit using store procedure cause want flexible column in the trigger audit

im using Oracle 12 C ..

CREATE OR REPLACE PROCEDURE DBADMIN.TEST3 (OUTPUT          OUT SYS_REFCURSOR,
                                          TABLE_NAME   IN     VARCHAR2)
IS
    N   NUMBER;
BEGIN
    N := 0;

    EXECUTE IMMEDIATE '

CREATE OR REPLACE TRIGGER DBADMIN.TA_EMPLOYEES3
    AFTER INSERT OR DELETE OR UPDATE
    ON DBADMIN.EMPLOYEES
    FOR EACH ROW
DECLARE
    SID   VARCHAR2 (30);
BEGIN
    SELECT SYS_CONTEXT ('' USERENV       '', ''    IP_ADDRESS          '') INTO IP FROM DUAL;

    SELECT SEQ#
      INTO SID1
      FROM v$session
     WHERE audsid = (SELECT USERENV ('' SESSIONID '') FROM DUAL);

    IF INSERTING
    THEN
        INSERT INTO DBADMIN.DBLOG_MONITORING_DETAIL2 (SID,
                                                          COLUMNS,
                                                          OLDVALUE,
                                                          NEWVALUE)
                 VALUES (SID1,
                         i.COLUMN_NAME,
                        'for row in (SELECT column_name from user_tab_columns where table_name=''EMPLOYEES'' loop  
                        execute immediate '':old.row.column_name '';
                      end loop;
/

32 26 PLS-00103: Encountered the symbol "FOR"

i think im bad logic in my script .. can give me better logic or repair my script its better ?? .

Answer 1

In Oracle, you should really rarely use dynamic SQL to create database objects. People sometimes abuse that functionality which is - in my opinion - what you're trying to do. The fact that you can do it doesn't mean that you should do it, e.g. you can poke your eye with a pencil, but you shouldn't do that.

Rule of thumb with dynamic SQL:

• forget about EXECUTE IMMEDIATE
• Create a local variable (a large VARCHAR2 or CLOB, depending on what you're doing)
• compose the statement (CREATE TRIGGER in your case) and
• store it into that variable
• display it on the screen using DBMS_OUTPUT.PUT_LINE
• copy/paste it and run it as a standalone statement
  • if it succeeds, you've done a good job so you can now use it in EXECUTE IMMEDIATE
  • if it fails, you'll have to try a little bit harder, debug it, fix errors and repeat the cycle

As of the error you got: this:

for row in (SELECT column_name from user_tab_columns where table_name=''EMPLOYEES'' loop  
  execute immediate '':old.row.column_name '';
end loop;

is invalid as the INSERT INTO target; you can't mix SQL and PL/SQL that way. It won't work at all, regardless dynamic SQL you use. Besides, it is obvious that it is wrong (missing closing bracket, what exactly are you executing immediately? old_row.column_name? How would you execute a column name?

In my opinion (once again), you shouldn't do it that way. If you want to create a trigger, do it - but not dynamically.