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

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