My Oracle Support Banner

CANNOT INVOKE STORED PROCEDURE WITH SYS PRIVILEGES AT ORACLE.JPUB.REFLECT.CLIENT.INVOKE (Doc ID 1073389.1)

Last updated on MARCH 08, 2017

Applies to:

JPublisher - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

A JDBC client testcase application invokes a Java class that has been uploaded into a database version 10.2.0.4 using the jpub.reflect.Client.invoke() method.

The purpose of the jpub.reflect.Client.invoke() method is to allow a Java client program to directly invoke Java code that has been uploaded to the database without need for publishing an interface for it such as creating a Java Stored Procedure.


This call fails with the error:

java.sql.SQLException: Cannot invoke a stored procedure with SYS privileges


The JDBC client is not trying to log in as SYS but rather using another database user

example:

Java class to be uploaded to the database:


import java.sql.*;
import java.util.* ;

public class HelloTest
{
public static String dummyWorld()
{
return "Hello World : ";
}
}

loadjava -u scott/tiger -r -v -synonym -g PUBLIC HelloTest.java

Client JDBC code to invoke the unpublished Java code:
import java.sql.Connection;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jpub.reflect.Client;

public class TestJPubClient
{
public static void main(String[] args) throws Exception
{
System.out.println("Hi there ");
Connection conn = null;
String oradebugSession = "alter session set events '10046 trace name context forever, level 12'";

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//localhost:1521/orcl");
ods.setUser("scott");
ods.setPassword("tiger");

String dummyResult = null;

// Retrieve connection.
conn = ods.getConnection();
System.out.println("Connected ");

// for debug only:
// conn.createStatement().execute(oradebugSession);
// System.out.println("Trace started ");
try
{
System.out.println("Invoke the java class ");
dummyResult = (String) Client.invoke(conn, "HelloTest", "dummyWorld", null, null);
System.out.println("Output is = " + dummyResult);
} catch (Throwable e)
{
System.out.println("\nError occured: ");
e.printStackTrace();
}
// Close the connection
conn.close();
System.out.println("Done ");
}
}

Good test run:
java TestJPubClient
Hi there
Connected
Invoke the java class
Output is = Hello World :
Done

Bad test run:
java.sql.SQLException: Cannot invoke a stored procedure with SYS privileges
at oracle.jpub.reflect.Client.invoke(Client.java:109)
at oracle.jpub.reflect.Client.invoke(Client.java:60)
at oracle.jpub.reflect.Client.invoke(Client.java:43)
at oracle.jpub.reflect.Client.invoke(Client.java:35)


Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.