CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Arav
Arav

Reputation: 5247

Regarding Execute Immediate in stored procedure

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

Upvotes: 2

Views: 991

Answers (2)

Guru
Guru

Reputation: 2371

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.

Upvotes: 0

Pop
Pop

Reputation: 4022

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.

Upvotes: 3

Related Questions