ORACLE 10G - 10.2.0.1.0

[Stephen Badgett]

I am trying to build a SQL statement to pulls information on a triggers specifically TRIGGER NAME , DISCRIPTION AND BODY...

This is the actual trigger ....

DECLARE
v_next_val NUMBER;

BEGIN

SELECT SRB.s01_employee_no.nextval
INTO v_next_val FROM SYS.DUAL;
:new.EMPNO := v_next_val;
END;


Why does this not work ...

SQL> SELECT 'CREATE OR REPLACE '||USER||'.'||TRIGGER_NAME||' '||DBA_TRIGGERS.DESCRIPTION||' '||DBA_TRIGGERS.TRIGGER_BODY||' ' from DBA_TRIGGERS WHERE OWNER = ''||USER||'' and TRIGGER_NAME not like('BIN%');
SELECT 'CREATE OR REPLACE '||USER||'.'||TRIGGER_NAME||' '||DBA_TRIGGERS.DESCRIPTION||' '||DBA_TRIGGERS.TRIGGER_BODY||' ' from DBA_TRIGGERS WHERE OWNER = ''||USER||'' and TRIGGER_NAME not like('BIN%')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG


Now this sort of work (missing INTO v_next_val FROM SYS.DUAL; :new.EMPNO := v_next_val; END; ) ...

SQL> SELECT TRIGGER_NAME ,DESCRIPTION, TRIGGER_BODY from DBA_TRIGGERS where OWNER = 'SRB' and TRIGGER_NAME not like('BIN%');

T01_EMPLOYEE
"SRB"."T01_EMPLOYEE" BEFORE
INSERT ON "SRB"."EMPLOYEE" FOR EACH ROW
DECLARE
v_next_val NUMBER;

BEGIN

SELECT SRB.s01_employee_no.nextval



Thanks for you help in this,
Steve

[Yogeeraj_1]

hi steve,

can you create the attached procedures and try this:

SQL> exec print_table_records('select trigger_name, description, trigger_body from dba_triggers where owner=''SRB'' and trigger_name not like (''BIN%'')');

hope this helps!
kind regards
yogeeraj

[Frank de Vries]

Steve

Your problem seems quite normal
You try to get info from a column
TRIGGER_BODY that cannot be retrieved
dynamically with '||TRIGGER_BODY||'

[Patti Johnson]

Try using the dbms_metadata.get_ddl function.

set heading off
set long 40000
SELECT SYS.DBMS_METADATA.GET_DDL('TRIGGER','trigger_name','trigger_owner') FROM DUAL;

Patti

[Stephen Badgett]

Yogeeraj

I have created the two procedures and then exec the sql statement but only got back the "PL/SQL procedure successfully completed." Was there going to be output from this?

SRB > exec print_table_records('select trigger_name, description, trigger_body from dba_triggers where owner=''SRB'' ');

PL/SQL procedure successfully completed.


Patti

This is very close to what I need but, would require that I know the TRIGGER name "T01_EMPLOYEE". Is there an alternative to this? I was trying the build a create or replace TRIGGER statement to a spool file.

for example:

set heading off
set long 40000
SELECT SYS.DBMS_METADATA.GET_DDL('TRIGGER','T01_EMPLOYEE','SRB') FROM DUAL;


CREATE OR REPLACE TRIGGER "SRB"."T01_EMPLOYEE" BEFORE
INSERT ON "SRB"."EMPLOYEE" FOR EACH ROW DECLARE
v_next_val NUMBER;

BEGIN

SELECT SRB.s01_employee_no.nextval
INTO v_next_val FROM SYS.DUAL;
:new.EMPNO := v_next_val;
END;
ALTER TRIGGER "SRB"."T01_EMPLOYEE" ENABLE


Thank you all for this help,

Steve

[Patti Johnson · Accepted solution]

Steve,

You can't use a wild card with the dbms_metadata.get_ddl function, but you can use the old trick of using sql to write sql, then run the generated script.

SELECT 'SELECT SYS.DBMS_METADATA.GET_DDL(' || '''' || 'TRIGGER' || ''''|| ',' || ''''
||TRIGGER_NAME || '''' || ',' || '''' || OWNER || '''' || ')'
|| 'FROM DUAL;'
FROM
DBA_TRIGGERS WHERE OWNER = 'schema owner'
/

I added the owner name since you would only want to generate triggers for you application, not sys or system.

This could probably also be done using a pl/sql cursor, but I haven't coded that one.

Patti

[Stephen Badgett]

Thank you Patti that will work

[Stephen Badgett]

Had to exclude the the TRIGGERs that start with bin

SELECT 'SELECT SYS.DBMS_METADATA.GET_DDL(' || '''' || 'TRIGGER' || ''''|| ',' || ''''
||TRIGGER_NAME || '''' || ',' || '''' || OWNER || '''' || ')'
|| 'FROM DUAL;'
FROM
DBA_TRIGGERS WHERE OWNER = ''||USER||'' and ''||TRIGGER_NAME||'' not like('BIN%')
/

[Stephen Badgett]

Ok now I will show a solution from all your help. May look a little messy and I will try to clean it up but, this does what I need ...
____________________________________________

set linesize 132
set heading off
set echo off
set pagesize 10000
set long 40000
spool /tmp/trigger.sql

SELECT 'SELECT SYS.DBMS_METADATA.GET_DDL(' || '''' || 'TRIGGER' || ''''|| ',' || ''''||TRIGGER_NAME || '''' || ',' || '''' || OWNER || '''' || ')'|| 'FROM DUAL;' FROM DBA_TRIGGERS WHERE OWNER = ''||USER||'' and ''||TRIGGER_NAME||'' not like('BIN%')
/
spool off

host /bin/cat /tmp/trigger.sql |grep -v "TRIGGER_NAME" |grep -v "spool off"|grep -v "> SELECT"|grep -v "2 /"|grep -v ">" > /tmp/triggermake.sql



spool /tmp/tables.sql
select 'CREATE TABLESPACE "'||USER||'" '''||substr(v$datafile.name,1,80)||''' size '||dba_tablespace_usage_metrics.TABLESPACE_SIZE/100||'M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;' from dba_tablespace_usage_metrics, v$datafile WHERE dba_tablespace_usage_metrics.TABLESPACE_NAME =''||USER||'' and v$datafile.NAME LIKE ('%'||USER||'.dbf');
select 'create TABLE '||USER||'.'||tab.tname||'(TMP CHAR(1) DEFAULT '''' NOT NULL) TABLESPACE "'||USER||'";' from tab where tab.tname not like ('BIN%');
select DISTINCT'ALTER TABLE '||USER||'.'||COL.tname||' ADD ("'||COL.CNAME||'" '||COL.COLTYPE||'('||COL.WIDTH||') '||col.nulls||');' from tab,COL where COL.tname not like ('BIN%');
select 'ALTER TABLE '||USER||'.'||tab.tname||' DROP COLUMN TMP;' from tab where tab.tname not like ('BIN%');
@/tmp/triggermake.sql
spool off

host cat /tmp/tables.sql |grep -v "rows selected"|grep -v "> select"|grep -v "triggermake.sql"|grep -v "spool off" > /tmp/CREATENEWschema.sql

[Stephen Badgett]

This was a great experience and I plan on going here first next time. Good going all