Skip to content

Instantly share code, notes, and snippets.

@krisrice
Created May 4, 2017 14:10
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save krisrice/de6f694b5241682059968bd5beadbcf7 to your computer and use it in GitHub Desktop.
Save krisrice/de6f694b5241682059968bd5beadbcf7 to your computer and use it in GitHub Desktop.
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
Copy link

bjornmy 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