How to Take Backup of Stored Procedure in Oracle

  • by

Backup of Stored Procedure

Usually, source codes of programming unit should be version-controlled by the developer team. But if they have never carried out their job, we can also do it by ourselves.

In this post, I leveraged SQL scripts that are from Ask Tom to export source codes of all programming units of a specific schema, then I wrapped them into a shell script for daily execution.

A. Main Shell Script

We need a dedicated place to store our backups.

Make Backup Directory

[oracle@test ~]$ mkdir /home/oracle/source_backup
[oracle@test ~]$ cd /home/oracle/source_backup

Edit Main Script

We wrapped the main exporting SQL script getallcode.sql into the shell script. In this case, we’d like to backup the source codes of HR.

[oracle@test source_backup]$ vi getSourceCodes.sh
#!/bin/bash
. ~/.bash_profile
USER=hr
PASSWD=welcome1
BACKUP_DIR=/home/oracle/source_backup/$USER
BACKUP_DIR_TODAY=$BACKUP_DIR/`date +'%Y%m%d'`

mkdir -p $BACKUP_DIR_TODAY
cd $BACKUP_DIR_TODAY
ln -s ../../getcode.sql getcode.sql
ln -s ../../getallcode.sql getallcode.sql
sqlplus -s $USER/$PASSWD @getallcode.sql
find $BACKUP_DIR -type d -mtime +29 -name '20*' -exec rm -rf {} \;

As you have noticed, I keep only about 30 days of source codes on the server.

Make it Executable

Don’t forget to enable the script to be executable.

[oracle@test source_backup]$ chmod u+x getSourceCodes.sh

B. Main SQL Script

The main SQL script will embed getcode.sql and make another SQL script xtmpx.sql that can recursively export source codes of programming units one by one.

[oracle@test source_backup]$ vi getallcode.sql
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER' )
/
spool off
spool getallcode_INSTALL.sql
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
exit

As you can see, we export 4 object types of programming unit.

C. Second SQL Script

[oracle@test source_backup]$ vi getcode.sql
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100

Let’s see what we have so far.

[oracle@test source_backup]$ ll
total 12
-rw-r--r-- 1 oracle oinstall 429 Sep  6 20:03 getallcode.sql
-rw-r--r-- 1 oracle oinstall 448 Sep  6 20:03 getcode.sql
-rwxr--r-- 1 oracle oinstall 370 Sep  6 20:02 getSourceCodes.sh

D. Schedule the Job

You can add the executable script to your crontab or something else. Next, let’s check the backup result.

[oracle@test source_backup]$ ll hr/20190906/
total 24
-rw-r--r-- 1 oracle oinstall 551 Sep  6 21:00 ADD_JOB_HISTORY.sql
-rw-r--r-- 1 oracle oinstall 205 Sep  6 21:00 getallcode_INSTALL.sql
lrwxrwxrwx 1 oracle oinstall  20 Sep  6 21:00 getallcode.sql -> ../../getallcode.sql
lrwxrwxrwx 1 oracle oinstall  17 Sep  6 21:00 getcode.sql -> ../../getcode.sql
-rw-r--r-- 1 oracle oinstall 323 Sep  6 21:00 SECURE_DML.sql
-rw-r--r-- 1 oracle oinstall 166 Sep  6 21:00 SECURE_EMPLOYEES.sql
-rw-r--r-- 1 oracle oinstall 269 Sep  6 21:00 UPDATE_JOB_HISTORY.sql
-rw-r--r-- 1 oracle oinstall 205 Sep  6 21:00 xtmpx.sql

Files with upper-cased name are the source codes of programming units, one file one unit. Please note that, you can add other object types to the main SQL script in order to get more types of source codes, but VIEW cannot be included.

Leave a Reply

Your email address will not be published. Required fields are marked *