Not sure what wrong with this Anonymous PL/SQL Block. Can someone please help. I'm seeing an error:
ORA-06550: line 74, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
PROCEDURE CUR_AVAIL IS
BEGIN
DECLARE
i NUMBER :=0;
v_FDOM NUMBER := 0;
v_LDOM NUMBER := 0;
v_RESOURCE_ID NUMBER;
v_SW_GROUP_NAME VARCHAR2(255);
v_TIER_NAME VARCHAR2(255);
v_RESOURCE_NAME VARCHAR2(255);
v_STARTIME NUMBER;
v_ENDTIME NUMBER;
v_NEW_STARTIME NUMBER;
v_NEW_ENDTIME NUMBER;
v_AVAILVABILITY NUMBER;
v_RESOURCE_ID1 NUMBER;
v_SW_GROUP_NAME1 VARCHAR2(255);
v_TIER_NAME1 VARCHAR2(255);
v_RESOURCE_NAME1 VARCHAR2(255);
v_STARTIME1 NUMBER;
v_ENDTIME1 NUMBER;
v_NEW_STARTIME1 NUMBER;
v_NEW_ENDTIME1 NUMBER;
v_AVAILVABILITY1 NUMBER;
rec_cur_avail cur_avail%ROWTYPE;
CURSOR cur_avail
IS
SELECT R.ID,
R.NAME,
G.NAME,
P.NAME,
AV.STARTIME,
AV.ENDTIME,
CASE
WHEN AV.STARTIME > v_FDOM
THEN AV.STARTIME
WHEN AV.STARTIME < v_FDOM
THEN v_FDOM
END NEW_STARTIME,
CASE
WHEN AV.ENDTIME < v_LDOM
THEN AV.ENDTIME
WHEN AV.ENDTIME > v_LDOM
THEN v_LDOM
END NEW_ENDTIME,
AV.AVAILVAL
FROM hquser.EAM_RESOURCE R
JOIN hquser.EAM_RES_GRP_RES_MAP RGM
ON R.ID=RGM.RESOURCE_ID
JOIN hquser.EAM_RESOURCE_GROUP RG
ON RGM.RESOURCE_GROUP_ID=RG.ID
JOIN hquser.EAM_RESOURCE G
ON RG.RESOURCE_ID=G.ID
JOIN hquser.EAM_RES_GRP_RES_MAP PGM
ON G.ID=PGM.RESOURCE_ID
JOIN hquser.EAM_RESOURCE_GROUP PG
ON PGM.RESOURCE_GROUP_ID=PG.ID
JOIN hquser.EAM_RESOURCE P
ON PG.RESOURCE_ID=P.ID
JOIN hquser.EAM_MEASUREMENT M
ON M.RESOURCE_ID=R.ID
JOIN hquser.HQ_AVAIL_DATA_RLE AV
ON M.ID=AV.MEASUREMENT_ID
JOIN hquser.EAM_MEASUREMENT_TEMPL T
ON M.TEMPLATE_ID =T.ID
WHERE T.ALIAS ='Availability'
AND P.NAME ='KPI: Availability'
AND ( ( AV.STARTIME> v_FDOM
AND AV.ENDTIME < v_LDOM )
OR ( AV.STARTIME BETWEEN v_FDOM AND v_LDOM )
OR ( AV.ENDTIME BETWEEN v_FDOM AND v_LDOM )
OR ( AV.STARTIME < v_FDOM
AND AV.ENDTIME > v_LDOM ) );
CREATE GLOBAL TEMPORARY TABLE hquser.TEMP_TAB_AVAIL (v_RESOURCE_ID NUMBER, v_SW_GROUP_NAME VARCHAR2(255),
v_TIER_NAME VARCHAR2(255), v_RESOURCE_NAME VARCHAR2(255),
v_STARTIME NUMBER, v_ENDTIME NUMBER,
v_NEW_STARTIME NUMBER, v_NEW_ENDTIME NUMBER,
v_AVAILVABILITY NUMBER);
BEGIN
LOOP
SELECT (TO_DATE(ADD_MONTHS (TRUNC(TRUNC(sysdate,'MM')-1,'MM'), -i)) -
TO_DATE('01/01/1970 00:00:00', 'MM-DD-YYYY HH24:MI SS')) * 24 * 60 * 60 * 1000
INTO v_FDOM
FROM DUAL;
SELECT (TO_DATE(ADD_MONTHS(TRUNC(sysdate,'MM')-1,-i)) -
TO_DATE('01/01/1970 00:00:00','MM-DD-YYYY HH24:MI SS'))* 24 * 60 * 60 * 1000
INTO v_LDOM
FROM DUAL;
i:= i+1;
If ( i >= 13 ) Then
Exit;
End if;
OPEN cur_avail;
LOOP
FETCH cur_avail INTO rec_cur_avail;
BEGIN
INSERT
INTO hquser.TEMP_TAB_AVAIL VALUES
(
v_RESOURCE_ID,
v_SW_GROUP_NAME,
v_TIER_NAME,
v_RESOURCE_NAME,
v_STARTIME ,
v_ENDTIME,
v_NEW_STARTIME,
v_NEW_ENDTIME,
v_AVAILVABILITY
);
END;
EXIT WHEN cur_avail%NOTFOUND;
END LOOP;
CLOSE cur_avail;
END LOOP;
END;
END CUR_AVAIL;