Oracle 11g: Java Stored Procedures - ORA 1031

Working environment: Oracle 10.2.0.2 , 10.2.0.3 on several OS platforms
Nor working environment: Oracle 11.1.0.6 on Windows XP Pro.

The problem:
JSP(Java Stored Procedure) and Wrapper PL/SQL package are own by user Schema1.
User Schema1 Grants EXECUTE on Wrapper PL/SQL package to user Schema2. User Schema2 can execute pure PL/SQL function from Wrapper PL/SQL package
on both Oracle 10g and Oracle 11g.

User Schema2 cannot execute function using JSP on Oracle 11g. (Error ORA - 01031 -...)

Please find below a working sample of the code to reproduce this problem.
It works on Oracle 10g, but it fails on Oracle 11g on my environment. I suspect, I'm missing some privileges to be granted to User Schema2, but I cannot find any relevant documentation. I've experimented with several roles and/or privileges for user Schema2.
But no success so far.

The Question:
Which privilege should I grant to user Schema2 to run successful JSP own by user Schema1.
Which chapter of Oracle documentation describes this new requirement.

The Code:
--

• Connected as DBA user with privs to create other users;
  --
  
• Create 2 users (schemas) able to create and run packages.
  --
  CREATE USER Schema1 IDENTIFIED BY sqll; GRANT CREATE SESSION TO Schema1; GRANT CREATE ANY PROCEDURE TO Schema1; GRANT ALTER ANY PROCEDURE TO Schema1; GRANT EXECUTE ANY PROCEDURE TO Schema1 WITH ADMIN OPTION;

CREATE USER Schema2 IDENTIFIED BY sqll;
GRANT CREATE SESSION TO Schema2;
GRANT CREATE ANY PROCEDURE TO Schema2;

• Connect as first new user: create Java source and package.
  --
  CONNECT Schema1/sqll@<...>; create or replace and compile java source named TestTimeClass as import java.util.Date; public class TestTimeClass { public static long getTime() { Date dCurrent = new Date(); long nCurrent = dCurrent.getTime(); return nCurrent; } } /

create or replace package TESTJavaPackageCall AUTHID CURRENT_USER is   FUNCTION TestTime RETURN NUMBER;
  FUNCTION TestNum RETURN NUMBER;
end;
/

create or replace package body TESTJavaPackageCall is   FUNCTION TestTime RETURN NUMBER AS
    LANGUAGE JAVA NAME 'TestTimeClass.getTime() return long';   FUNCTION TestNum RETURN NUMBER AS
   BEGIN
     RETURN TO_NUMBER(TO_CHAR(SYSDATE,'SSSS'));    END;
end;
/

• Test run new package.functions SELECT TESTJavaPackageCall.TestTime FROM DUAL; SELECT TESTJavaPackageCall.TestNum FROM DUAL;
• GRANT Execute on new package to second user: GRANT EXECUTE ON TestJavaPackageCall TO Schema2;
• Connect as second user: CONNECT Schema2/sqll@<...>;

SELECT Schema1.TESTJavaPackageCall.TestNum FROM DUAL; SELECT Schema1.TESTjavaPackageCall.TestTime from dual;
-- At this point Oracle 10g will return correct results.

• On Oracle 11g only function TestNum will return a value.
• Call to function TestTime will result in ORA - 01031 error.

Thank you
Thomas Olszewicki
CPAS Systems Inc. Received on Tue Jan 08 2008 - 13:19:29 CST