Re: plsql trigger ddl extraction

Hi Charles

For info, from 9i onwards you can get DDL using the DBMS_METADATA package, example as follows;

SELECT dbms_metadata.get_ddl('TRIGGER','MYTRIGGER','MYSCHEMA') FROM dual

KJP Charles J. Fisher wrote:

> Below I have written some pl/sql to extract trigger definitions for a
> table.
>
> Can somebody tell me:
>
> 1. If this is the best/easiest way to do this (from within sqlplus).
> 2. Why I am not getting the final right parenthesis on the WHEN clause.
> 3. Why all the tabs/whitespace remains when using dbms_output.put_line,
> but disappears when going character-by-character with dbms_output.put.
>
> I would do this with a simple select, but I can't seem to solve the
> formatting problems.
>
> --------------------------------------------------------------------------
> / Charles J. Fisher | Really, I'm not out to destroy Microsoft. /
> / cfisher_at_rhadmin.org | That will just be a completely unintentional /
> / http://rhadmin.org | side effect. -Linus Torvalds /
> --------------------------------------------------------------------------
>
>
>
> set serveroutput on size 1000000
>
> DECLARE
> mydesc user_triggers.description%TYPE;
> mywhen user_triggers.when_clause%TYPE;
>
> l_cursor integer default dbms_sql.open_cursor;
> l_buflen number := 250;
> l_long_val varchar2(250);
> l_long_len number;
> l_curpos number;
> junk integer;
> BEGIN
> dbms_sql.parse(l_cursor,
> 'select description, when_clause, trigger_body
> from user_triggers
> where table_name = ''MYTAB''',
> dbms_sql.native);
>
> dbms_sql.define_column(l_cursor, 1, mydesc, 2000);
> dbms_sql.define_column(l_cursor, 2, mywhen, 2000);
> dbms_sql.define_column_long(l_cursor, 3);
>
> junk := DBMS_SQL.EXECUTE(l_cursor);
>
> loop
> if dbms_sql.fetch_rows(l_cursor) > 0 then
> dbms_sql.column_value(l_cursor, 1, mydesc);
>
> dbms_output.put('create or replace trigger ');
>
> l_long_val := mydesc;
> l_long_len := length(mydesc);
>
> for junk in 1..l_long_len loop
> if substr(l_long_val,junk,1) = chr(10) then
> dbms_output.put_line('');
> else
> dbms_output.put(substr(l_long_val,junk,1));
> end if;
> end loop;
>
> dbms_sql.column_value(l_cursor, 2, mywhen);
>
> if mywhen is not null then
> dbms_output.put('WHEN (');
> l_long_val := mywhen;
> l_long_len := length(mywhen);
>
> for junk in 1..l_long_len loop
> if substr(l_long_val,junk,1) = chr(10) then
> dbms_output.put_line('');
> else
> dbms_output.put(substr(l_long_val,junk,1));
> end if;
> end loop;
> dbms_output.put_line(')');
>
> end if;
>
> l_curpos := 0;
>
> loop
> dbms_sql.column_value_long(l_cursor,
> 3,
> l_buflen,
> l_curpos,
> l_long_val,
> l_long_len);
>
> l_curpos := l_curpos + l_long_len;
>
> for junk in 1..l_long_len loop
> if substr(l_long_val,junk,1) = chr(10) then
> dbms_output.put_line('');
> else
> dbms_output.put(substr(l_long_val,junk,1));
> end if;
> end loop;
>
> dbms_output.put_line('');
>
> exit when l_long_len = 0;
> end loop;
>
> dbms_output.put_line('/');
> else
> exit;
> end if;
>
> end loop;
> dbms_sql.close_cursor(l_cursor);
> END;
> /
Received on Tue Nov 11 2003 - 16:34:22 CST