You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Paul Osborne <pa...@morley-computing.co.uk> on 2005/02/16 00:29:55 UTC
PL/SQL in generated Oracle scripts
I am trying to use the Maven Torque plugin to generate and initialize an
Oracle 8i database. However, the torque 'insert-sql' target is failing
because the generated SQL contains PL/SQL code, giving the error:
[torque-sql-exec] [ERROR] Failed to execute: DECLARE TABLE_DOES_NOT_EXIST
EXCEPTION. Am I using the wrong torque target to execute the scripts
The SQL scripts appear to be correct and work perfectly via sql*plus.
I've successfully managed to create the schema for MySQL without any
problems but am struggling with Oracle.
Any help would be greatly appreciated!
My properties are set as follows:
torque.project = hwh
torque.database = oracle
torque.targetPackage = com.channel4.projects.learning.hwh.torque
torque.database.driver = oracle.jdbc.driver.OracleDriver
torque.database.host = 127.0.0.1
torque.database.createUrl = jdbc:oracle:thin:@localhost:1521:dev8i
torque.database.buildUrl = jdbc:oracle:thin:@localhost:1521:dev8i
torque.database.default = hwh
profile=oracle
jcdAlias = default
databaseName = ${torque.project}
databaseUser = ${torque.database.user}
databasePassword = ${torque.database.password}
dbmsName = Oracle
jdbcLevel = 3.0
jdbcRuntimeDriver = ${torque.database.driver}
urlProtocol = jdbc
urlSubprotocol = oracle:thin
urlDbalias = dev8i
Sample of the generated SQL...
----------------------------------------------------------------------------
-
-- subject
----------------------------------------------------------------------------
-
DECLARE
TABLE_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE subject CASCADE CONSTRAINTS';
EXCEPTION
WHEN TABLE_DOES_NOT_EXIST THEN NULL;
END;
/
CREATE TABLE subject
(
id NUMBER (10) NOT NULL,
name VARCHAR2 (30) NOT NULL,
CONSTRAINT name_unique UNIQUE (name)
)
/
ALTER TABLE subject
ADD CONSTRAINT subject_PK
PRIMARY KEY (id)
/
Re: PL/SQL in generated Oracle scripts
Posted by Thomas Fischer <fi...@seitenbau.net>.
Nuno Miguel Rodrigues <nm...@gmail.com> schrieb am 16.02.2005
10:35:52:
> On Tue, 15 Feb 2005 23:29:55 -0000, Paul Osborne
> <pa...@morley-computing.co.uk> wrote:
> > I am trying to use the Maven Torque plugin to generate and initialize
an
> > Oracle 8i database. However, the torque 'insert-sql' target is failing
> > because the generated SQL contains PL/SQL code, giving the error:
> > [torque-sql-exec] [ERROR] Failed to execute: DECLARE
TABLE_DOES_NOT_EXIST
> > EXCEPTION. Am I using the wrong torque target to execute the scripts
> >
> > The SQL scripts appear to be correct and work perfectly via sql*plus.
> >
> > I've successfully managed to create the schema for MySQL without any
> > problems but am struggling with Oracle.
> >
> > Any help would be greatly appreciated!
>
> [...]
>
> PL/SQL is a different environment from that of SQL, hence the error.
> sql+ handles PL/SQL.
>
No, I do not think this is the point. The point is that a different line
separator (/) has to be used in ant(maven) to run the scripts.
> HTH,
>
>
> --
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: PL/SQL in generated Oracle scripts
Posted by Nuno Miguel Rodrigues <nm...@gmail.com>.
On Tue, 15 Feb 2005 23:29:55 -0000, Paul Osborne
<pa...@morley-computing.co.uk> wrote:
> I am trying to use the Maven Torque plugin to generate and initialize an
> Oracle 8i database. However, the torque 'insert-sql' target is failing
> because the generated SQL contains PL/SQL code, giving the error:
> [torque-sql-exec] [ERROR] Failed to execute: DECLARE TABLE_DOES_NOT_EXIST
> EXCEPTION. Am I using the wrong torque target to execute the scripts
>
> The SQL scripts appear to be correct and work perfectly via sql*plus.
>
> I've successfully managed to create the schema for MySQL without any
> problems but am struggling with Oracle.
>
> Any help would be greatly appreciated!
[...]
PL/SQL is a different environment from that of SQL, hence the error.
sql+ handles PL/SQL.
HTH,
--
Nuno Miguel Rodrigues <nm...@gmail.com>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: PL/SQL in generated Oracle scripts
Posted by Paul Osborne <pa...@morley-computing.co.uk>.
Thanks Thomas,
Yes, I'm using Torque 3.1.1. So, I'll make the necessary changes to the
templates.
Paul.
-----Original Message-----
From:
torque-user-return-5801-paul.osborne=morley-computing.co.uk@db.apache.org
[mailto:torque-user-return-5801-paul.osborne=morley-computing.co.uk@db.apach
e.org] On Behalf Of Thomas Fischer
Sent: 16 February 2005 07:47
To: Apache Torque Users List
Subject: RE: PL/SQL in generated Oracle scripts
Hi Paul,
I suppose you use Torque 3.1.1. In that version, a change was introduced in
order not to let the "drop table" statements fail if the table is not
already there (e.g. if you start with an empty database). But this change
produces more problem than it solves, so I removed it again in CVS. In
particular, it produces invalid scripts which cannot be executed 'by hand'.
For oracle, please use the sql templates either from cvs HEAD or from
Torque 3.1. To do this, replace the directories templates/sql/base/oracle,
templates/sql/db-init/oracle, templates/sql/load/oracle and the file
templates/sql/id-table/id-table.oracle.
Thomas
"Paul Osborne" <pa...@morley-computing.co.uk> schrieb am 16.02.2005
00:29:55:
> I am trying to use the Maven Torque plugin to generate and initialize an
> Oracle 8i database. However, the torque 'insert-sql' target is failing
> because the generated SQL contains PL/SQL code, giving the error:
> [torque-sql-exec] [ERROR] Failed to execute: DECLARE TABLE_DOES_NOT_EXIST
> EXCEPTION. Am I using the wrong torque target to execute the scripts
>
> The SQL scripts appear to be correct and work perfectly via sql*plus.
>
> I've successfully managed to create the schema for MySQL without any
> problems but am struggling with Oracle.
>
> Any help would be greatly appreciated!
>
> My properties are set as follows:
>
> torque.project = hwh
>
> torque.database = oracle
>
> torque.targetPackage = com.channel4.projects.learning.hwh.torque
>
> torque.database.driver = oracle.jdbc.driver.OracleDriver
>
> torque.database.host = 127.0.0.1
>
> torque.database.createUrl = jdbc:oracle:thin:@localhost:1521:dev8i
>
> torque.database.buildUrl = jdbc:oracle:thin:@localhost:1521:dev8i
>
> torque.database.default = hwh
>
> profile=oracle
>
> jcdAlias = default
>
> databaseName = ${torque.project}
>
> databaseUser = ${torque.database.user}
>
> databasePassword = ${torque.database.password}
>
> dbmsName = Oracle
>
> jdbcLevel = 3.0
>
> jdbcRuntimeDriver = ${torque.database.driver}
>
> urlProtocol = jdbc
>
> urlSubprotocol = oracle:thin
>
> urlDbalias = dev8i
>
>
>
> Sample of the generated SQL...
>
>
----------------------------------------------------------------------------
> -
>
> -- subject
>
>
----------------------------------------------------------------------------
> -
>
> DECLARE
>
> TABLE_DOES_NOT_EXIST EXCEPTION;
>
> PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
>
> BEGIN
>
> EXECUTE IMMEDIATE 'DROP TABLE subject CASCADE CONSTRAINTS';
>
> EXCEPTION
>
> WHEN TABLE_DOES_NOT_EXIST THEN NULL;
>
> END;
>
> /
>
>
>
> CREATE TABLE subject
>
> (
>
> id NUMBER (10) NOT NULL,
>
> name VARCHAR2 (30) NOT NULL,
>
> CONSTRAINT name_unique UNIQUE (name)
>
> )
>
> /
>
>
>
> ALTER TABLE subject
>
> ADD CONSTRAINT subject_PK
>
> PRIMARY KEY (id)
>
> /
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: PL/SQL in generated Oracle scripts
Posted by Thomas Fischer <fi...@seitenbau.net>.
Hi Paul,
I suppose you use Torque 3.1.1. In that version, a change was introduced in
order not to let the "drop table" statements fail if the table is not
already there (e.g. if you start with an empty database). But this change
produces more problem than it solves, so I removed it again in CVS. In
particular, it produces invalid scripts which cannot be executed 'by hand'.
For oracle, please use the sql templates either from cvs HEAD or from
Torque 3.1. To do this, replace the directories templates/sql/base/oracle,
templates/sql/db-init/oracle, templates/sql/load/oracle and the file
templates/sql/id-table/id-table.oracle.
Thomas
"Paul Osborne" <pa...@morley-computing.co.uk> schrieb am 16.02.2005
00:29:55:
> I am trying to use the Maven Torque plugin to generate and initialize an
> Oracle 8i database. However, the torque 'insert-sql' target is failing
> because the generated SQL contains PL/SQL code, giving the error:
> [torque-sql-exec] [ERROR] Failed to execute: DECLARE TABLE_DOES_NOT_EXIST
> EXCEPTION. Am I using the wrong torque target to execute the scripts
>
> The SQL scripts appear to be correct and work perfectly via sql*plus.
>
> I've successfully managed to create the schema for MySQL without any
> problems but am struggling with Oracle.
>
> Any help would be greatly appreciated!
>
> My properties are set as follows:
>
> torque.project = hwh
>
> torque.database = oracle
>
> torque.targetPackage = com.channel4.projects.learning.hwh.torque
>
> torque.database.driver = oracle.jdbc.driver.OracleDriver
>
> torque.database.host = 127.0.0.1
>
> torque.database.createUrl = jdbc:oracle:thin:@localhost:1521:dev8i
>
> torque.database.buildUrl = jdbc:oracle:thin:@localhost:1521:dev8i
>
> torque.database.default = hwh
>
> profile=oracle
>
> jcdAlias = default
>
> databaseName = ${torque.project}
>
> databaseUser = ${torque.database.user}
>
> databasePassword = ${torque.database.password}
>
> dbmsName = Oracle
>
> jdbcLevel = 3.0
>
> jdbcRuntimeDriver = ${torque.database.driver}
>
> urlProtocol = jdbc
>
> urlSubprotocol = oracle:thin
>
> urlDbalias = dev8i
>
>
>
> Sample of the generated SQL...
>
>
----------------------------------------------------------------------------
> -
>
> -- subject
>
>
----------------------------------------------------------------------------
> -
>
> DECLARE
>
> TABLE_DOES_NOT_EXIST EXCEPTION;
>
> PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
>
> BEGIN
>
> EXECUTE IMMEDIATE 'DROP TABLE subject CASCADE CONSTRAINTS';
>
> EXCEPTION
>
> WHEN TABLE_DOES_NOT_EXIST THEN NULL;
>
> END;
>
> /
>
>
>
> CREATE TABLE subject
>
> (
>
> id NUMBER (10) NOT NULL,
>
> name VARCHAR2 (30) NOT NULL,
>
> CONSTRAINT name_unique UNIQUE (name)
>
> )
>
> /
>
>
>
> ALTER TABLE subject
>
> ADD CONSTRAINT subject_PK
>
> PRIMARY KEY (id)
>
> /
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org