How to take backup of source code in flat file in oracle

In this post we are going to see how to take the backup of  source code in a text file. All you have to do write a small piece of code. You can check the source code in DBA_SOURCE view (or ALL_SOURCE). In the following procedure we are going to use the same view to get the code.

Sample Code :-

   1: CREATE OR REPLACE PROCEDURE BACKUP_SOURCE_CODE IS

   2:   crlf         VARCHAR2(2) := chr(13) || chr(10);

   3:   l_filehandle UTL_FILE.file_type;

   4:   l_file_path  VARCHAR2(20) := 'OP_DATA_DIR';

   5:   l_cur_rows   NUMBER(10, 0) := 0;

   6:   CURSOR source_info is

   7:     SELECT a.owner, a.name, a.type, a.line, a.text, b.cnt_line

   8:       FROM (SELECT owner, name, type, line, text

   9:               FROM all_source

  10:              WHERE name in (SELECT object_name

  11:                               FROM all_objects

  12:                              WHERE object_type = 'PROCEDURE'

  13:                                AND owner in ('SCOTT'))

  14:                AND text is not null

  15:              ORDER BY name, line) a,

  16:            (SELECT name, count(line) AS cnt_line

  17:               FROM all_source

  18:              WHERE name IN (SELECT object_name

  19:                               FROM all_objects

  20:                              WHERE object_type = 'PROCEDURE'

  21:                                AND owner in ('SCOTT'))

  22:                AND text IS NOT NULL

  23:              GROUP BY name

  24:              ORDER BY name) b

  25:      WHERE a.name = b.name

  26:      ORDER BY a.name, a.line;

  27:   TYPE t_source IS TABLE OF source_info%rowtype;

  28:   l_source t_source := t_source();

  29:   l_exception EXCEPTION;

  30: BEGIN

  31:  

  32:   l_filehandle := UTL_FILE.fopen(l_file_path, 'bkup_source_code.sql', 'W'); --Create/Open a file with WRITE permission

  33:   OPEN source_info;

  34:   LOOP

  35:     FETCH source_info BULK COLLECT

  36:       INTO l_source LIMIT 100; --Using bulk collect to increase performance

  37:     EXIT WHEN l_source.COUNT = 0;

  38:     l_cur_rows := source_info%rowcount;

  39:     FOR i IN 1 .. l_source.COUNT LOOP

  40:       UTL_FILE.PUT(l_filehandle, l_source(i).text);

  41:       UTL_FILE.fflush(l_FileHandle);

  42:       IF (l_source(i).line = l_source(i).cnt_line) THEN

  43:         UTL_FILE.PUT(l_filehandle, crlf);

  44:         UTL_FILE.PUT(l_filehandle, '/'); --Add '/' at the end of the each procedure

  45:         UTL_FILE.PUT(l_filehandle, crlf); --Flush the content

  46:         UTL_FILE.fflush(l_FileHandle);

  47:       END IF;

  48:     END LOOP;

  49:   END LOOP;

  50:   UTL_FILE.FCLOSE(l_filehandle); --Close the File

  51:   CLOSE source_info;

  52:   DBMS_OUTPUT.PUT_LINE('Backup process completed successfully');

  53: EXCEPTION

  54:   WHEN OTHERS THEN

  55:     DBMS_OUTPUT.PUT_LINE('Error occured while taking the backup : -' ||

  56:                          sqlerrm);

  57: END BACKUP_SOURCE_CODE;

  58: /

That’s it execute the above procedure (EXEC BACKUP_SOURCE_CODE) and the file (bkup_source_code.sql) will be created OP_DATA_DIR directory.

I did the test run of the procedure and found it take 17 second to take backup of more than 200 procedure in oracle 11G R2.

As you can see here only procedure are taken as backup, you can modify the cursor ‘source_info‘ and you can include the Function, Package, Index, Trigger etc.

And another thing to run this procedure you should have access for oracle directories and UTL_FILE utilities. For this you need to do create a oracle directory as

CREATE OR REPLACE DIRECTORY OP_DATA_DIR AS ‘/home/data_bkup’

For more information about Oracle directory check here and here. and for UTL_FILE check here.

Usage : –

Well their are lot of source code controller available but this tool comes handy if you have to move the code from one database to another database.

So till next time bye.

Advertisements

Published by

Anirban

I am software engineer by profession. Sometimes i post some topics about technology and some other topics which i find out interesting. My hobbies include watching movies,music, reading books(both technical and story books(fiction only)).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s