Oracle scripting for JD Edwards

Some tips and tricks for oracle scripting:

Have a standardised header for your scripts.  Here is a standard header and footer that I like to use.  Basic and can be augmented to what you want:

 

column filename new_val filename
select 'GenerateStats_' || to_char(sysdate, 'yyyymmdd' ) filename from dual;
spool &filename

set echo on

set timing on
select to_char(sysdate,'HH24:MI:SS') from dual;
select to_char(sysdate,'YYY MM DD') from dual;

 

Script goes here

 

select to_char(sysdate,'HH24:MI:SS') from dual;
select to_char(sysdate,'YYY MM DD') from dual;

set echo off

spool off;

quit;

Something like this will spool to the current directory and log to a filename that is unique based upon day.  that is pretty nice.  You need to use things like this when you are doing cutovers or go-lives and need auditable results.

 

SELECT 'SELECT DBMS_METADATA.GET_DDL(''' || 'INDEX''' || ',''' || index_name || '''' || ',''' || 'UAFINDTA' || ''')'  || ' FROM dual ;'
from all_indexes t1 where t1.table_owner = 'UAFINDTA'
and not exists (select 1 from all_indexes t2 where t2.table_owner = 'PYFINDTA' and t1.index_name = t2.index_name  );

Remember that catalogs and functions are your friend.  The number of cool queries that you can do over all_indexes and all_tables is limitless.  The above query will generate the statements that will generate DDL for all indexes in the UAFINDTA schema that is not in PYFINDTA.

remember to add PARALLEL clauses and also NOLOGGING if you are allowed – this’ll make index generates up to 50% quicker.