Converting an Oracle Stored Procedure DATE Input Parameter

Question

I am very new to Oracle and have a question about input parameters to a stored procedure. Basically its a stored procedure being called from an external system passing in a date formatted as MM/DD/YYYY.

Oracle doesn't seem to like the MM/DD/YYYY format as it gives me a "not a valid month" error. (I think it wants like a DD-MMM-YYYY?) whatever the default is.

is there a way to convert the date as it comes into the procedure without getting an error?

such as:

    create procedure test_proc
(
 v_input_date IN DATE := to_char(v_input_date, 'MM/DD/YYYY')
)

I know the above code likely makes no actual sense but hopefully it will convey what I'd like to do. The user would call the procedure something like

BEGIN
test_proc('01/01/2018')
END

Barbaros Özhan · Accepted Answer · 2018-03-23T14:31:26.073

1

You may try with ANSI type date 'yyyy-mm-dd' formatting like in the following sample :

SQL>create or replace procedure test_proc( v_input_date date ) is
  v_diff int;
begin
  v_diff := trunc(sysdate)-v_input_date;
  dbms_output.put_line(v_diff||' days difference...');
end;
/

SQL> set serveroutput on;
SQL> begin
  test_proc(date '2018-03-21');
end;
/
2 days difference...

edited Mar 23 '18 at 14:31

answered Mar 23 '18 at 14:09

Barbaros Özhan

59,113
10
31
55

`v_diff := trunc(sysdate)-v_input_date;` is shorter and faster. – Wernfried Domscheit Mar 23 '18 at 14:20
Thank you very much! I was able to perform exactly what I needed using the date (test_proc(date '2018-03-21');) and yyyy-mm-dd you suggested in the input string. now to get the calling system to send it in this format :)..... THANKS AGAIN! – Gazrok Mar 23 '18 at 14:22

MT0 · Answer 2 · 2018-03-23T15:16:29.333

Your problem is not in the procedure, it is in the code calling the procedure.

'01/01/2018' is not a date it is a string but your procedure expects a date; however, Oracle tries to be helpful and will implicitly try to convert the string to a date using the TO_DATE( string_value, format_model ) function. Since it does not have a specified format model, it will use the default format for a date which is the NLS_DATE_FORMAT session parameter and if this format mask does not match the format of the string then you will get an error.

(Note: session parameters are per-user-session and can be changed by each user so you should not rely on them being the same for each user or even the same between sessions for the same user!)

You can see the format of the NLS_DATE_FORMAT session parameter using the query:

SELECT VALUE
FROM   NLS_SESSION_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT';

And your code to call the procedure is implicitly being converted to something like:

BEGIN
  test_proc(
    TO_DATE(
      '01/01/2018',
      ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
    )
  );
END;

To generate a date you should explicitly convert the string to a date either by:

Using an ANSI literal

BEGIN
  test_proc( DATE '2018-01-01' );
END;

Or by specifying the format mask used in the conversion

BEGIN
  test_proc( TO_DATE( '01/01/2018', 'MM/DD/YYYY' ) );
END;

Converting an Oracle Stored Procedure DATE Input Parameter

2 Answers2