Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Truncate table in procedure

851548Mar 30 2011 — edited Mar 30 2011
Hello,
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
This post has been answered by BluShadow on Mar 30 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 27 2011
Added on Mar 30 2011
4 comments
2,728 views