CODEWITHSUNDEEP
oracle-databaseplsqloracle-apex
Ray Jasson
Ray Jasson

Reputation: 461

Wrong number or types of arguments in call to '<=' when comparing Timestamp in oracle apex

DECLARE FUNCTION CHECKTIME (APP_TIME IN TIMESTAMP) 
RETURN BOOLEAN IS
BEGIN
    DECLARE
    START_TIME TIMESTAMP;
    END_TIME TIMESTAMP;
    CURSOR ST IS
        SELECT APP_START_TIME FROM APPOINTMENTS WHERE DOC_ID = :P24_DOC_ID;
    CURSOR ET IS
        SELECT APP_END_TIME FROM APPOINTMENTS WHERE DOC_ID = :P24_DOC_ID;
    BEGIN
        OPEN ST;
        OPEN ET;
        LOOP
            FETCH ST INTO START_TIME;
            FETCH ET INTO END_TIME;
            EXIT WHEN ST%NOTFOUND;
            IF APP_TIME <= ST AND APP_TIME >= ET
            THEN RETURN FALSE;
            END IF;
        END LOOP;
        CLOSE ST;
        CLOSE ET;
        RETURN TRUE;
    END;
END;

BEGIN
    RETURN CHECKTIME(:P42_APP_START_TIME);
END;

I was trying to put the above code in PL/SQL Function Body Returning Boolean in validation section of an item field. This is the error I got.

ORA-06550: line 18, column 25: PLS-00306: wrong number or types of arguments in call to '<='

What is the real cause of the error?

Upvotes: 0

Views: 217

Answers (2)

Ray Jasson
Ray Jasson

Reputation: 461

DECLARE FUNCTION CHECKTIME (APP_TIME IN TIMESTAMP, DOCID IN VARCHAR2) 
RETURN BOOLEAN IS
BEGIN
    DECLARE
    START_TIME TIMESTAMP;
    END_TIME TIMESTAMP;
    CURSOR T IS
        SELECT APP_START_TIME, APP_END_TIME FROM APPOINTMENTS WHERE DOC_ID = DOCID;
    BEGIN
        OPEN T;
        LOOP
            FETCH T INTO START_TIME, END_TIME;
            EXIT WHEN T%NOTFOUND;
            IF APP_TIME <= END_TIME AND APP_TIME >= START_TIME
            THEN RETURN FALSE;
            END IF;
        END LOOP;
        CLOSE T;
        RETURN TRUE;
    END;
END;

BEGIN
    RETURN CHECKTIME(:P42_APP_START_TIME, :P42_DOC_ID);
END;

It seems like the error occurs because I use 2 different cursors which might return different number of rows and hence I can't use the comparison operator. After I change it to a single cursor, the error disappeared.

Upvotes: 0

APC
APC

Reputation: 146219

ST and ET are cursors, which means they are data structures. To make a comparison you need to refer to the individual elements of the projections, that is

IF APP_TIME <= ST.APP_START_TIME AND APP_TIME >= ET.APP_END_TIME

By the way. It seems odd to compare separate cursors with no apparent ordering. Either you're comparing values from the same record in which case you need only the one cursor, or there are multiple rows for DOC_ID in which case some form of ordering or joining would seem to be necessary.

Upvotes: 2

Related Questions