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  |
azeem87
Messages: 116
Registered: September 2005
Location: dallas
|
Senior 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 #656625 is a reply to message #656624] |
Wed, 12 October 2016 17:34  |
 |
BlackSwan
Messages: 26766
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 |
Solomon Yakobson
Messages: 3273
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.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 03:19:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
