Re: plsql trigger ddl extraction

KevJohnP wrote:

> 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;
>> /
>

With one proviso. Before executing the DBMS_METADATA do the following:

SQL> SET LONG 10000 or some other suitably sized number.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)