Exporting Oracle Database stored procedures

Oracle| stored Procedure | data | database

Project is over, want to write their own stored procedures exported to save a copy of this write a simple script, take out to share with you. In fact, it is very simple, mainly used to User_procedures, user_source Two views, we can see that.

It seems that some of the online search is not comprehensive enough, and fragmented, if you feel good to support it, '

#! /usr/bin/ksh
. ~/.profile

Begintime= ' date +%y year%m month%d%h%m minutes%s seconds '

Bindir=~/work/common
Prodir=~/work/common/procedure

#连接数据库 (with parameter-s)
ora_s ()
{
~/cfg/dbetl_s
}

#连接数据库 (with no parameters)
ORA ()
{
~/cfg/dbetl
}

#获得存储过程名称
Getprocedurename ()
{
ora_s <<! >${bindir}/procedure.lst

Set heading off;
SET FEEDBACK off;
Set pagesize 0;
Set Wrap off;
Set echo off;
Set term off;
Set Linesize 1000;
Set Trimspool on;

Select object_name from User_procedures;

Exit
!

}

#导出存储过程代码
Exportproceduresource ()
{
ora_s <<! >${prodir}/${procedure_name}.sql

Set serveroutput on;
Set heading off;
SET FEEDBACK off;
Set pagesize 0;
Set Wrap off;
Set echo off;
Set term off;
Set Linesize 1000;
Set Trimspool on;

Select text from User_source a where a.name = ' ${procedure_name} ' order by A.line;

Exit
!

}

##################### Mian ##########################

Getprocedurename

mkdir Prodir

While Read procedure_name
Todo

Exportproceduresource

Done<${bindir}/procedure.lst

echo "Begin at ${begintime}"
echo "End at ' date +%y year%m month%d%h%m minutes%s seconds '"