PLS-103 error
Please help with the below error:
55/4 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
loop
Script
CREATE OR REPLACE FUNCTION GET_SERVICE_HOUR(FROM_DT IN DATE, TO_DT IN DATE)
RETURN TIME AS
HRS NUMBER(4):=0;
MINS NUMBER(2):=0;
SEC NUMBER(2):=0;
DAY_ELAPSE NUMBER(4):=0;
N_SEC NUMBER(4):=0;
D1 NUMBER:=0;
SERVICE_TIME DEC(2);
STARTDATE DATE;
ENDDATE DATE;
STARTDATE DATE;
TEMPDATE DATE;
T_TIME TIME;
I NUMBER:=0;
J NUMBER:=0;
CUR CURSOR ;
BEGIN
STARTDATE := FROM_DT;
ENDDATE := TO_DT;
TEMPDATE:=STARTDATE;
FOR I IN 1..2 LOOP
BEGIN
IF TO_CHAR(TEMPDATE,'HH24:MI:SS') <'10:00:00' THEN
TEMPDATE:=TO_DATE(TO_CHAR( TEMPDATE,'DD-MM-YY') + ' 10:00:00', 'DD/MM/YYYY HH24:MI:SS');
ELSE IF TO_CHAR(TEMPDATE,'HH24:MI:SS') >'19:00:00' THEN
TEMPDATE:=TO_DATE(TO_CHAR( TEMPDATE,'DD-MM-YY') + ' 19:00:00', 'DD/MM/YYYY HH24:MI:SS');
ELSE
BEGIN
OPEN CUR FOR SELECT EXTRACT(HOUR FROM D )*60+EXTRACT(MINUTE FROM D ) SECS
FROM( SELECT NUMTODSINTERVAL(TO_DATE(TO_CHAR( TEMPDATE )||' 19:00:00','DD/MM/YY HH24:MI:SS')- TEMPDATE,'DAY')D
FROM DUAL
) ;
IF CUR%NOTFOUND THEN
J:=0;
ELSE
FETCH CUR INTO J;
END IF;
CLOSE CUR;
D1:=D1+J;
END;
END IF;
STARTDATE:= TEMPDATE;
TEMPDATE:= ENDDATE;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[DEBUG] D1 = ' ||TO_CHAR(FROM_DT)||' TO '||TO_CHAR(STARTDATE));
DBMS_OUTPUT.PUT_LINE('[DEBUG] D2 = ' ||TO_CHAR(TO_DT)||' TO '||TO_CHAR(ENDDATE));
DAY_ELAPSE:= TRUNC(ENDDATE - STARTDATE ) ;
T_TIME:= TO_DATE('00:00:00','HH24:MI');
CUR:= NULL;
RETURN T_TIME;
END;
/
SHOW ERRORS;
|