The below script can be used to back up the required code files all at one go. The driving criteria can be modified according to the requirement of individual projects. The code will be backed up as files in the database server in the specified location one file per database object.
DECLARE
v_ddl_statement CLOB;
v_index NUMBER;
v_length NUMBER;
c_chunk_size NUMBER := 4000;
v_chunk_size NUMBER;
c_path VARCHAR2(100) := '/usr/tmp'; --Edit this to an ORacle directory
or a valid unix path with utl_file write permission
v_file_name VARCHAR2(100);
v_data_chunk VARCHAR2(32767);
v_handle utl_file.file_type;
/*Edit the below cursor to set the selection criteria
according to your requirements*/
CURSOR cur_object IS
SELECT object_type,
owner,
object_name
FROM dba_objects
WHERE object_type NOT IN ('PACKAGE BODY', 'SCHEMA', 'DIRECTORY') --These object
types are not supported. Package will extract both spec and body
/* Edit the below conditions as
suitable*/
AND object_name LIKE 'XXBIZ%EAM%'
--AND created > SYSDATE - 5
AND rownum < 5;
BEGIN
FOR object_rec IN cur_object
LOOP
v_file_name := object_rec.object_name || '_' ||
object_rec.object_type || '.sql';
dbms_output.put_line(object_rec.object_name
|| ' to ' || v_file_name);
SELECT dbms_metadata.get_ddl(object_rec.object_type,
object_rec.object_name,
object_rec.owner)
INTO v_ddl_statement
FROM dual;
v_index
:= 1;
v_length :=
dbms_lob.getlength(v_ddl_statement);
v_handle := utl_file.fopen(c_path,
v_file_name,
'W',
32767);
LOOP
v_chunk_size := least(c_chunk_size,
v_length -
v_index);
v_data_chunk :=
dbms_lob.substr(v_ddl_statement,
v_chunk_size,
v_index);
utl_file.put_line(v_handle,
v_data_chunk);
EXIT WHEN v_chunk_size < c_chunk_size;
v_index := v_index + v_chunk_size;
END LOOP;
utl_file.put_line(v_handle,
'/');
utl_file.put_line(v_handle,
'');
utl_file.fclose(v_handle);
END LOOP;
END;
/
