ant-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anthony Bisong <abis...@yahoo.com>
Subject RE: Problems Using PLSQL In Ant SqlTask
Date Tue, 14 Sep 2004 16:07:58 GMT
Hi Jérôme,

I was refered to your ant code below on the Ant Mailing list and my question is what is in
the
${src.dir}/${packagedir}/${packagename}.pks file.  I also noticed that you passed the
"packagename" to your select statements.  Would you explain further, thanks.

 <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>





--- "Dick, Brian E." <Brian.Dick@FMR.com> wrote:

> Here's an abbreviated version of what I am using.
> 
> <?xml version="1.0" ?>
> 
> <project name="TestDb" default="test">
>    <target name="orafile">
>       <dirname  property="sqldir"  file="${sqlfile}"/>
>       <basename property="sqlbase" file="${sqlfile}"/>
> 
>       <tempfile property="build.file" suffix=".sql"/>
> 
>       <concat destfile="${build.file}" force="true" append="false">
>          <header filtering="no" trimleading="yes">
>             whenever oserror  exit failure rollback
>             whenever sqlerror exit failure rollback
>          </header>
> 
>          <filelist dir="${sqldir}" files="${sqlbase}"/>
>       </concat>
> 
>       <exec executable="sqlplus" failonerror="true">
>          <arg value="-L"/>
>          <arg value="-S"/>
>          <arg value="${OracleDb.username}/${OracleDb.password}"/>
>          <arg value="@${build.file}"/>
>       </exec>
>    </target>
> 
>    <target name="test">
>       <antcall target="orafile">
>          <param name="sqlfile"             value="test.sql"/>
>          <param name="OracleDb.username"   value="scott"/>
>          <param name="OracleDb.password"   value="tiger"/>
>       </antcall>
>    </target>
> </project>
> 
> -----Original Message-----
> From: Anthony Bisong [mailto:abisong@yahoo.com] 
> Sent: Monday, September 13, 2004 12:11 PM
> To: Ant Users List
> Subject: RE: Problems Using PLSQL In Ant SqlTask
> 
> 
> I tried this with the exec task
> 
> 1.  In build.xml file code
> 
> <project name="Datascan" default="main" basedir=".">
>     <target name="main">
>         <exec executable="C:\downloads\dbPatch-ant-script\testExecPlsql.sql" />
>     </target>
> </project>
> 
> 
> 2.  In testExecPlsql.sql file code
> 
> CONNECT usr-test/usr-pw@ORA-test-02-DBA
> 
> declare
> 	v_cust	varchar2(6);
> begin
> 	select cust_nbr into v_cust from adm_configuration;
> 
> 
> 3.  But I am getting the following:
> 
> BUILD FAILED
> C:\downloads\dbPatch-ant-script\dbPatch-build.xml:46: Execute failed: java.io.IOException:
> CreateProcess: C:\downloads\d
> bPatch-ant-script\testExecPlsql.sql error=193
> 
> 
> 
> 
> 
> --- Anthony Bisong <abisong@yahoo.com> wrote:
> 
> > When using the exec task at what point do you do the jdbc connection, is it in the
exec task
> or
> > do
> > you use the sql task for the jdbc connectivity and then call the exec task.  A sample
code
> will
> > be
> > greatly appreciated.
> > 
> > Thanks
> > 
> > 
> > --- "Dick, Brian E." <Brian.Dick@FMR.com> wrote:
> > 
> > > I use the exec task and SqlPlus to execute my PL/Sql, but another poster (Jérôme
Grelier
> > > [jgrelier@octo.com]) claims that the following works to compile packages. Maybe
you will
> find
> > > something useful in this code.
> > > 
> > > However, JDBC is not SqlPlus, so commands specific to SqlPlus (e.g. @) will
definitely not
> > work.
> > > 
> > > 
> > > <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>
> > > 
> > > -----Original Message-----
> > > From: Anthony Bisong [mailto:abisong@yahoo.com] 
> > > Sent: Friday, September 10, 2004 10:57 AM
> > > To: Ant Users List
> > > Subject: Problems Using PLSQL In Ant SqlTask
> > > 
> > > 
> > > I am having problems running oracle PL/Sql in the ant sql task.  Also it seems
the sql task
> > does
> > > not like the following sql commands: @, declare, begin.  Has anyone been able
to get PL/Sql
> > and
> > > the sql commands:  @, declare, begin to work in the sql task?
> > > 
> > > =====
> > > Anthony Bisong
> > > CELL: 770-827-5941
> > > abisong@yahoo.com
> > > 
> > > ---------------------------------------------------------------------
> > > 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
> > > 
> > > 
> > 
> > 
> > =====
> > Anthony Bisong
> > CELL: 770-827-5941
> > abisong@yahoo.com
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@ant.apache.org
> > For additional commands, e-mail: user-help@ant.apache.org
> > 
> > 
> 
> 
=== message truncated ===


=====
Anthony Bisong
CELL: 770-827-5941
abisong@yahoo.com

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


Mime
View raw message