среда, 7 апреля 2010 г.

export oracle objects into separate files from commandline

I need this to export regularly objects from command line and add them into svn
to monitor shanges in oracle database.

Idea:
Create sql file for sqlplus oracle tool that will create temporary file
that will do export

Name: oradump.sql
WHENEVER SQLERROR EXIT 1
SET SQLBLANKLINES ON
SET HEADING OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
--hope nobody produce one line longer then 2k
SET LINESIZE 2000
SET LONGCHUNKSIZE 2000
--hope this will be enough to export views...
SET LONG 64000
SET PAGESIZE 0
SET VERIFY OFF

--the schema to export
define p_schema="&1"
--the schema to export
define p_mask="&2"
--the target directory
define p_dir="&3"

define p_pcr="CREATE OR REPLACE"

select 'prepare export '||chr(10)||'objects like "&p_mask" from schema "&p_schema" into "&p_dir"' from dual
/
ALTER SESSION SET CURRENT_SCHEMA = &p_schema
/
--create temp export sctipt
SET TERMOUT OFF
spool tmp.sql
/
prompt WHENEVER SQLERROR EXIT 1
prompt SET SQLBLANKLINES ON    
prompt SET HEADING OFF         
prompt SET FEEDBACK OFF        
prompt SET TRIMSPOOL ON        
prompt SET LINESIZE 2000
prompt SET LONGCHUNKSIZE 2000
prompt SET LONG 64000
prompt SET PAGESIZE 0          
prompt SET VERIFY OFF
prompt SET TERMOUT OFF
prompt ALTER SESSION SET CURRENT_SCHEMA = &p_schema
prompt /

--create out dirs for sources
select 'host md "&p_dir'||chr(92)||type||'"' 
    from user_source
    where type like '&p_mask'
    group by type
    order by type
/


select 'spool "&p_dir'||chr(92)||type||chr(92)||name||'.sql"'||chr(10)
        ||'select decode(line,1,''&p_pcr '','''')||text from user_source where name='''||name||''' and type='''||type||''' order by line'||chr(10)
        ||'/'||chr(10)
        ||'spool off'
    from user_source 
    where type like '&p_mask'
    group by name,type 
    order by type,name
/


--create out dirs for views
select 'host md "&p_dir'||chr(92)||'VIEW'||'"' 
    from dual
    where 'VIEW' like '&p_mask'
/

select 'spool "&p_dir'||chr(92)||'VIEW'||chr(92)||v.view_name||'.sql"'||chr(10)
        ||'select ''&p_pcr VIEW '||v.view_name||' (''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'select ''   ''||column_name||decode(column_id,'||to_char(vc.colcount)||','''','','') '
            ||'from user_tab_cols where table_name='''||v.view_name||''' order by column_id'
            ||chr(10)||'/'||chr(10)
        ||'select '') AS''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'select text from user_views where view_name='''||v.view_name||''''
            ||chr(10)||'/'||chr(10)
        ||'select chr(10)||''/''||chr(10) from dual'
            ||chr(10)||'/'||chr(10)
        ||'spool off'
    from user_views v,(select max(column_id) colcount,table_name from user_tab_cols group by table_name) vc
    where v.view_name=vc.table_name
    and 'VIEW' like '&p_mask'
    and v.view_name like 'VACC%'
    order by v.view_name

/



prompt exit
spool off


exit
/

Now let's create command file to start this script:
Name: oradump.cmd


@echo off
cd "%~dp0"

set oradump_srv=MY_SERVER
set oradump_uid=MY_USER
set oradump_sch=MY_SCHEMA
set oradump_dir=.\output
set oradump_obj=%%

if "%oradump_pwd%"=="" set /P oradump_pwd=PASS (visible)

sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@oradump.sql" %oradump_sch% "%oradump_obj%" "%oradump_dir%"
echo do export
sqlplus -L -S %oradump_uid%/%oradump_pwd%@%oradump_srv% "@tmp.sql"

del tmp.sql 2>nul

exit 0

Now create "output" directory,
define correct values for variables in oradump.cmd
and you can export objects (types, packages, procedures, functions, views)
into separate files