Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-out.triton.net!triton.net!newsfeeder.triton.net!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.mel.connect.com.au!news.xtra.co.nz!53ab2750!not-for-mail From: KevJohnP User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: plsql trigger ddl extraction References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 128 Message-ID: Date: Wed, 12 Nov 2003 11:34:22 +1300 NNTP-Posting-Host: 210.54.125.190 X-Complaints-To: newsadmin@xtra.co.nz X-Trace: news.xtra.co.nz 1068590062 210.54.125.190 (Wed, 12 Nov 2003 11:34:22 NZDT) NNTP-Posting-Date: Wed, 12 Nov 2003 11:34:22 NZDT Organization: Xtra Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:247476 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@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; > /