CODEWITHSUNDEEP
oracle-databaseplsqldate-conversion
Gazrok
Gazrok

Reputation: 109

Converting an Oracle Stored Procedure DATE Input Parameter

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

Upvotes: 0

Views: 4890

Answers (2)

MT0
MT0

Reputation: 168416

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:

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

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...

Upvotes: 1

Related Questions