1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
|
SQL> set serveroutput on
SQL> drop table t
Table dropped.
SQL> create table t(c1 int, c2 int, c3 int)
Table created.
SQL> --
SQL> -- generates the INSERT statement for the UPDATE trigger
SQL> -- in a existing VARCHAR2 variable (contents of p_stmt is _not_ erased)
SQL> -- with enclosing BEGIN/END
SQL> CREATE OR REPLACE PROCEDURE gen_insert(p_table_name IN VARCHAR2, p_stmt IN OUT VARCHAR2 ) IS
v_first_column BOOLEAN := TRUE;
v_char CHAR := CHR(0);
v_pattern1 CHAR(3) := ',' || CHR(10) || v_char;
v_pattern2 CHAR(2) := CHR(10);
v_table_name VARCHAR2(30) := UPPER(p_table_name);
BEGIN
p_stmt := p_stmt || 'BEGIN' || CHR(10);
p_stmt := p_stmt || 'INSERT INTO ' || LOWER(p_table_name) || '_audit' || ' ( ' || CHR(10);
-- list of columns names
FOR v_cols IN (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name)
LOOP
p_stmt := p_stmt || LOWER(v_cols.column_name) || ',' || CHR(10);
END LOOP;
-- remove last comma
p_stmt := p_stmt || v_char;
p_stmt := REPLACE(p_stmt, v_pattern1, v_pattern2);
p_stmt := p_stmt || ' )' || CHR(10) || ' VALUES ( ' || CHR(10);
v_first_column:= TRUE;
-- list of columns with ':old' prefix
FOR v_cols IN (SELECT column_name FROM user_tab_cols WHERE table_name = v_table_name)
LOOP
p_stmt := p_stmt || ':old.' || LOWER(v_cols.column_name) || ',' || CHR(10);
END LOOP;
-- remove last comma
p_stmt := p_stmt || v_char;
p_stmt := REPLACE(p_stmt, v_pattern1, v_pattern2);
p_stmt := p_stmt || ' ); ' || CHR(10);
p_stmt := p_stmt || 'END;' || CHR(10);
END;
Procedure created.
SQL> show errors
No errors.
SQL> declare code varchar2(200);
begin
gen_insert('T', code);
dbms_output.put_line(code);
end;
BEGIN
INSERT INTO t_audit (
c1,
c2,
c3
)
VALUES (
:old.c1,
:old.c2,
:old.c3
);
END;
PL/SQL procedure successfully completed.
SQL> show errors
No errors. |
Partager