ORA-01031: privilegios insuficientes

[hensa22]

Hi everyone

I am facing a problem about store procedure on Oracle-.

I have a store procedure

create or replace PROCEDURE onevalue(
qq varchar2,
)
AS
Qry varchar(500);
begin
execute immediate 'CREATE GLOBAL temporary TABLE t_here( v_xx varchar2(250) not NULL ) ON COMMIT DELETE ROWS';


end;

when I execute it, oracle shows error message :
ORA-01031: privilegios insuficientes ORA-06512:

but when I execute script "create global temporary table" only,
it's working correctly.

thanks in advance

what's wrong??

[davide++]

Hi all.

Hard to say...
Surely some privileges are missing.
Do you get the error when yo're trying to create the stored procedure, or when you're trying to execute it?
If you cannot create the procedure your database user is missing CREATE PROCEDURE grant; otherwise it's missing EXECUTE ANY PROCEDURE grant.

I hope this will help you.

[GPP]

GRANT EXECUTE missing ?

for procedures and function you need execute permissions!

[davide++]

Well, you don't need execute permission if you execute the procedure within the user that holds it (the user that created the procedure). If you want execute the procedure from another user, this must have EXECUTE PROCEDURE (or EXECUTE ANY PROCEDURE) grant.