ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jérôme Grelier <jgrel...@octo.com>
Subject RE: Compiling PL/SQL
Date Mon, 06 Sep 2004 14:00:00 GMT
I understand the sql task is not able to detect compilation errors (nor is
sqlplus). Based on your solution, we could then have something like this:

<target name="compilepackage">
 <sql driver="oracle.jdbc.driver.OracleDriver"
	url="${db.url}"
	userid="${db.user}"
	password="${db.password}"
	onerror="abort"
	delimitertype="row"
	delimiter="/"
	keepformat="yes"
	classpath="${jdbc.jar}">	
	
	<transaction src="${src.dir}/${packagedir}/${packagename}.pks"/>
	<transaction>
			BEGIN
			
			DECLARE
			NUM INTEGER;
			ERROR_TEXT USER_ERRORS.TEXT%TYPE;
			
			BEGIN
			
			SELECT Count(*) INTO NUM FROM USER_ERRORS WHERE Type
= 'PACKAGE' AND NAME = '${packagename}';
			
			If num > 0 Then
			SELECT TEXT INTO ERROR_TEXT FROM USER_ERRORS WHERE
Type = 'PACKAGE' AND NAME = '${packagename}';
			RAISE_APPLICATION_ERROR(-20000, ERROR_TEXT, True);
			End IF;
			
			END;

			END;
	</transaction>
	<transaction src="${src.dir}/${packagedir}/${packagename}.pkb"/>

	<transaction>
			BEGIN
			
			DECLARE
			NUM INTEGER;
			ERROR_TEXT USER_ERRORS.TEXT%TYPE;
			
			BEGIN
			
			SELECT Count(*) INTO NUM FROM USER_ERRORS WHERE Type
= 'PACKAGE BODY' AND NAME = '${packagename}';
			
			If num > 0 Then
			SELECT TEXT INTO ERROR_TEXT FROM USER_ERRORS WHERE
Type = 'PACKAGE BODY' AND NAME = '${packagename}' AND ROWNUM = 1;
			RAISE_APPLICATION_ERROR(-20000, ERROR_TEXT, True);
			End IF;
			
			END;
			
			END;
	</transaction>		
	</sql>	
  </target>

The main advantage is that you don't need sqlplus and you don't need to
modify your source files to add "WHENEVER ..."

Each time you want to compile a package you make a "antcall" and set
${packagedir} and ${packagename}.

Note: delimiter, delimitertype and keepformat attribute are important for
the task above to work properly.

Thanks for your help (as well as David).

Jerome

>-----Original Message-----
>From: Dick, Brian E. [mailto:Brian.Dick@FMR.com]
>Sent: vendredi 27 août 2004 20:00
>To: Ant Users List
>Subject: RE: Compiling PL/SQL
>
>Don't use the sql task. Use the exec task to run SqlPlus.
>
>To get SqlPlus to return an error code
>
>1) Before your procedure code put WHENEVER OSERROR and WHENEVER SQLERROR
>2) After your procedure code put
>BEGIN
>SELECT COUNT(*)
>FROM USER_ERRORS
>WHERE NAME = 'myobjname'
>AND   TYPE = 'myobjtype';
>IF SQL%ROWCOUNT > 0 THEN
>RAISE_APPLICATION_ERROR(-20000,'SqlPlus error',TRUE);
>END IF;
>END;
>/
>
>In the past, I passed the procedure file as a parameter to a script that
>wrapped it with this code. If I did it today, I would use redirectors.
>
>-----Original Message-----
>From: Jérôme Grelier [mailto:jgrelier@octo.com]
>Sent: Friday, August 27, 2004 1:31 PM
>To: user@ant.apache.org
>Subject: Compiling PL/SQL
>
>
>Hi,
>
>I would like to compile several PL/SQL packages via the sql task.
>
>Apparently the only way for the sql task to be able to do that is to set
>the
>delimitertype attribute to "row" and the delimiter attribute to "/".
>
>Setting those attributes seems to make the sql task "happy" and everything
>looks good. But ant does not detect any compilation error! Here a simple
>example in verbose mode (using ant 1.6.2):
>
>[sql] SQL:
>[sql] Create OR Replace Package pkg_utils_date
>[sql] AS
>[sql]    Function rdc_date_get_jou(
>[sql]       p_datin   IN   DATE
>[sql]    )
>[sql]       Return VARCHAR2;
>[sql] nd pkg_utils_date;
>[sql] 0 rows affected
>[sql] Committing transaction
>[sql] 1 of 1 SQL statements executed successfully
>
>BUILD SUCCESSFUL
>
>As you can see the "End..." instruction is missing one character. If I have
>a look at the database the pl/sql has been loaded with the missing
>character: it is in an invalid state and it does not compile. But ant says
>"SQL statements executed successfully" and my script does not stop.
>
>I tried to change the value of the onerror attribute but it does not change
>anything (the default value is abort anyway, that is what I would like it
>to
>do).
>
>What I am doing wrong?
>
>Thanks,
>Jerome
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
>For additional commands, e-mail: user-help@ant.apache.org
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
>For additional commands, e-mail: user-help@ant.apache.org
>



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
For additional commands, e-mail: user-help@ant.apache.org


Mime
View raw message