Create 'TABLE AS' in PL/SQL block

Question

My query is simple but PL/SQL code block is expecting 'INTO' statement.

Here is my query:

DECLARE

yesterdays_date DATE := SYSDATE-1;
start_date DATE :='01/JAN/2013';

BEGIN     
     EXECUTE IMMEDIATE 'DROP TABLE P2P_DATA'; 
    EXCEPTION     
  WHEN OTHERS THEN         
   IF SQLCODE != -942 THEN     
 --if table not found DO NOTHING AND MOVE ON
 --dbms_output.put_line('HELLO');
            NULL;
    END IF;

---------------------------create new table here-------------------
CREATE TABLE P2P_DATA AS
SELECT 
    GM_NAME,
    NEW_SKILL,    
    WEEK_DATE,
    TOR_MWF
FROM TEST_TABLE 
WHERE WEEK_DATE BETWEEN start_date AND yesterdays_date;

END;

it gives the COMPILE time error:

FOUND CREATE: EXPECTING END SELECT or (BEGIN CASE CLOSE CONTINUE DECLARE ... ETC)

I am simply setting the dates in the declaration block and then creating a new table as a result of the select statement. any ideas how to accomplish this task?

Answer 1

This does the same thing but avoids the execute immediate and overhead of creating and dropping tables.

DECLARE

yesterdays_date DATE := SYSDATE-1;
start_date DATE :='01/JAN/2013';

BEGIN     
 --clear out old data------
 DELETE from P2P_DATA;

---------------------------insert new data here-----------------
INSERT INTO P2P_DATA
SELECT 
GM_NAME,
CASE WHEN SUBSTR(CST_NAME,0,5) = 'A' 
     THEN 'BVG1' ELSE SUBSTR(CST_NAME,0,5) END AS CST_NAME,
NEW_SKILL,    
WEEK_DATE,
TOR_MWF,
TOR_MA,    
TOR_DL
FROM TEST_TABLE 
WHERE WEEK_DATE BETWEEN start_date AND yesterdays_date;

END;

--do your commit outside the transaction just in case

Even better just create a view and avoid the whole table thing when all you want is a subset.

CREATE VIEW VW_P2P_DATA 
AS
SELECT 
GM_NAME,
CASE WHEN SUBSTR(CST_NAME,0,5) = 'A' 
     THEN 'BVG1' ELSE SUBSTR(CST_NAME,0,5) END AS CST_NAME,
NEW_SKILL,    
WEEK_DATE,
TOR_MWF,
TOR_MA,    
TOR_DL
FROM TEST_TABLE 
WHERE WEEK_DATE BETWEEN TO_DATE('01/JAN/2013','DD/MON/YYYY') AND SYSDATE-1;