Oracle merge partition from procedure giving error

Question

  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.

Answer 1

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 ;
/

Answer 2

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 ;
  /

Answer 3

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

Answer 4

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.