ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dick, Brian E." <Brian.D...@FMR.com>
Subject RE: Compiling PL/SQL
Date Tue, 07 Sep 2004 12:04:19 GMT
Cool. Good alternative to using SqlPlus.

However, I develop my code with SqlPlus and take advantage of its features like host variables,
output formatting, output spooling, etc. So using the same tool for development and building
works best for me.

-----Original Message-----
From: Jérôme Grelier [mailto:jgrelier@octo.com] 
Sent: Monday, September 06, 2004 10:00 AM
To: 'Ant Users List'
Subject: RE: Compiling PL/SQL


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

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


Mime
View raw message