CODEWITHSUNDEEP
oracle-database
Shamik
Shamik

Reputation: 7108

Oracle merge partition from procedure giving error

  CREATE OR REPLACE PROCEDURE test
  AS
  sql_stmt  VARCHAR2(200);
  BEGIN
     sql_stmt := 'ALTER TABLE daily_table PARTITIONS p1 , p2 into PARTITION p2';
     EXECUTE IMMEDIATE sql_stmt;

  END ;
  /

The above procedure is giving me the following error -

ORA-01031: insufficient privileges
ORA-06512: at "test", line 8
ORA-06512: at line 6

But if I run the ALTER Command directly on the sql prompt, I am not receiving any error.. I am wondering what permission I need to provide the user to perform the merge from the procedure.

Upvotes: 0

Views: 1491

Answers (4)

George Jansen
George Jansen

Reputation:

I do use DDL in stored procedures: most commonly to truncate summary tables that the procedure will then re-populate; now and then for DDLish tasks such as renaming columns of an imported table to conform to Oracle's standard rules for an identifier, or for creating primary keys and sequences for named tables. Generally I use

dbms_utility.exec_ddl_statement(blah);

rather than

EXECUTE IMMEDIATE blah;

a prejudice I won't attempt to justify. I will say, that having packaged procedure supplied and documented by Oracle suggests that it is not to be prohibited across the board.

Upvotes: 0

Mike
Mike

Reputation:

I'm sure that if someone creates a stored proc SPECIFICALLY for DDL, then they realize that it is commited automatically.

Upvotes: 0

Shamik
Shamik

Reputation: 7108

I fixed the issue by using AUTHID CURRENT_USER 

CREATE OR REPLACE PROCEDURE test AUTHID CURRENT_USER
  AS
  sql_stmt  VARCHAR2(200);
  BEGIN
     sql_stmt := 'ALTER TABLE daily_table PARTITIONS p1 , p2 into PARTITION p2';
     EXECUTE IMMEDIATE sql_stmt;

  END ;
  /

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425471

Note: you should never use DDL in stored procedures. Below is the sample of very, very badly designed code which should be avoided.

CREATE OR REPLACE PROCEDURE test
AS
sql_stmt  VARCHAR2(200);
BEGIN
     sql_stmt := 'GRANT ALTER ON daily_table TO your_user';
     EXECUTE IMMEDIATE sql_stmt;
     sql_stmt := 'ALTER TABLE daily_table PARTITIONS p1 , p2 into PARTITION p2';
     EXECUTE IMMEDIATE sql_stmt;
END ;
/

Upvotes: 0

Related Questions