Carts and SQL Developer Command Line Interface

Oracle SQL Developer is a great tool and had evolved quite a bit.  There are two features that have really helped me keep track of all the code that is generated in the database.

The first is the cart tool that generates code for selected objects.  The only problem is the clicking or dragging objects to the cart.   To solve this I wrote a package to produce a cart file.  The other cool feature is the SDCLI that can produce the DDL as specified in the cart.  That Jeff Smith did a great article recently.

But generating a cart  as new objects are  created  can be a pain.  The code for the package below takes a schema and generates a code for table, view, function,  procedure, package and body.      I generate the DDL,  not data as in the article, but that change could be added.  I have a scheduled job running every night to generate the desired schema and a shell script that uses sdcli to add the code to a SVN repository.  The scripts deletes the the target directory just before generating the files so the files are not renamed with a number.

The code uses the user UTILS and a connection known as UTILS.

 

--------------------------------------------------------
-- DDL for Package CART_TOOL
--------------------------------------------------------

CREATE OR REPLACE PACKAGE CART_TOOL AS 

 PROCEDURE do_schema( pSchema VARCHAR2, pStartdate date default null);

END CART_TOOL;

--------------------------------------------------------
-- DDL for Package Body CART_TOOL
--------------------------------------------------------

 CREATE OR REPLACE PACKAGE BODY "CART_TOOL" 
AS
 FUNCTION fillObj(
 ptype VARCHAR2,
 p_obj VARCHAR2,
 pName VARCHAR2)
 RETURN VARCHAR2
 AS
 vRet VARCHAR2(32000) ;
 crlf VARCHAR2(4) := chr(10);
 BEGIN
 vRet := '<dbobject>' 
 || crlf || ' <objectconnection><![CDATA[IdeConnections%23UTILS]]></objectconnection>' 
 || crlf || ' <objecttype><![CDATA['|| p_obj ||']]></objecttype>' 
 || crlf || ' <objectschema><![CDATA['|| ptype ||']]></objectschema>' 
 || crlf || ' <objectname><![CDATA['|| pName ||']]></objectname>' 
 || crlf || ' <include><![CDATA[true]]></include>' 
 || crlf || ' <meta><![CDATA[true]]></meta>' 
 || crlf || ' <data><![CDATA[false]]></data>' 
 || crlf || ' <filter><![CDATA[]]></filter> ' 
 || crlf || '</dbobject>';
 RETURN vRet;
 END;
 
 
 PROCEDURE do_schema(
 pSchema VARCHAR2, pStartdate date default null )
 AS
 vHeader VARCHAR2(32000);
 vObject VARCHAR2(32000);
 crlf VARCHAR2(4) := chr(10);
 vFooter VARCHAR2(32000) := ' </objects>' || crlf || '</cart>' ;
 fh utl_file.file_type;
 l_filename varchar2(256) := pSchema||'.sdcart' ;
 BEGIN
 fh := utl_file.fopen( 'OUTPUT', l_filename, 'w' , 32767 );

 vHeader := '<?xml version="1.0" encoding="UTF8" ?>' 
 || crlf || '<cart>' 
 || crlf || ' <connections>' 
 || crlf || ' <connection>'
 || crlf || ' <displayname><![CDATA[UTILS]]></displayname>' 
 || crlf || ' <connectionname><![CDATA[IdeConnections%23UTILS]]></connectionname>' 
 || crlf || ' <connectionuser><![CDATA[UTILS]]></connectionuser>' 
 || crlf || ' </connection>' 
 || crlf || ' </connections>' 
 || crlf || '<objects>' ;
 utl_file.put_line( fh, vHeader );
 FOR rec IN
 (SELECT owner
 ,object_type
 ,object_name
 FROM all_objects
 WHERE object_TYPE IN ( 'FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TABLE','VIEW' )
 and LAST_DDL_TIME >= coalesce( pStartdate, to_date('20150701','YYYYMMDD')) -- Date of db creation
 AND DECODE ( pSchema,NULL,1,owner,1,0 ) = 1
 ORDER BY
 CASE object_type
 WHEN 'TABLE' THEN 1
 WHEN 'VIEW' THEN 2
 WHEN 'PACKAGE' THEN 3
 WHEN 'FUNCTION' THEN 4
 WHEN 'PROCEDURE' THEN 5
 ELSE 6
 END
 )
 LOOP
 utl_file.put_line( fh, fillObj( rec.owner,rec.object_type,rec.object_name ));
 END LOOP;
 utl_file.put_line(fh, vFooter );
 utl_file.fclose( fh );
 END do_schema;
 
 
END CART_TOOL;

/


## script snippet
for f in `ls /OUTPUT/*.sdcart`
 do 
 d=${f##*/} 
 d=${d%.*}
 echo "Exporting $d"
 d=$base_dir$d 
 rm -rf $d

 sdcli cart export -cart $f -tgt $d -cfg $base_dir/export_tool.xml 
 rm -f $f 
 done 


Bookmark the permalink.

Leave a Reply