ORA-01843: not a valid month

• rockstar283

  SSCrazy

  Points: 2704

  More actions

  May 11, 2012 at 3:49 pm

  #255818

  I am creating a table in Oracle using the execute sql task of ssis..the code is as follows:

  BEGIN

  execute immediate '

  create table mem1 nologging as

  select distinct t1.*,

  CAST(TO_CHAR(ADD_MONTHS(TO_DATE(sysdate, ''MM/DD/YYYY''), -3), ''yyyymm'') AS NUMBER) as Report_mth

  from pat_1mon t1,

  pat_6mon t2

  where t1.pat_uid = t2.pat_uid (+)

  and t2.pat_uid is null

  group by t1.pat_uid';

  END;

  I am getting the following error when the package is executed:

  [Execute SQL Task] Error: Executing the query "BEGIN

  execute immediate '

  create table mem1 nolog..." failed with the following error: "ORA-01843: not a valid month

  ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  If I run the same query in Oracle without 'execute immediate' part, then I am getting the correct result.

  Can someone please help me out here..

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  May 16, 2012 at 2:43 am

  #1488242

  Maybe you'll have more luck in an Oracle forum?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP