Dynamic Create Trigger: Success with compilation error?

This could be a security issue.  When you "open the trigger.. [and] recompile" it, which tool or application do you use for that?  Whichever tool you use likely gets the benefit of role-based security.  This is different than your PL\SQL procedure has when it runs, since PL\SQL does not have the benefit of role-based grants.  It uses only direct (user-based) grants and/or system privileges (like "alter any table", etc.).  

To build or modify a trigger dynamically with PL\SQL you may need to grant the owner of your PL\SQL procedure the "create any trigger" and "alter any trigger" system privileges.  I'm not sure I would want that risk in a production database.  Is modifying the trigger text the only way to accomplish what you need?  It may be safer and easier to modify data in a control table, and have the trigger read from the control table to modify its action based on the data it finds there.

1. user who excute that proc has create and alter any trigger system privileges and DBA
2. There isn't any trailing "/" at the end
3. This is my code

PROCEDURE CREATE_AUDIT_TRIGGER
  (p_table_name IN VARCHAR2)
AS
  CURSOR c1 IS SELECT * FROM audit_info WHERE table_name = UPPER (p_table_name);
  v_sql            VARCHAR2 (4000) := NULL;
  v_table_name     VARCHAR2 (30)   := NULL;
  cid2             INTEGER;
  result_set       INTEGER;
  column_fix      VARCHAR2(200);
  value_fix      VARCHAR2(200);
  column_list      VARCHAR2(200);
  value_list      VARCHAR2(200);
BEGIN
  v_table_name := RTRIM(LTRIM(SUBSTR(RPAD(p_table_name, 30),1,21)));
  cid2 := DBMS_SQL.OPEN_CURSOR;  
  v_sql :=
      'CREATE OR REPLACE TRIGGER ' || v_table_name || '_audit_trig' || CHR(13)|| CHR(10)
  ||  ' AFTER INSERT OR DELETE OR UPDATE ON ' || v_table_name || CHR(13)|| CHR(10)
  ||  ' FOR EACH ROW' || CHR(13)|| CHR(10)
  ||  ' DECLARE' || CHR(13)|| CHR(10)
  ||  ' s_id VARCHAR2(10);' || CHR(13)|| CHR(10)
  ||  ' usr VARCHAR2(20);' || CHR(13)|| CHR(10)
  ||  ' BEGIN ' || CHR(13)|| CHR(10)
  ||  ' s_id:=AUDIT_PKG.GET_SESSION_ID;' || CHR(13)|| CHR(10)
  ||  ' usr:=AUDIT_PKG.GET_USER_NAME;' || CHR(13)|| CHR(10);
  column_fix:='INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action';
  for rec in c1 loop
  column_list:='';
  value_list:='';
  if rec.key_column_1 is not null then
    column_list:=column_list || ',key_column_1';
    value_list:=value_list || ',' || ' :NEW.' || rec.key_column_1 ;
  end if;
  if rec.key_column_2 is not null then
    column_list:=column_list || ',key_column_2';
    value_list:=value_list || ',' || ' :NEW.' || rec.key_column_2;
  end if;
  if rec.key_column_3 is not null then
    column_list:=column_list || ',key_column_3';
    value_list:=value_list || ',' || ' :NEW.' || rec.key_column_3;
  end if;
  if rec.c_column_1 is not null then
    column_list:=column_list || ',c_column_1';
    value_list:=value_list || ',' || ' :NEW.' || rec.c_column_1;
  end if;
  if rec.c_column_2 is not null then
    column_list:=column_list || ',c_column_2';
    value_list:=value_list || ',' || ' :NEW.' || rec.c_column_2;
  end if;
    if rec.c_column_3 is not null then
    column_list:=column_list || ',c_column_3';
    value_list:=value_list || ',' || ' :NEW.' || rec.c_column_3;
  end if;
    if rec.c_column_4 is not null then
    column_list:=column_list || ',c_column_4';
    value_list:=value_list || ',' || ' :NEW.' || rec.c_column_4;
  end if;
 if rec.c_column_5 is not null then
    column_list:=column_list || ',c_column_5';
    value_list:=value_list || ',' || ' :NEW.' || rec.c_column_5;
  end if;
  if rec.n_column_1 is not null then
    column_list:=column_list || ',n_column_1';
    value_list:=value_list || ',' || ' :NEW.' || rec.n_column_1;
  end if;
  if rec.n_column_2 is not null then
    column_list:=column_list || ',n_column_2';
    value_list:=value_list || ',' || ' :NEW.' || rec.n_column_2;
  end if;
    if rec.n_column_3 is not null then
    column_list:=column_list || ',n_column_3';
    value_list:=value_list || ',' || ' :NEW.' || rec.n_column_3;
  end if;
    if rec.n_column_4 is not null then
    column_list:=column_list || ',n_column_4';
    value_list:=value_list || ',' || ' :NEW.' || rec.n_column_4;
  end if;
    if rec.d_column_1 is not null then
    column_list:=column_list || ',d_column_1';
    value_list:=value_list || ',' || ' :NEW.' || rec.d_column_1;
  end if;
  if rec.d_column_2 is not null then
    column_list:=column_list || ',d_column_2';
    value_list:=value_list || ',' || ' :NEW.' || rec.d_column_2;
  end if;
  if rec.d_column_3 is not null then
    column_list:=column_list || ',d_column_3';
    value_list:=value_list || ',' || ' :NEW.' || rec.d_column_3;
  end if;
  exit;
  end loop;
  for rec in c1 loop
      if rec.audit_insert is not null then
          value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''I''';
          v_sql:=v_sql || 'IF INSERTING THEN '|| CHR(13)|| CHR(10);
          v_sql:=v_sql || column_fix || column_list || ') ';
          v_sql:=v_sql || value_fix || value_list || '); ' || CHR(13)|| CHR(10);
          v_sql:=v_sql || 'END IF;' || CHR(13)|| CHR(10);
      end if;
      if rec.audit_delete is not null then
          value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''D''';
          v_sql:=v_sql || 'IF DELETING THEN '|| CHR(13)|| CHR(10);
          v_sql:=v_sql || column_fix || column_list || ') ';
          v_sql:=v_sql || REPLACE(value_fix || value_list || '); ',':NEW.',':OLD.') || CHR(13)|| CHR(10);
          v_sql:=v_sql || 'END IF;'|| CHR(13)|| CHR(10);
      end if;
      if rec.audit_update is not null then
          value_fix:='VALUES(audit_seq.nextval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''O''';
          v_sql:=v_sql || 'IF UPDATING THEN '|| CHR(13)|| CHR(10);
          v_sql:=v_sql || column_fix || column_list || ') ';
          v_sql:=v_sql || value_fix || value_list || '); '|| CHR(13)|| CHR(10);
          value_fix:='VALUES(audit_seq.currval,'''|| v_table_name ||''',s_id,usr,SYSDATE,''N''';
          v_sql:=v_sql || column_fix || column_list || ') ';
          v_sql:=v_sql || REPLACE(value_fix || value_list || '); ',':NEW.',':OLD.')|| CHR(13)|| CHR(10);
          v_sql:=v_sql || 'END IF;'|| CHR(13)|| CHR(10);
      end if;
      exit;
  end loop;
  v_sql := v_sql  ||  'END;';
  DBMS_SQL.PARSE(cid2,v_sql,DBMS_SQL.NATIVE);
  result_set := DBMS_SQL.EXECUTE (cid2);
  DBMS_SQL.CLOSE_CURSOR (cid2);
