CODEWITHSUNDEEP
oracle-databaseant
SC_Adams
SC_Adams

Reputation: 156

Need to get error/warning when building a PL/SQL package with Ant

Using the following I can compile a PL/SQL package. 

<exec executable="sqlplus" failonerror="true" errorproperty="exit.status" logError="true">
    <arg line="MyUser/MyPassword@MyDatasource"/>
    <arg value='@C:\public\data_svn\trunk\DataBase_Scripts\packages\FSMP_PACKAGE.pkb'/>
</exec>

But if it has an error it still records a successful build. I have tried to get error codes out of the exec, but get nothing – or 0, success or not.

I also tried WHENEVER SQLERROR EXIT SQL.CODE; and WHENEVER SQLERROR EXIT WARNING SQL.CODE;

But no difference.

Any ideas?

Upvotes: 0

Views: 626

Answers (1)

SC_Adams
SC_Adams

Reputation: 156

After some more searches and experimentation I found the solution. Basically you have to use `WHENEVER SQLERROR EXIT WARNING SQL.CODE' and capture the error by querying the USER_ERROR table. Based on http://grokbase.com/t/ant/user/048v9ah8eq/compiling-pl-sql

<target name="RunExec">
    <exec executable="sqlplus" failonerror="true" errorproperty="exit.status" logError="true">
        <arg line="domain/secret@datasource"/>
        <arg value='@${buildPackage.sql}'/>    
    </exec>

In the buildPackage.sql

WHENEVER SQLERROR EXIT WARNING SQL.CODE
    @C:\packages\FSMP_PACKAGE.pks
BEGIN
    DECLARE
        NUM INTEGER;
        ERROR_TEXT USER_ERRORS.TEXT%TYPE;
    BEGIN
        SELECT Count(*) INTO NUM FROM USER_ERRORS WHERE Type
        = 'PACKAGE' AND NAME = 'FSMP_PACKAGE';
        If num > 0 Then
            SELECT TEXT INTO ERROR_TEXT FROM USER_ERRORS WHERE
            Type = 'PACKAGE' AND NAME = 'FSMP_PACKAGE';
            RAISE_APPLICATION_ERROR(-20000, ERROR_TEXT, True);
        End IF;
    END;
END;
/
@C:\packages\FSMP_PACKAGE.pkb
BEGIN
    DECLARE
        NUM INTEGER;
        ERROR_TEXT USER_ERRORS.TEXT%TYPE;
    BEGIN
        SELECT Count(*) INTO NUM FROM USER_ERRORS WHERE Type
        = 'PACKAGE BODY' AND NAME = 'FSMP_PACKAGE';
        If num > 0 Then
            SELECT TEXT INTO ERROR_TEXT FROM USER_ERRORS WHERE
            Type = 'PACKAGE BODY' AND NAME = 'FSMP_PACKAGE';
            RAISE_APPLICATION_ERROR(-20000, ERROR_TEXT, True);
        End IF;
    END;
END;
/

Note that WHENEVER SQLERROR EXIT WARNING SQL.CODE gets no semi-colon, as it’s a SQLPlus directive and there needs to be a slash between packages. The script will halt at the first error, but we use this for test deployment so the packages should have worked in our development database.

Upvotes: 1

Related Questions