Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month (11.2.0.4)
ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656616] Wed, 12 October 2016 15:05 Go to next message
azeem87
Messages: 96
Registered: September 2005
Location: dallas
Member
Hi,

was having this procedure where it accepts Table_name and # of months to retain partition and drop remaining.
CREATE OR REPLACE PROCEDURE Dropping_historical_partitions (p_table_name varchar, p_months number)
AS
lv_msg        varchar2(100);
   lv_drop_part   VARCHAR2 (2000);
   lv_err_code    NUMBER;
   lv_err_mesg    VARCHAR2 (2000);
   search_tbl_name varchar2(50);
   lv_start_time      TIMESTAMP;
   lv_end_time        TIMESTAMP;
BEGIN
 SELECT SYSDATE
     INTO lv_start_time
     FROM DUAL;
    
     lv_msg := ' Started  Drop Partitions';
   select distinct table_name into search_tbl_name from user_tab_partitions where table_name=p_table_name;
    FOR x IN (SELECT table_name, partition_name, high_value
               FROM user_tab_partitions
              WHERE table_name = p_table_name)
   LOOP
      IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'MM') <ADD_MONTHS (SYSDATE,- p_months)
         )
      THEN
         BEGIN
            lv_drop_part :=
                  'ALTER TABLE '
               || x.table_name
               || ' DROP PARTITION '
               || x.partition_name
               || ' UPDATE GLOBAL INDEXES';
                
            DBMS_OUTPUT.put_line (   ' Dropping Partition:'
                                  || x.partition_name
                                  || CHR (10)
                                 );
            DBMS_OUTPUT.put_line (lv_drop_part || CHR (10));

            EXECUTE IMMEDIATE lv_drop_part;
            
             SELECT SYSDATE INTO lv_end_time FROM DUAL;
         
         EXCEPTION
            WHEN OTHERS
            THEN
               lv_err_code := SQLCODE;
               lv_err_mesg := SUBSTR (SQLERRM, 1, 100);
               DBMS_OUTPUT.put_line (   'Error Occurred - '
                                     || lv_err_code
                                     || lv_err_mesg
                                    );
        SELECT SYSDATE  INTO lv_end_time   FROM DUAL;
     
      COMMIT;
      
         END;
      END IF;
   END LOOP;

       EXCEPTION
               WHEN NO_DATA_FOUND THEN
                     DBMS_OUTPUT.put_line ('Specified Table Does Not Exist. Please specify the correct TABLE NAME' );
                     SELECT SYSDATE  INTO lv_end_time   FROM DUAL;
                        COMMIT;
END;
/ 


Execute the Procedure
EXECUTE Dropping_historical_partitions ( 'TRANS_LOG_PARTITION', 12);
it gives an error at this line   --  IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'MM') <ADD_MONTHS (SYSDATE,- p_months)

BEGIN Dropping_historical_partitions ( 'TRANS_LOG_PARTITION', 12); END;
Error at line 1
ORA-01843: not a valid month

changed the date format to below and ran again..

IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'YYYY-MM-DD') <ADD_MONTHS (SYSDATE,- p_months)


EXECUTE Dropping_historical_partitions ( 'TRANS_LOG_PARTITION', 12);
Error at line 1
ORA-01847: day of month must be between 1 and last day of month

Please advice, if there is a different way to write this procedure and call it through scheduler please share.

Thanks for your time.
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656619 is a reply to message #656616] Wed, 12 October 2016 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ I doubt that "SUBSTR (x.high_value, 11, 10)" is a "MM" month value.
2/ Display and post high_value values, I bet you have not a date in the format you think.


Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656620 is a reply to message #656619] Wed, 12 October 2016 15:26 Go to previous messageGo to next message
azeem87
Messages: 96
Registered: September 2005
Location: dallas
Member
TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

just took that random value from highvalue,
this column datatype is LONG.

I tried with
IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'YYYY-MM-DD') <ADD_MONTHS (SYSDATE,- p_months)
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656621 is a reply to message #656620] Wed, 12 October 2016 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For me year starts at position 10 not 11.
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656623 is a reply to message #656621] Wed, 12 October 2016 16:11 Go to previous messageGo to next message
azeem87
Messages: 96
Registered: September 2005
Location: dallas
Member
tried with 10 position.

IF (TO_DATE (SUBSTR (x.high_value, 10, 9), 'YYYY-MM-DD') <ADD_MONTHS (SYSDATE,- p_months)

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Any other procedure / code if anyone has to do same , dropping older than N month partition.

[Updated on: Wed, 12 October 2016 16:21]

Report message to a moderator

Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656624 is a reply to message #656616] Wed, 12 October 2016 17:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just declare variable:

lv_high_value DATE;

And change

IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'MM') <ADD_MONTHS (SYSDATE,- p_months)

to

      EXECUTE IMMEDIATE 'BEGIN :1 :=' || x.high_value || '; END;';
        USING OUT lv_high_value;
      IF lv_high_value < ADD_MONTHS(SYSDATE, - p_months)

SY.
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656625 is a reply to message #656624] Wed, 12 October 2016 17:34 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
  1* select substr(high_value,1,20) LLL, count(*) from dba_tab_partitions group by substr(high_value,1,20) order by 1
SQL> /
select substr(high_value,1,20) LLL, count(*) from dba_tab_partitions group by substr(high_value,1,20) order by 1
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG


Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656626 is a reply to message #656625] Wed, 12 October 2016 18:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2710
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not sure what you mean:

