copying stored procedures each into its own file using ant

Question

I want to be able to have an ant task which would connect to a remote oracle instance and copy the stored procedures each into its own file. I know I can have an ant sql task which will do SELECT object_type, object_name, dbms_metadata.get_ddl(object_type, object_name) object_ddl FROM user_objects WHERE OBJECT_TYPE in ('INDEX', 'TRIGGER', 'TABLE', 'VIEW', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'SYNONYM', 'TYPE')
ORDER BY OBJECT_TYPE, OBJECT_NAME

but this will get the result set all into one file, whereas I want a file per each procedure (this is because I intend to subsequently diff them against what's in SVN).

Any ideas?

Thanks! Alex

Answer 1

Looks like you've already figured out the hardest part on how to construct SQL statement and get the results from the database server to your machine.

Now do this.

1. Construct SQL statement to get all stored procedure names.
2. Execute <loadfile property="procnames" file="result-of-sql-run.txt"/>
3. Use antcontrib for task to iterate over stored procedure names

Here is how to iterate:

   <for list="${procnames}" delimeter="..." param="sproc-name">
      <sequential>
        <!-- Construct a new SQL statement to get specific
             stored procedure named @{sproc-name} -->
     </sequential>
   </for>

I would be interested to know how you execute SQL code and connect to the database from ant, could you publish your solution?

Answer 2

I've done something similar, and I'd suggest you write the DDL extractor/file creator in another language (I used Perl) and launch it with the Ant exec task - I know this is somewhat frowned upon, but shelling out to another language gives you some advantages over the "SELECT DBMS_METADATA.GET_DDL..." method:

(Note: the external program you'll be writing is still a wrapper around DBMS_METADATA.GET_DDL)

• You can programatically set the DBMS_METADATA.SET_TRANSFORM_PARAM options to give you a better match to your source control standards.

• You can strip out schema qualifiers if necessary.

• If your objects were not originally deployed through Ant from source control you can reduce the mismatch noise by doing some simple whitespace transformations that other client programs may introduce.

• You can better control the extensions of the files you produce if you have that standard (e.g. ".trg" for triggers).

Answer 3

Alexander, thanks so much for your help. Here's the code I used, based on your suggestion. The only disclaimer is that because I had to do dbms_lob.substr to convert the clob to varchar2, the max I could grab is 4000 characters from each procedure ddl and also, it killed the formatting during this conversion and ended all up in one line. If you have any ideas of how to rectify those two issues, let me know.

<target name="retrieve_procedures_names" depends="environment">

    <sql driver="${mp.db.driver.class}" url="${mp.db.connection.url}" userid="${mp.db.user.name}"
         password="${mp.db.password}" print="yes"
         output="${db.tmp.dir}/procedure_names.txt" onerror="stop" autocommit="true" encoding="UTF-8"
         showheaders="false" showtrailers="false">
        <classpath location="${db.driver.jar}"/>
        SELECT OBJECT_NAME FROM user_objects WHERE OBJECT_TYPE = 'PROCEDURE'
    </sql>

</target>
<target name="retrieve_procedures" depends="retrieve_procedures_names">
    <loadfile property="procnames" srcfile="${db.tmp.dir}/procedure_names.txt"/>
    <for list="${procnames}" delimiter="${line.separator}" param="sproc-name">
        <sequential>
            <sql driver="${mp.db.driver.class}" url="${mp.db.connection.url}" userid="${mp.db.user.name}"
                 password="${mp.db.password}" print="yes"
                 output="${db.tmp.dir}/@{sproc-name}.txt" onerror="stop" autocommit="true" encoding="UTF-8"
                 keepformat="true" showheaders="false" showtrailers="false">
                <classpath location="${db.driver.jar}"/>
                SELECT dbms_lob.substr(dbms_metadata.get_ddl(object_type, object_name), 4000, 1) FROM user_objects
                WHERE OBJECT_TYPE = 'PROCEDURE' and upper(object_name) = '@{sproc-name}'
            </sql>
        </sequential>
    </for>

</target>