Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 10th, 2005, 02:57 AM
Registered User
 
Join Date: Nov 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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;






Similar Threads
Thread Thread Starter Forum Replies Last Post
pls help me out..runtime error 20599 archana007 SQL Server 2000 2 April 5th, 2006 06:14 PM
Crystal reports & ASP - Runtime Error - Pls Help Prasad_Tina Classic ASP Basics 0 January 21st, 2005 10:53 AM
Automation Error 430 Pls Help sumanmukherjee VB Components 0 September 5th, 2003 01:21 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.