ORA 1031 - insufficient privileges & Java

[Quoted] i have some tricky problem and no resolution for it:

I created the following stored procedure for user Scott:

CREATE OR REPLACE PROCEDURE updateProcedure ( L_SQL VARCHAR2 ) authId CURRENT_USER
AS PRAGMA AUTONOMOUS_TRANSACTION;
v_cur number;
v_stat number;
BEGIN
  /* EXECUTE IMMEDIATE L_SQL_QUERY; */
  v_cur := dbms_sql.open_cursor;
  dbms_sql.parse (v_cur,L_SQL,DBMS_SQL.native);   v_stat := dbms_sql.execute(v_cur);
  dbms_sql.close_cursor(v_cur);
END;
>From SQL*Plus i can test it without problems:

begin
[Quoted] updateProcedure('CREATE OR REPLACE FUNCTION getValue RETURN VARCHAR2 IS calcul VARCHAR2(2000);Begin calcul := ''myValue''; RETURN calcul; END;');
end;

[Quoted] Then, i have a piece of Java code i loaded via javaload into the same user Scott:

OracleDataSource dataSource = FactoryHandler.getDataSource();

String strFct = "CREATE OR REPLACE FUNCTION getValue RETURN VARCHAR2 IS calcul VARCHAR2(2000); BEGIN calcul := ''myValue''; RETURN calcul; END;";
dataSource.update("BEGIN updateProcedure('" + strFct+ "');END;");

[Quoted] -> this fails with an SQLException and ORA 1031 insufficient privileges

What is missing there? This worked fine already on another Oracle database, but fails on this
new installation. I think this is an issue of granting privileges, but which one? I tried the "grant
execute on any procedure to Scott", without success. Even promoting Scott to dbadmin does
not help!

Who has an idea?

Thanks,
Naven. Received on Thu Oct 19 2006 - 00:21:46 CEST