Docs should make clear how to apply DDL for e.g. Oracle package

[GoogleCodeExporter]

> What steps will reproduce the problem?
1. Create an Oracle package definition containing several procedures in
000_Create_XXX_Package.sql
2. Run DbDeploy
3. Always fails at second procedure of package body

> What is the expected output? What do you see instead?

It should create the package. Code is fine: runs perfectly against the
database. I see:

[sql] Executing resource:
C:\Projects\Helm\distribution\database\ora\Helm-schemaUpgrade.sql
[sql] Failed to execute:    procedure check_val( id in integer, l_cname in
varchar2, l_old in varchar2, l_new in varchar2)

BUILD FAILED
C:\Projects\Helm\build.xml:254: java.sql.SQLException: ORA-00900: invalid
SQL statement

> What version of the product are you using? On what operating system?
v3.0, Windows XP

> Please provide any additional information below.
Always fails on the 2nd procedure in the package definition, not the body.
See attached files. 0046_Create_Audit_Package.sql is where it fails.

Original issue reported on code.google.com by ponybre...@gmail.com on 21 Oct 2009 at 12:16

Attachments:

• 0046_Create_Audit_Package.sql
• 0047_Create_Audit_Package_Body.sql

[GoogleCodeExporter]

dbdeploy, like the ant sql task, defaults to using a delimiter of ";", 
splitting a 
your file wherever this delimiter appears.

When creating an oracle package the entire declaration must be presented to 
oracle in 
a single chunk.  So you need to use a different delimiter.

If you use sqlplus, you need to delimit with "/" on a line on its own. To get 
dbdeploy to do this, use these parameters to the ant task:

 delimiter="/" delimitertype="row"

(Note that both of your example files need to be presented to oracle in a 
single 
statement, so it doesn't matter what you set the delimiter to as long as it's 
not the 
default of ";".)

See UsingTheAntInterface for more information.  I'll keep this ticket open 
because 
this is a common problem and the docs should be clearer.

Original comment by gtack...@googlemail.com on 8 Nov 2009 at 2:48

• Changed title: Docs should make clear how to apply DDL for e.g. Oracle package
• Changed state: Accepted
• Added labels: Component-Docs

[GoogleCodeExporter]

Yes, that did. Guilty as charged for not reading the docs. Thanks a lot for the
detailed, helpful response. And my apologies for the late reply.

Original comment by ponybre...@gmail.com on 7 Dec 2009 at 1:35