Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pln-e!extra.newsguy.com!lotsanews.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Daniel Morgan Newsgroups: comp.databases.oracle.server Subject: Re: plsql trigger ddl extraction Date: Tue, 11 Nov 2003 16:05:16 -0800 Organization: ATS Message-ID: <1068595537.55231@yasure> 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 References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@pond178.drizzle.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 150 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:247481 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@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@x.washington.edu (replace 'x' with a 'u' to reply)