Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
script
var CopyOption = Java.type("java.nio.file.StandardCopyOption");
// put all files here >>>
var ddlPath = java.nio.file.FileSystems.getDefault().getPath('ddl')
var fs = java.nio.file.FileSystems.getDefault();
var f = java.nio.file.Files;
if ( ! f.exists(ddlPath)) {
f.createDirectory(ddlPath)
}
// issue the sql
// customize as needed
var objects = util.executeReturnList('select owner,object_type,object_name from all_objects where object_name not like \'%$%\' and object_type not in (\'LOB\',\'JOB\') and owner = user order by 1,2,3',null);
// loop the results
for (i = 0; i < objects.length; i++) {
// debug is nice
ctx.write( "Exporting " + objects[i].OBJECT_TYPE + ":" + objects[i].OBJECT_NAME );
out.flush();
// get the ddl
var binds = {}
binds.type = objects[i].OBJECT_TYPE == 'PACKAGE BODY'? "PACKAGE_BODY" : objects[i].OBJECT_TYPE ;
binds.name = objects[i].OBJECT_NAME;
binds.owner = objects[i].OWNER;
var ddl = util.executeReturnList('select dbms_metadata.get_ddl(:type,:name,:owner) ddl from dual',binds);
var blobStream = ddl[0].DDL.getAsciiStream();
// get the path/file handle to write to
var fname = objects[i].OBJECT_NAME;
switch(objects[i].OBJECT_TYPE) {
case 'PACKAGE':
case 'PROCEDURE':
fname = fname + ".pls";
break;
case 'PACKAGE BODY':
fname = fname + ".plb";
break;
default:
fname = fname + ".sql";
}
ctx.write( "\t\t"+fname+"\n");
var path = fs.getPath('ddl/' + fname);
// dump the file stream to the file
f.copy(blobStream,path,CopyOption.REPLACE_EXISTING);
}
/
@bjornmy

This comment has been minimized.

Copy link

commented Mar 22, 2018

Hi.
It seems like all files generated with this code is missing an ';' in the end of the file from the dbms_metadata.get_ddl output.
Generating the ddl in sql developer with dbms_metadata.get_ddl is returning this missing ';' , but when writing the stream to file with this script it is removed.
Any idea on why this is so?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.