X

Jeff Taylor's Weblog

Java Stored Procedures and SQLJUTL2.INVOKE

BEFORE:



I was having trouble accessing Java Stored Procedures from an Oracle 12c client:

java.sql.SQLException: ORA-06550: line
1, column 13:

PLS-00201: identifier 'SQLJUTL2.INVOKE' must be
declared

ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

    at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)

    at
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)

    at
oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)

    at
oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)

    at
oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)

    at
oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)

    at
oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220)

    at
oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:48)

    at
oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)

    at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)

    at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)

    at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)

    at
oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:5661)

    at
oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)

    at
oracle.jpub.reflect.Client.invoke(Client.java:97)

    at
JavaStoredProcedureStubs.RecommendationEngine.newInstance(RecommendationEngine.java:31)

    at client.myClient.doit(myClient.java:80)
    at client.myClient.main(myClient.java:126)



The problem was also effecting Oracle's jpub client:

$ jpub -user=scott/tiger
-url=jdbc:oracle:thin:@
myserver:1521/orcl
-java=java_stored_procedures.StoredProcedure1


Note: Oracle Databases 10g Release 2 or later is recommended
for publishing server-side Java.

ERROR: Unable to obtain information on server-side Java
classes: java.sql.SQLException: ORA-06550: line 1, column 13:

PLS-00201: identifier 'SQLJUTL2.REFLECT' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 Please ensure you have installed
[ORACLE_HOME]/sqlj/lib/sqljutl.sql and sqljutl.jar.

J2T-106, ERROR: Sorry, unable to continue due to
oracle.jpub.JPubException: ORA-06550: line 1, column 13:
PLS-00201: identifier 'SQLJUTL2.REFLECT'
must be declared


ORA-06550: line 1, column 7:

PL/SQL: Statement ignored


WORKAROUND:


$ sqlplus
scott/tiger@
myserver:1521/orcl
@$ORACLE_HOME/jpub/sql/sqljutl2.sql


SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 16 07:05:43
2015


Copyright (c) 1982, 2014, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 -
64bit Production

With the Partitioning, OLAP, Data Mining and Real Application
Testing options



Package created.



Package body created.


SQL>


AFTER:


$ jpub
-user=scott/tiger
-url=jdbc:oracle:thin:@myserver:1521/orcl
-java=java_stored_procedures.StoredProcedure1


ERROR: error reflecting Java
class in server:
reflect("java_stored_procedures.StoredProcedure1",91)

./java_stored_procedures/StoredProcedure1.java


$ ls -l
java_stored_procedures/


total 13

-rw-r--r--   1 oracle   oinstall    2233 Jun 16 07:06
StoredProcedure1.class

-rw-r--r--   1 oracle   oinstall    2867 Jun 16 07:06
StoredProcedure1.java



COMMENTS:


1) The workaround is NOT required for 11g Clients

2) This problem in the 12c Client effects BOTH 11g and 12c Servers

3) The workaround must be run as the current user (e.g.
scott/tiger). Running sqljutl2.sql as 'SYS as SYSDBA' is NOT
sufficient.

4) The workaround is necessary even when "describe
sys.SQLJUTL2
" indicates that FUNCTION
REFLECT RETURNS LONG exists in the server.



Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.