CODEWITHSUNDEEP
sqloracle-database
Mrgr8m4
Mrgr8m4

Reputation: 502

Oracle PL/SQL - Not a valid month error

I am trying to create calendar table using this PL/SQL script from Oracle site: Date Dimension Data Generator

Everything works good, but I cannot run statement from section 5:

SELECT *
FROM TABLE(UDF_CALENDAR_GENERATOR(CAST('1-JAN-2016' AS DATE), CAST('31-DEC-2016' AS DATE)));

I get error:

  1. 00000 - "not a valid month"

*Cause:
*Action:

I tried to use to_date function (based od this answer):

SELECT *
FROM TABLE(UDF_CALENDAR_GENERATOR(TO_DATE('14-Apr-2015', 'DD-MON-YYYY'), TO_DATE('14-May-2015', 'DD-MON-YYYY')));

But it doesn't work. What is wrong with this statement?

Upvotes: 1

Views: 3642

Answers (4)

Barbara Clark
Barbara Clark

Reputation: 11

Find default date format: SELECT * FROM NLS_SESSION_PARAMETERS;

in sql developer: at beginning of query: alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY';

at end of query (to return to default format): alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

BUT stored procedure is different. after the BEGIN: execute immediate 'alter session set NLS_DATE_FORMAT = ''MM/DD/YYYY''';

At end of stored procedure, just before 'END ' : execute immediate 'alter session set NLS_DATE_FORMAT = ''DD-MON-RR''';

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

Someone should tell the author that in Oracle, date literals are written like date '2016-01-01', so the query should be:

select *
from   table(udf_calendar_generator(date '2016-01-01', date '2016-12-31'));

The CAST() expression is effectively TO_DATE() with no date format or language specified, requiring an element of luck for it to work in any given environment.

Upvotes: 5

hekko
hekko

Reputation: 292

SELECT *
FROM TABLE(
    UDF_CALENDAR_GENERATOR(
        TO_DATE('14-Apr-2015', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'), 
        TO_DATE('14-May-2015', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')
    )
);

Upvotes: 5

pablomatico
pablomatico

Reputation: 2242

It must be a date format issue, be sure that your date format is DD-MON-YYYY and ,since you're using English, be also sure that your date languate is English:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';

Upvotes: 4

Related Questions