1

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 procedure.

Here is the stored procedure -

create or replace sp_exec
 as
 .......
 v_mth date;
 .......
begin
 select TO_DATE('01-' || to_char(add_months(sysdate, -1), 'MON-YY'), 'DD-MON-YY')
  INTO v_mth FROM dual;
  execute immediate 'create table tbl_mon_ '||v_mth||' as select ............... from '|| v_mth ||' ..... ';
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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Md Wasi
  • 479
  • 3
  • 16
  • Inside a OL/SQL block only directly granted privileges apply. Privileges which are granted by role (e. g. DBA) do not apply. – Wernfried Domscheit Jul 17 '19 at 07:04
  • @KaushikNayak I have already all types of `GRANT` privilege for this user schema. I have checked in `session_privs` table. – Md Wasi Jul 17 '19 at 07:23
  • 1
    *"This stored proc is in user schema itself means all types of GRANT privileges have"* - That's not right. Appropriate privileges should have been granted **directly** to the user to read or write a particular **database object**, like a table. Go through both the answers I added, you'll know. – Kaushik Nayak Jul 17 '19 at 07:28
  • 1
    What is your exact error message (I know only `ORA-01031: insufficient privileges` which may appear when you install an Oracle on your machine) and what is the SQL statement? As far as I remember you should get an `ORA-39034: Table ... string does not exist` if you try to select a table where privileges are missing. Maybe list also the privileges you have been granted. – Wernfried Domscheit Jul 17 '19 at 11:38
  • I would also expect `select any table` privilege. – Wernfried Domscheit Jul 18 '19 at 06:04
  • Again, please provide the exact and full error message and the generated SQL string. Otherwise we cannot help you. – Wernfried Domscheit Jul 18 '19 at 07:51
  • Instead of `SESSION_PRIVS` better select `USER_SYS_PRIVS` and `USER_TAB_PRIVS_RECD` – Wernfried Domscheit Jul 18 '19 at 07:57
  • @WernfriedDomscheit when i check using `USER_SYS_PRIVS` then able to `select any table` as privilege but `ADMIN_OPTION` as **NO**. – Md Wasi Jul 18 '19 at 11:15
  • You don't provide needed information, I am out for this question. – Wernfried Domscheit Jul 18 '19 at 11:44
  • @WernfriedDomscheit I've updated all the code. Due to variable getting error when i execute stored proc. In `EXECUTE IMMEDIATE` i am using variable in the stored proc. Due to this variable getting error. Please see – Md Wasi Jul 18 '19 at 14:24
  • Run `DBMS_OUTPUT.PUT_LINE(v_sql);` before the `EXECUTE IMMEDIATE`. Can you run the dynamic statement manually? – Wernfried Domscheit Jul 19 '19 at 07:02
  • @WernfriedDomscheit Thanks for the suggestion. But at the time of creation of dynamic tables I did it but getting the error. – Md Wasi Jul 19 '19 at 07:06
  • Then ask your DBA for the missing privileges. Once again, the privileges has to be granted **directly** to the user not via a ROLE. – Wernfried Domscheit Jul 19 '19 at 07:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/196687/discussion-between-wernfried-domscheit-and-md-wasi). – Wernfried Domscheit Jul 19 '19 at 07:10

0 Answers0