Re: Oracle 11g: Java Stored Procedures - ORA 1031

On Jan 9, 5:24 pm, "kuassi.men..._at_gmail.com" <kuassi.men..._at_gmail.com> wrote:
> On Jan 8, 11:19 am, Thomas Olszewicki <Thom..._at_cpas.com> wrote:
>
>
>
>
>
> > 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.
>
> Hi,
>
> You need needs to grant execute on the underyling Java class; the 10g
> behavior was a laisser-aller that we fixed in 11g.
>
> Kuassihttp://db360.blogspot.com- Hide quoted text -
>
> - Show quoted text -

Kuassi,
That fixed the problem...
Thank you very much.

The only other "gotcha" was to remember that Java Class names are Case sensitive...
So it must be:
GRANT EXECUTE ON "TestTimeClass" TO Schema2 Instead of
GRANT EXECUTE ON TestTimeClass TO Schema2

BTW
I enjoyed attending your session in San Francisco at OOW, Thank you very much

Thomas Olszewicki
CPAS Systems Inc. Received on Thu Jan 10 2008 - 12:38:41 CST