Regarding Execute Immediate in stored procedure

Question

I am having the below statement from stored procedure. It's giving Insufficient Privileges. But If i run the create statement alone from the sql prompt it's creating table.

    execute immediate 'create table TEST_ABC(
                    NO_AC NUMBER(8) 
                    , ILL_PER VARCHAR2(15)
                    , INIT_C DATE
                    )';

What needs to be done to have priviliges to create table via execute immediate from stored procedure. Not sure how it's working from sql command prompt

Answer 1

Procedures don't inherit privileges granted via a role. More info here. Please check if that's what happening to you.

One way to solve this problem is to grant "CREATE TABLE" privilege directly to the account that owns the procedure.

Answer 2

Is the procedure created by the same user? If it is created by some one else and you have EXECUTE privilege alone, then the error is right (assuming the create procedure doesn't have AUTHID CURRENT USER clause).

Can you create any other table? If you can, then there is some issue. We would need more details to analyse.