You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ant.apache.org by Jérôme Grelier <jg...@octo.com> on 2004/11/11 17:17:51 UTC
RE: Problems Using PLSQL In Ant SqlTask
I'm sorry, I went off the list for a while (that would be too long to
explain why) and totally forgot your email. I've just found it while
cleaning and sorting my emails.
There are 2 things to understand:
- Our packages are split in two files: .pks files contain package
specifications and .pkb files contain package body files.
- The files containing our packages have the same name than the packages
themselves
Therefore we only have to provide the name of the package to be able to find
the source file(s) and to execute the appropriate sql query to check
compilation errors.
If you're still interested here is how the target looks like after being
refactored using the macrodef task:
<macrodef name="compilepl">
<attribute name="srcfile"/>
<sequential>
<sql driver="${db.driver}" url="${db.url}" userid="${db.user}"
password="${db.pwd}" onerror="abort" delimitertype="row" delimiter="/"
keepformat="yes" classpath="${jdbc.jar}">
<transaction src="@{srcfile}"/>
</sql>
</sequential>
</macrodef>
<macrodef name="check.plcompilation">
<attribute name="objecttype"/>
<attribute name="package.name"/>
<sequential>
<sql driver="${db.driver}" url="${db.url}" userid="${db.user}"
password="${db.pwd}" classpath="${jdbc.jar}" delimitertype="row">
BEGIN
DECLARE
NUM INTEGER;
ERROR_TEXT USER_ERRORS.TEXT%TYPE;
BEGIN
SELECT Count(*) INTO NUM FROM USER_ERRORS WHERE Type = '@{objecttype}'
AND NAME = '@{package.name}';
If num > 0 Then
SELECT TEXT INTO ERROR_TEXT FROM USER_ERRORS WHERE Type =
'@{objecttype}' AND NAME = '@{package.name}' AND ROWNUM = 1;
RAISE_APPLICATION_ERROR(-20000, ERROR_TEXT, True);
End IF;
END;
END;
</sql>
</sequential>
</macrodef>
<macrodef name="compilepackage">
<attribute name="package.name"/>
<attribute name="package.name.tocheck" default="@{package.name}"/>
<attribute name="package.extension" default="pks"/>
<attribute name="packagebody.extension" default="pkb"/>
<sequential>
<compilepl
srcfile="${package.dir}/@{package.name}.@{package.extension}"/>
<check.plcompilation objecttype="PACKAGE"
package.name="@{package.name.tocheck}"/>
<compilepl
srcfile="${package.dir}/@{package.name}.@{packagebody.extension}"/>
<check.plcompilation objecttype="PACKAGE BODY"
package.name="@{package.name.tocheck}"/>
</sequential>
</macrodef>
You can modify them to match you specific needs.
Then in your build file you only have to write the following to be able to
compile a pl/sql package from ant:
...
<compilepackage package.name="PKG_ERR"/>
<compilepackage package.name="PKG_MSG"/>
...
Sorry for the (very long) delay,
Jerome
>-----Original Message-----
>From: Anthony Bisong [mailto:abisong@yahoo.com]
>Sent: mardi 14 septembre 2004 18:08
>To: Ant Users List; jgrelier@octo.com
>Subject: RE: Problems Using PLSQL In Ant SqlTask
>
>Hi Jirtme,
>
>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." <Br...@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 <ab...@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." <Br...@FMR.com> wrote:
>> >
>> > > I use the exec task and SqlPlus to execute my PL/Sql, but another
>poster (Jirtme 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