What to do with Oracle data format error?

Question

I have a procedure with the following header:

CREATE OR REPLACE PROCEDURE ABC.PRC_MYPROC
    (
       P_Cursor      OUT SYS_REFCURSOR,
       P_Year        DATE,
       P_Unit        CLOB,
       P_Location    CLOB, 
       P_Vp          CLOB,
       P_Director    CLOB, 
       P_Manager     CLOB
   )

I would like to test this procedure to make sure that it is returning the correct results.

I am writing the following code in TOAD to test the procedure:

VARIABLE io_cursor refcursor;
Execute ABC.PRC_MYPROC(:io_cursor, TO_DATE('2015'), 'TTV_ARC_UMF', 'Olympia', 'Mark Brant', 'Jane Lowe', 'Stanley Drake');
print io_cursor;

When I execute the code a window in TOAD pops up asking me to set the values for io_cursor. I set the type to CURSOR, and direction and value are grayed out.

When I click Ok, I get the following error:

ORA-01861: literal does not match format string
ORA-06512: at line 1

Does anyone know what I can do?

Answer 1

This is a problem with how you're calling the procedure, not with the procedure itself.

You are calling TO_DATE() with a single argument, so the literal you have provided will be converted to a date using your session's NLS_DATE_FORMAT. Which, presumably, is not just 'YYYY'.

SELECT TO_DATE('2015') FROM DUAL;

Error report -
SQL Error: ORA-01861: literal does not match format string

You can see the same thing by explicitly supplying a longer format model, e.g.:

SELECT TO_DATE('2015', 'DD/MM/YYYY') FROM DUAL;

Error report -
SQL Error: ORA-01861: literal does not match format string

You shouldn't rely on the NLS setting anyway, though for an ad hoc test it's kind of OK. But since your settings don't match the value you're passing, you need to specify it:

SELECT TO_DATE('2015', 'YYYY') FROM DUAL;

TO_DATE('2015','YYYY')
----------------------
2015-06-01 00:00:00   

As you can see, if you only supply the year the date defaults to the first day of the current month, as mentioned in the documentation:

If you specify a date value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a date value without a date, then the default date is the first day of the current month.

It's not really clear what you plan to do with the passed value, but if you expect it to be the first of the year you'll need to provide that more fully, either with a longer literal and format model, or more simply with a date literal:

SELECT DATE '2015-01-01' FROM DUAL;

DATE'2015-01-01'  
-------------------
2015-01-01 00:00:00

Or if you want to base the value on the current year you can truncate that instead:

SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;

TRUNC(SYSDATE,'YYYY')
---------------------
2015-01-01 00:00:00