truncate and insert within procedure don't work together

Question

I need to do truncate table and then insert data in that table using procedure.

However, one does like dinamic sql but the other one doesn't:

create or replace 
procedure   RECREATE_AGGREGATE
AUTHID DEFINER
AS
BEGIN
    TRUNCATE TABLE AGGREGATE;    
    INSERT INTO AGGREGATE SELECT * FROM OLD_AGGREGATE;
END;

Error(6,14): PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "TABLE" to continue.

If I add execute immediate around TRUNCATE statement, it works fine, but insert is erroring out. If I remove it, TRUNCATE TABLE complains...

create or replace 
    procedure   RECREATE_AGGREGATE
    AUTHID DEFINER
    AS
    BEGIN
        execute immediate 'TRUNCATE TABLE AGGREGATE';    
        INSERT INTO AGGREGATE SELECT * FROM OLD_AGGREGATE;
    END;

Error(7,5): PL/SQL: SQL Statement ignored Error(7,84): PL/SQL: ORA-00942: table or view does not exist

Can some shed some light here?

Answer 1

create or replace 
procedure   RECREATE_AGGREGATE
AUTHID DEFINER
AS
BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE AGGREGATE';    
    INSERT INTO AGGREGATE SELECT * FROM OLD_AGGREGATE;
END;

will work assuming that you have appropriate privileges.

Based on your edit (and echoing @OracleUser's comment), you're likely getting an error running your INSERT statement because old_aggregate is owned by another user and you only have SELECT privileges on that table via a role. If you want to do something in a definer's rights stored procedure, you'll need to have those privileges granted directly to you (or be using 12c which lets you grant privileges to blocks of code rather than to users).

Assuming you want to use a definer's rights stored procedure, you'd need the owner of old_aggregate (or the DBA) to

GRANT SELECT 
   ON old_user.old_aggregate
   TO new_user;

You can verify that you only have the privilege via a role by disabling roles for the session. I'll wager that if you do

SQL> set role none;
SQL> SELECT * FROM old_aggregate

that you'll get an ORA-00942 error as well. This is a good way of simulating what privileges will be available to the user inside a definer's rights stored procedure.