I am invoking an oracle procedure from a ORDS API.
In the oracle procedure, I am inserting a record to a table where I am assigning a value of sysdate to a date column.
When I run that procedure from PL/SQL developer tool, the date is filled correctly and the record is being created as i expect.
But when I call the ORD API from Postman, the insert statement fails with error: "ORA-20999: ORA-01843: not a valid month"
when I dig bit deeper, I found that the date format of sysdate is different when it is invoked via the ORDS API from postman.
A similar program would be something like this:
PROCEDURE p_test IS
v_startdate date;
BEGIN
v_startdate := sysdate;
insert into table1(date1) values (v_startdate);
END;
We have a hint that this issue is related to NLS setting which is set during the session run but not able to find out where do we need to make this change. Can someone help me to resolve this issue ?