CODEWITHSUNDEEP
sqloracle-databaseprocedureprivileges
user8143344
user8143344

Reputation:

Grant privilege to create table INSIDE oracle sql procedure

I am trying to create a table inside a procedure on oracle. But I keep getting ORA-01031: insufficient privileges. I thought that maybe my user needs a privilege for doing that?

Here is the procedure code:

CREATE OR REPLACE PROCEDURE AjouterCol(TAB VARCHAR2, NOM VARCHAR2) IS 
REQ VARCHAR2(200);
RES VARCHAR2(200);
RES1 VARCHAR2(200);
NAME VARCHAR2(200);
BEGIN
  NAME:=TAB || '_' || NOM;

  RES:='CREATE TABLE '||NAME||' AS SELECT * FROM '|| TAB || ' WHERE Col9 = ''PARIS'' AND SOUNDEX(Col9) = SOUNDEX(''PARIS'')' ;
  EXECUTE IMMEDIATE RES;

  RES1:='ALTER TABLE '|| NAME ||' ADD MAGASIN VARCHAR2(5)';
  EXECUTE IMMEDIATE RES1;

  REQ:='UPDATE '|| NAME ||' SET MAGASIN = '''|| NOM ||''' WHERE MAGASIN IS NULL' ;
  EXECUTE IMMEDIATE REQ;

  END;
  /

Any help please ? Thank you.

Upvotes: 0

Views: 3665

Answers (2)

user8143344
user8143344

Reputation:

So thank you everyone for your answers. I resolved the problem by simply adding AUTHID CURRENT_USER to the proc declaration. Thank you again.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143093

If user (which executes that script) acquired CREATE TABLE privilege via role, it can create tables. However, those privileges won't work in named PL/SQL procedures - you'll have to grant that privilege directly to the user (i.e. not via role).

Upvotes: 1

Related Questions