cancel
Showing results for 
Search instead for 
Did you mean: 

ORACLE 10G - 10.2.0.1.0

SOLVED
Go to solution
Stephen Badgett
Regular Advisor

ORACLE 10G - 10.2.0.1.0

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
Not as is, is now
10 REPLIES
Yogeeraj_1
Honored Contributor

Re: ORACLE 10G - 10.2.0.1.0

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Frank de Vries
Respected Contributor

Re: ORACLE 10G - 10.2.0.1.0

Steve

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

Look before you leap
Patti Johnson
Respected Contributor

Re: ORACLE 10G - 10.2.0.1.0

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
Regular Advisor

Re: ORACLE 10G - 10.2.0.1.0

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
Not as is, is now
Patti Johnson
Respected Contributor
Solution

Re: ORACLE 10G - 10.2.0.1.0

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
Regular Advisor

Re: ORACLE 10G - 10.2.0.1.0

Thank you Patti that will work
Not as is, is now
Stephen Badgett
Regular Advisor

Re: ORACLE 10G - 10.2.0.1.0

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%')
/
Not as is, is now
Stephen Badgett
Regular Advisor

Re: ORACLE 10G - 10.2.0.1.0

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
Not as is, is now
Stephen Badgett
Regular Advisor

Re: ORACLE 10G - 10.2.0.1.0

This was a great experience and I plan on going here first next time. Good going all
Not as is, is now
Yogeeraj_1
Honored Contributor

Re: ORACLE 10G - 10.2.0.1.0

hi steve,
sorry for this late reply.

you should have run the following command before running the "exec print...":

set serveroutput on


regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)