Dynamically creating Oracle PL/SQL packages vis JDBC

110 views
Skip to first unread message

DMZ

unread,
Jan 27, 2003, 11:16:00 AM1/27/03
to
I am writing a code generator that generates hundreds of PL/SQL
packages for testing. Currently they are generated to files, but
because they are frequently regnerated, to save time it is preferable
if the packages could be compiled by the Java program at the same time
they are generated (the files would still be created so they can be
used if needed).

The string variables "packageSpec" and "packageBody" below have the
entire "CREATE OR REPLACE PACKAGE" to "END;" i.e. the same contents as
the package files which get created.

Statement stmt = conn.createStatement();
stmt.execute(packageSpec);
stmt.execute(packageBody);

No matter what, the package and package body always are invalid in the
database after this runs, although the contents are correct. If I do
SHOW ERRORS or ALTER PACKAGE COMPILE in SQL Plus, the errors below
show up.

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/31 PLS-00103: Encountered the symbol "" when expecting one of
the
following:
end function package pragma private procedure subtype type
use <an identifier> <a double-quoted delimited-identifier>
cursor form current

However, if I bring up the package from the database in SQL Navigator
or Oracle Forms (after the generator has attempted to create it in
the database) and click the "Save" button to compile it without making
any changes, it compiles successfully!

I have tried stripping the trailing slash ("/") before sending it to
the JDBC Statement, and still have the same problem. Stripping the
last semi-colon doesn't work either.

Database privileges are not an issue here, as the packages are being
compiled in my own schema, and the Java code is able to create the
contents of the all packages in the database (although they remain
invalid unless compiled in SQL Navigator or SQL Plus as described
above).

What am I doing wrong? What do I really need to do to get a PL/SQL
package properly created at runtime via JDBC?

I am running Oracle 8.1.7.4 on the server, with JDK 1.4.1 and Oracle's
classes12.zip JDBC thin drivers on the client OC where the generator
runs.


Thanks in advance.

DMZ

unread,
Jan 27, 2003, 7:07:56 PM1/27/03
to
I have figured out the problem.

The PL/SQL package files were being generated for use on Windows, so
other developers could read them on their Windows PCs using Notepad or
whatever their favorite editor is. So each line of code was
terminated with the DOS/Windows line-terminating characters "\r\n".

When I transformed all occurrences of "\r\n" to "\n" before passing
the package creation script to the JDBC statement, it worked fine.
Apparently Oracle's JDBC drivers didn't like those double-character
line terminators, and it seems that SQL Navigator, Forms 6i, and SQL
Plus strip them out somehow for compilation.

dmz...@inbox.net (DMZ) wrote in message news:<4550a206.03012...@posting.google.com>...

Reply all
Reply to author
Forward
0 new messages