Truncate table in procedure
851548Mar 30 2011 — edited Mar 30 2011Hello,
I would like to create the following procedure on Oracle 10g:
----------
CREATE OR REPLACE PROCEDURE TEST2_RUN AS
BEGIN
truncate table TEST2 drop storage;
insert into TEST2 (
FORMATTED_BATCH_ID,
A_AKT_MYRAKT_CELL_1AIRE_BAF3,
A_ALK_NPM_ALK_CELL_1AIRE_BAF3,
A_ALK_NPM_ALK_CELL_2AIRE_BAF3,
A_BCRABL_CELL_1AIRE_BAF3,
A_BCRABL_CELL_2AIRE_BAF3,
A_BCRABL_CELL_2AIRE_KU_812,
A_BCRABL_T315I_CELL_1AIRE_BAF3,
A_BCRABL_T315I_CELL_2AIRE_BAF3,
A_BLK_TEL_BLK_CELL_1AIRE_BAF3,
A_BRAF_WT_CELL_2AIRE_MEL_WO
)
select formatted_batch_id
,a_AKT_MYRAKT_CELL_1AIRE_BAF3,a_ALK_NPM_ALK_CELL_1AIRE_BAF3,a_ALK_NPM_ALK_CELL_2AIRE_BAF3
,a_BCRABL_CELL_1AIRE_BAF3,a_BCRABL_CELL_2AIRE_BAF3,a_BCRABL_CELL_2AIRE_KU_812
,a_BCRABL_T315I_CELL_1AIRE_BAF3,a_BCRABL_T315I_CELL_2AIRE_BAF3,a_BLK_TEL_BLK_CELL_1AIRE_BAF3
,a_BRAF_WT_CELL_2AIRE_MEL_WO
from cell_assays_union_all_piv_vw
;
END TEST2_RUN;
-----------------------
I know I need to add permissions to be able to use the "truncate" function in a procedure but I cannot manage to make it work adding "WITH EXECUTE AS SELF"
CREATE OR REPLACE PROCEDURE TEST2_RUN
WITH EXECUTE AS SELF
AS
BEGIN
...
I get the error "PLS-00103: encountered the symbol EXECUTE when expecting one of the following: interface"
Could someone please help me?
Thank you very much in advance!
Miriam