Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bettine.

Asked: March 07, 2025 - 11:15 am UTC

Last updated: March 13, 2025 - 1:50 pm UTC

Version: 24.2.2

Viewed 1000+ times

You Asked

I'm trying to follow along with the live lab 'Build a 'Doctor Appointments Made Easy' App using Oracle APEX
Introduction' to get to know the new Workflows in Application Express.
In Lab 9 task 6 I run into an error in the Check Appointment Fee activity: ORA-01843: not a valid month.
Since I'm still very new to Application Express I have no clue where to look for cause of this error.

When I check the details for the workflow, the following activities are completed:
- Start
- Compute Doctor Availability
- Doctor Available?
- Raise Appointment Request
- Appointment Approved?
- Confirm Appointment

Then this activity is faulted:
- Check Appointment Fee
ORA-01843: not a valid month

Can you provide me some help with fixing this issue?


and Connor said...

Sorry I don't know much about that lab, so I'd suggest you post this on the APEX forum

https://forums.oracle.com/ords/apexds/domain/dev-community/category/apex

but in terms of your error - it happens when an input string does not match the expected format to convert it to a date. For example

SQL> select sysdate from dual;

SYSDATE
---------
10-MAR-25


so I can see that in this session, my database is expecting strings to come in as DD-MON-YY. If I differ from that


SQL> select to_date('10/03/2025') from dual;
select to_date('10/03/2025') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


Best practice is typically to make your impervious to such settings by explicitly including it, eg

SQL> select to_date('10/03/2025','dd/mm/yyyy') from dual;

TO_DATE('
---------
10-MAR-25


so in the context of the lab, they might be expecting a particular format.

Rating

  (2 ratings)

Comments

Best practice

Sergiusz Wolicki, March 12, 2025 - 11:38 pm UTC

The best practice is to use DATE literals:

select date '2025-10-03' from dual;

This makes the date specification independent from both NLS_DATE_FORMAT and NLS_CALENDAR, and it is a much shorter notation.

The problem with the DATE literals is that they do not support the time part. If you want to specify the time part, you need something like:

select cast(timestamp '2025-10-03 23:55:37' as date) from dual;

The cast is especially important if such a specification is included in a predicate when it is compared to an indexed DATE column. TIMESTAMP has higher priority than DATE in implicit conversions, so if the explicit cast is missing, the column's data type is converted to TIMESTAMP, making index range access impossible.

Chris Saxon
March 13, 2025 - 1:50 pm UTC

Thanks for sharing Sergiusz.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.