Question Details [ID: 803098416492]
              
             Print 
             Help 

         QuestionsSearch/Archives

        Search  Archives  [ Question Details ]  

        
                    
                    
            partha -- Thanks for the question regarding "Convert Long Column to 
            Varchar2", version 8.1.6

            You Asked

I have following select statement from which i'm generating a Create trigger 
statement, can you please help me how do i concatenate complete text of 
trigger_body(long) to other fields.

Eg:

select 'CREATE OR REPLACE TRIGGER'||' '||trigger_name||' 
'||description,trigger_body
from all_triggers
where owner = 'XXX';

Thanks in advance.

Partha


            and we said...

You cannot (nor do you want to)

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

select
'create or replace trigger "' ||
         trigger_name || '"' || chr(10)||
 decode( substr( trigger_type, 1, 1 ),
         'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
              chr(10) ||
 triggering_event || chr(10) ||
 'ON "' || table_owner || '"."' ||
       table_name || '"' || chr(10) ||
 decode( instr( trigger_type, 'EACH ROW' ), 0, null,
            'FOR EACH ROW' ) || 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

followup to comment one

Add a set long 5000 (or more) to the script -- sorry, I have that in my 
login.sql to show upto 5000 bytes of a long -- not the default of 80
                    
            Was this response helpful to you? Let us know!   Reviews   
                  Generating create Trigger statement  June 14, 2001 
                  Reviewer:  helen clark  from USA 
                  i can't see complete info of the trigger, when i ran the 
                  statement?. Partha 

                  Generate Create Statement for Triggers  June 14, 2001 
                  Reviewer:  Partha  from USA 
                  This is very useful for DBA's. Partha 




            Bookmark this page with the link HERE 
                        Information 


                               This page provides the details of the question 
                              asked. To find another question click on the 
                              search tab. To view the question archives by week 
                              click on the archives tab. 








       
      Copyright  2001 Oracle Corporation, All rights reserved. 