END;

and this is trigger created

CREATE OR REPLACE TRIGGER chuc_vu_audit_trig
AFTER INSERT  OR DELETE  OR UPDATE
ON chuc_vu
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
 s_id VARCHAR2(10);
 usr VARCHAR2(20);
 BEGIN
 s_id:=AUDIT_PKG.GET_SESSION_ID;
 usr:=AUDIT_PKG.GET_USER_NAME;
IF INSERTING THEN
INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action,key_column_1,key_column_2) VALUES(audit_seq.nextval,'chuc_vu',s_id,usr,SYSDATE,'I', :NEW.MA_SO, :NEW.TEN);
END IF;
IF DELETING THEN
INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action,key_column_1,key_column_2) VALUES(audit_seq.nextval,'chuc_vu',s_id,usr,SYSDATE,'D', :OLD.MA_SO, :OLD.TEN);
END IF;
IF UPDATING THEN
INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action,key_column_1,key_column_2) VALUES(audit_seq.nextval,'chuc_vu',s_id,usr,SYSDATE,'O', :NEW.MA_SO, :NEW.TEN);
INSERT INTO audit_log(rec_id,table_name,session_id,user_name,action_date,action,key_column_1,key_column_2) VALUES(audit_seq.currval,'chuc_vu',s_id,usr,SYSDATE,'N', :OLD.MA_SO, :OLD.TEN);
END IF;
END;

Please help me

It looks like you are using PL\SQL to generate (potentially at least) lots of triggers for you dynamically.  That should work, in fact it looks like you have almost everything right.  You may be missing something very small, like maybe a "/" on the last line.

I would suggest creating a very simple test procedure first to create just one simple trigger on one table using all hard-coded values.  If you can get that to work successfully, then you most likely can get his much more complex process to work as well.

I have a couple other comments and questions though:
1. Which version of Oracle do you have?  If you have 8.1.5 or higher you can use the simpler, faster "execute immediate" syntax instead of "dbms_sql".

2. Triggers like this may generate a huge volume of audit data.   You may be able to reduce that and still have what you need.  If your records all have timestamps and user_id, then you wouldn't need any audit data for inserts, and you wouldn't need the ":new" values for updates, since all of that data is available in the triggering table.

3. Another way that can greatly reduce the volume of audit data is to have a history table for each table being audited that contains all of the column of the base table, plus a timestamp and user_id column if they are not already part of the base table, instead of one central audit table to track records for all tables.  Then (assuming that the base table captures the timestamp and user_id when records are inserted or updated) you only need a post-update trigger to write the primary or unique key field(s) plus  the :old values only for the column(s) actually changed.  You can use "decode" in your trigger to to do this.  You don't need to write anything to the audit tables following an insert then.  If the table allows deletes, you will need a pre-delete trigger as well to write all the field to the audit table.

1. the "/" is not need because when I append / to last line of trigger. The compilation still error.
2. I'm using 8.0.4 so it does'nt support execute immediate
3. if create history table for each I have to control a lot of tables dynamicly.

when you get this error ORA-24344, did you check the status of the trigger in DBA_TRIGGERS? What was it?

sora