2
Create or replace procedure myprocedure
is 
   stmt varchar2(1000);
   stmt2 varchar2(1000);
begin
    stmt := 'create global temporary table temp(id number(10))';
    execute immediate stmt;
    stmt2 := 'insert into temp(id) values (10)';
    execute immediate stmt2;
end;

when i execute the procedure 

exec myprocedure;

i got the error:

ORA-01031: insufficient privileges
ORA-06512: at "I3.MYPROCEDURE", line 7
ORA-06512: at line 2
Improve this question
  • 2
    Did the user you are using received the privileges to create tables through a role? Those are not active inside PL/SQL. The user needs to be granted the privilege directly. – a_horse_with_no_name Jul 12 '18 at 6:07
  • What is line 7 at "I3.MYPROCEDURE"? Which statement does cause the error? Is this 'execute immediate stmt;'? What is line 2? – miracle173 Jul 12 '18 at 6:19
  • line 7 is execute immediate stmt @miracle173 – Mojib Jul 12 '18 at 7:16
  • so the 'create global temporary table temp(id number(10))' raises the error. Which privilgese are necessary to create a temporary table? – miracle173 Jul 12 '18 at 11:09
  • Read first comment.Are you able to create table from sql*plus? – user153556 Jul 12 '18 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.