i can't see complete info of the trigger, when i ran the statement?.

Partha

This is very useful for DBA's.

Partha

thanks, just what i need.

Tom:

I tried your code to copy a trigger from one DEV to TEST but the trigger in TEST had errors.

here is the trigger in DEV and it was copied.

CREATE OR REPLACE TRIGGER pending
AFTER INSERT OR UPDATE of LOG_APP on intreq
FOR EACH ROW
WHEN (new.log_app='A')

in TEST:

create or replace trigger "PENDING"
AFTER
INSERT OR UPDATE
ON "IMSADMIN"."INTREQ"
FOR EACH ROW

It did not have the when condition and table.column format was different?

Thanks,

set echo off
set long 50000
set verify off
set feedback off
set termout off
set heading off
set pagesize 0
spool &1..sql

select
'create or replace trigger ' ||
substr(description,1,254)
|| DECODE(when_clause, null, null, 'when ( '||when_clause||')'||CHR(10)) ,
trigger_body
from user_triggers
where trigger_name = upper('&1')
/
prompt /

spool off
set verify on
set feedback on
set termout on
set heading on

Tom,

Some of views became INVLID after I changed the base tables' structure. The command of 'alter view <view_name> compile;' does not work for this case. The only way that I can make them VALID is to get the views' definition and recreate it. So I did the following:

set echo off
set verify off
set feedback off
set termout off
set heading off
set pages 0
set line 150
set long 5000
spool create_view.sql
select 'create or replace view ' || view_name || ' as' || chr(10), text, ';' from user_views
;
spool off

But the output script file (create_view.sql) does not looks okay:

==================================================
create or replace view APPLICATIONS_VIEW as select a.APPLICATION_ID, a.APPLICATION_TYPE,
a.NAME, a.EQUIPMENT_ASSET_ID, ;
a.STATUS, a.STATUS_MODIFIED_DATE, a.V
ERSION_NUMBER, a.CREATED_BY,
a.CREATION_DATE, a.MODIFIED_BY, a.MOD
IFIED_DATE, ap.PARAM_VALUE port,
ea.IP_ADDRESS host, ea.SITE_ID, ea.ty
pe
from APPLICATIONS a, APPLICATION_PARAMETER
S ap, EQUIPMENT_ASSETS ea
where a.APPLICATION_ID = ap.application_id
( + ) AND ap.PARAM_NAME ( + ) = 'PO
RT'

AND a.EQUIPMENT_ASSET_ID = ea.equipment_a
sset_id ( + )


create or replace view CSG_SERVICES_DENORMALIZED as SELECT CS.*, NVL(MCSD.ACCOUNTNUM, 0) ACCOUNT
_NUM, NVL(MVMS.SERVICE_NUM, 0) SERVI ;
CE_NUM

...
=====================================================

As you can see, the semicolon is put in the middle of create view statement. Any solution for this?

Thanks in advance for your help.

After coding the long-parsing procedures so excellently documented here in Ask Tom, I discovered that trigger text is queriable in USER_SOURCE as well as in USER_TRIGGERS. See the 9.2 SQL Language Reference, ALTER TRIGGER docs. Also the docs for the ALL_SOURCE dictionary view. Before today, I too believed that this was not available in USER_SOURCE.


SQL*Plus: Release 9.0.1.4.0 - Production on Mon Jan 24 15:15:55 2005

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select distinct type from user_source;

TYPE
------------
FUNCTION
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE

6 rows selected.

SQL> select count(*) from user_source where type='TRIGGER'
  2  and name = 'EDUCATION_TRJN';

  COUNT(*)
----------
        30

SQL>