0

When i execute stored procedure which contain EXECUTE IMMEDIATE along with create table statement inside the stored procedure then getting error while execute this Stored Proc. Here is the Stored proc-

create or replace sp_exec
 as
 .......
 v_mth date;
 .......
begin
 select to_char(add_months(current_DATE, -1), 'MON_YY') INTO v_mth FROM dual;
  execute immediate 'create table tbl_mon_' ||v_mth|| ' as select ............... from ..... ';
end;

When I try to execute this Stored proc then getting error "Insufficient Privilege" at 2nd last line using this code to execute Stored Proc.

 begin
  sp_exec;
 end;

then getting error which mentioned below:-

 ORA-01031: Insufficient privilege
 ORA-06512: at sp_exec line 11  --here line 11 mention for Execute immediate
 ORA-06512: at line 2         -- here mention for sp_exec in the PLSQL block

I am working in the user schema itself so I've all types of privileges even CREATE TABLE statement also. When i run outside the stored procedure (PL/SQL block) then dynamic table have been created.

APC
  • 144,005
  • 19
  • 170
  • 281
Md Wasi
  • 479
  • 3
  • 16

1 Answers1

2

Apparently, you acquired the create table privilege via role. It won't work in named PL/SQL procedures, which means that you'll have to grant it directly to user.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Do you have a privilege to select from a table which is source for your dynamically created table? Code you posted suggests that you are selecting `from v_mth`, where ˙`v_mth` is a **date**; you can't select from a table whose name is 01-JUN-19, can you? – Littlefoot Jul 22 '19 at 12:16
  • 1
    OK, but your code says: `as select ............... from '|| v_mth ||' ..... '`. This `v_mth` is equal to 01-JUN-19 (or whichever format TO_DATE in your database returns by default). That's an invalid table name. Unless, of course, you oversimplified code you posted. Try to create a simple example we'll be able to reproduce. – Littlefoot Jul 22 '19 at 12:51