PL/SQL Package Created In 10g From Java Application Is Invalid due to CR character (Doc ID 1399110.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.5 [Release 10.2]
JDBC - Version 10.2.0.5 and later
Information in this document applies to any platform.

Symptoms


A Java JDBC application reads a text file containing PL/SQL code and submits it to a database version 10g ( 10.2.0.5 database tested).  The PL/SQL package does get created however it contains a compilation error.
The same package compiles fine when the same Java program  creates it on the 11g database. ( 11.1 and 11.2 databases tested).
This is not specific to the JDBC driver used by the Java program.

Example of the SQL source code text file pack1.sql:
CREATE OR REPLACE PACKAGE pack1
IS
PROCEDURE proc1;
END pack1;



Java Program excerpt:
public static String parseScriptWithLineBreaks(InputStream is) throws SQLException, ClassNotFoundException {
String s = new String();
String sql = "";
StringBuffer sb = new StringBuffer();
try {
InputStreamReader irs = new InputStreamReader(is);
BufferedReader br = new BufferedReader(irs);
while((s = br.readLine()) != null) {
sb.append(s);
sb.append(System.getProperty("line.separator"));
}
br.close();
irs.close();
sql = sb.toString();
} catch (IOException ioe) {
//ErrorUtil.handleIOException(ioe);
System.out.println(ioe);
}
return sql;

}

...
InputStream is = readSQLFile("pack1.sql");
String StreamedSql1 = parseScriptWithLineBreaks(is);
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql1);
...


Check the created package on 10g database using SQLPlus:
SQL> alter package camilionCommonPsPkg compile;

SQL> show errors
No errors.
SQL> alter package pack1 compile;

Warning: Package altered with compilation errors.

SQL> show errors
Errors for PACKAGE PACK1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/14 PLS-00103: Encountered the symbol "" when expecting one of the
following:
is authid as compress compiled wrapped


There are no compilation errors on the 11g databases.

The complete testcase is available in testcase.zip

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms