Tutorial :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.


Solution:1

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


Solution:2

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


Solution:3

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


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »