CODEWITHSUNDEEP
oracle-databaseplsql
don_julian
don_julian

Reputation: 55

Why I getting error while translate query to function

I am trying to create function of existing SELECT query.

 SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
 WHERE uta.UserID = 353 
 AND uta.Approved = 1
 AND DATE '2020-06-06' BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') 
 AND TO_DATE(uta.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY

When I execute this query, I get correct result in output StartDate and EndDate.

But problem is when I write function and try to compile I get error

Error(724,8): PL/SQL: ORA-00936: missing expression


FUNCTION GET_AVAILABE_USER_PER_DATES(p_userId IN INT,p_dateFormat IN VARCHAR2)
    RETURN SYS_REFCURSOR IS 
    rc  SYS_REFCURSOR;
BEGIN
OPEN rc FOR
   SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
   WHERE uta.UserID = p_userId 
   AND uta.Approved = 1
   AND DATE p_dateFormat BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') 
   AND TO_DATE(uta.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY;
RETURN rc;
END GET_AVAILABE_USER_PER_DATES;

Compiler gives me error in following line

AND DATE p_dateFormat BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD')

I try to understand what I made wrong, where is mistake but I couldn't ? What is wrong here ? What i make wrong can someone tell me ?

UPDATE

FUNCTION ABSENCE_EXIST_FOR_DATE(p_userId IN INT,p_dateFormat IN DATE)
    RETURN SYS_REFCURSOR IS 
    rc  SYS_REFCURSOR;
BEGIN
OPEN rc FOR
    SELECT uta.StartDate, uta.EndDate FROM user_timesheets_absence uta
    WHERE uta.UserID = p_userId 
    AND uta.Approved = 1
    AND  p_dateFormat BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') 
    AND TO_DATE(uta.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY;
RETURN rc;
END ABSENCE_EXIST_FOR_DATE;

Error gone, but unfortunetlly when I execute function I get error message

ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Upvotes: 0

Views: 88

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

You must replace

DATE p_dateFormat

with the appropriate conversion of the VARCHAR2 parameter p_dateFormat to the DATE type

to_char(p_dateFormat,'yyyy-mm-dd')

Adapt the the format if appropriate or use DATE parameter in the function.

Than you would simple constraint

FUNCTION GET_AVAILABE_USER_PER_DATES(p_userId IN INT,p_dateFormat IN DATE)
...

AND  p_dateFormat BETWEEN TO_DATE(uta.StartDate,'YYYY-MM-DD') 
AND TO_DATE(uta.EndDate,'YYYY-MM-DD') + INTERVAL '1' DAY

Check if the name p_dateFormat is the best possible name for a parameter with a DATE value (or a string with a DATE value) ...

To avoid ORA-01861: literal does not match format string use the ON CONVERSION ERROR clause as follows (Oracle 12 and up is required)

FUNCTION GET_AVAILABE_USER_PER_DATES(p_userId IN INT,p_dateFormat IN DATE)
...

AND  p_dateFormat BETWEEN TO_DATE(uta.StartDate default null on conversion error,'YYYY-MM-DD') 
AND TO_DATE(uta.EndDate default null on conversion error,'YYYY-MM-DD') + INTERVAL '1' DAY

If the column EndDate or StartDate contain invalid data, such as today or 2020-99-01 the conversion will not fail with error, but returns null, is the record will not be selected (which is probably what you want).

Upvotes: 1

Related Questions