SQL> CREATE OR REPLACE PROCEDURE Dropping_historical_partitions (p_table_name varchar, p_months number)
  2  AS
  3  lv_msg        varchar2(100);
  4     lv_drop_part   VARCHAR2 (2000);
  5     lv_err_code    NUMBER;
  6     lv_err_mesg    VARCHAR2 (2000);
  7     search_tbl_name varchar2(50);
  8     lv_start_time      TIMESTAMP;
  9     lv_end_time        TIMESTAMP;
 10     lv_high_value DATE; -- add declaration
 11  BEGIN
 12   SELECT SYSDATE
 13       INTO lv_start_time
 14       FROM DUAL;
 15
 16       lv_msg := ' Started  Drop Partitions';
 17     select distinct table_name into search_tbl_name from user_tab_partitions where table_name=p_table_name;
 18      FOR x IN (SELECT table_name, partition_name, high_value
 19                 FROM user_tab_partitions
 20                WHERE table_name = p_table_name)
 21     LOOP
 22  --      IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'MM') <ADD_MONTHS (SYSDATE,- p_months) -- replace with next 3 lines
 23        EXECUTE IMMEDIATE 'BEGIN :1 :=' || x.high_value || '; END;'
 24          USING OUT lv_high_value;
 25        IF lv_high_value < ADD_MONTHS(SYSDATE, - p_months)
 26          THEN
 27           BEGIN
 28              lv_drop_part :=
 29                    'ALTER TABLE '
 30                 || x.table_name
 31                 || ' DROP PARTITION '
 32                 || x.partition_name
 33                 || ' UPDATE GLOBAL INDEXES';
 34
 35              DBMS_OUTPUT.put_line (   ' Dropping Partition:'
 36                                    || x.partition_name
 37                                    || CHR (10)
 38                                   );
 39              DBMS_OUTPUT.put_line (lv_drop_part || CHR (10));
 40
 41              EXECUTE IMMEDIATE lv_drop_part;
 42
 43               SELECT SYSDATE INTO lv_end_time FROM DUAL;
 44
 45           EXCEPTION
 46              WHEN OTHERS
 47              THEN
 48                 lv_err_code := SQLCODE;
 49                 lv_err_mesg := SUBSTR (SQLERRM, 1, 100);
 50                 DBMS_OUTPUT.put_line (   'Error Occurred - '
 51                                       || lv_err_code
 52                                       || lv_err_mesg
 53                                      );
 54          SELECT SYSDATE  INTO lv_end_time   FROM DUAL;
 55
 56        COMMIT;
 57
 58           END;
 59        END IF;
 60     END LOOP;
 61
 62         EXCEPTION
 63                 WHEN NO_DATA_FOUND THEN
 64                       DBMS_OUTPUT.put_line ('Specified Table Does Not Exist. Please specify the correct TABLE NAME' );
 65                       SELECT SYSDATE  INTO lv_end_time   FROM DUAL;
 66                          COMMIT;
 67  END;
 68  /

Procedure created.

SQL> drop table tbl purge
  2  /

Table dropped.

SQL> create table tbl(
  2                   dt date
  3                  )
  4    partition by range(dt)
  5      (
  6       partition p201601 values less than(date '2016-02-01'),
  7       partition p201602 values less than(date '2016-03-01'),
  8       partition p201603 values less than(date '2016-04-01'),
  9       partition p201604 values less than(date '2016-05-01'),
 10       partition p201605 values less than(date '2016-06-01'),
 11       partition p201606 values less than(date '2016-07-01'),
 12       partition p201607 values less than(date '2016-08-01'),
 13       partition p201608 values less than(date '2016-09-01')
 14      )
 15  /

Table created.

SQL> column partition_name format a15
SQL> column high_value format a83
SQL> select  partition_name,
  2          high_value
  3    from  user_tab_partitions
  4    where table_name = 'TBL'
  5  /

PARTITION_NAME  HIGH_VALUE
--------------- -----------------------------------------------------------------------------------
P201601         TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201602         TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201603         TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201604         TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201605         TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201606         TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201607         TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201608         TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

8 rows selected.

SQL> exec Dropping_historical_partitions('TBL',6)
Dropping Partition:P201601

ALTER TABLE TBL DROP PARTITION P201601 UPDATE GLOBAL INDEXES

Dropping Partition:P201602

ALTER TABLE TBL DROP PARTITION P201602 UPDATE GLOBAL INDEXES

Dropping Partition:P201603

ALTER TABLE TBL DROP PARTITION P201603 UPDATE GLOBAL INDEXES


PL/SQL procedure successfully completed.

SQL> select  partition_name,
  2          high_value
  3    from  user_tab_partitions
  4    where table_name = 'TBL'
  5  /

PARTITION_NAME  HIGH_VALUE
--------------- -----------------------------------------------------------------------------------
P201604         TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201605         TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201606         TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201607         TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P201608         TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL>


SY.
icon14.gif  Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656655 is a reply to message #656626] Thu, 13 October 2016 10:10 Go to previous messageGo to next message
azeem87
Messages: 96
Registered: September 2005
Location: dallas
Member
Thanks Solomon.
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656658 is a reply to message #656655] Thu, 13 October 2016 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
what results when invoked like below?
SQL> exec Dropping_historical_partitions('TBL',-1)
Re: ORA-01843: not a valid month and ORA-01847: day of month must be between 1 and last day of month [message #656660 is a reply to message #656658] Thu, 13 October 2016 10:51 Go to previous message
Solomon Yakobson
Messages: 2710
Registered: January 2010
Location: Connecticut, USA
Senior Member
And how -1 month can be "historical"? We should follow common sense, shouldn't we? More realistic issue is not handling interval partitioning.

SY.
Previous Topic: How to generate Query aggregation in oracle
Next Topic: Update Statement Correction
Goto Forum:
  


Current Time: Fri Feb 23 09:04:41 CST 2018

Total time taken to generate the page: 0.03484 seconds