plsql trigger ddl extraction

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Tue, 11 Nov 2003 21:36:38 GMT
Message-ID: <Pine.BSO.4.53.0311111527350.15768@bart.rhadmin.org>

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 - 15:36:38 CST