Home » SQL & PL/SQL » SQL & PL/SQL » error in adding partition (oracle 10g)
error in adding partition [message #400432] Tue, 28 April 2009 03:30 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Quote:
SQL> exec pr_add_partitions;
Last partition Name=ALO_052009
Last partition date 2009-06-01 00:00:00
Last Partition Date = 01-JUN-09
New Partition Name = ALO_062009
ALTER TABLE ACE2_LIST_OUTPUT ADD PARTITION ALO_062009 VALUES LESS THAN ( TO_DATE('01-JUL-09 00:00:00
ORA-01858: a non-numeric character was found where a numeric was expected
BEGIN pr_add_partitions; END;

*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SVEN_LENIN_PROD.PR_ADD_PARTITIONS", line 89
ORA-06512: at line 1


please find the above error of the below procedure

create or replace procedure pr_add_partitions
AS

TYPE partrec IS RECORD (
table_name VARCHAR2 (30),
partition_name VARCHAR2 (30),
upperbound VARCHAR2 (83),
tablespace_name VARCHAR2 (70)
);


CURSOR p_cur
IS
SELECT DISTINCT table_name
FROM user_tab_partitions
WHERE table_name='ACE2_LIST_OUTPUT'; 


CURSOR part_cur (i_table_name IN VARCHAR2)
IS
SELECT table_name, partition_name, high_value, tablespace_name
FROM user_tab_partitions
WHERE table_name = i_table_name
ORDER BY partition_position DESC;


prec partrec;
v_last_partition VARCHAR2 (50);
v_next_partition VARCHAR2 (500);
v_last_part_date VARCHAR2 (50);
v_next_part_date DATE;
v_part_tmp_date DATE;
v_part_date DATE;
v_initial_date DATE;
i_initial_date DATE;
v_sql VARCHAR2 (300);
v_initial_part VARCHAR2 (30);
i_initial_part VARCHAR2 (30);
c_dummy_partition CONSTANT VARCHAR2 (5) := 'DUMMY';
parent_tname VARCHAR2 (30);
v_missing_months NUMBER;
BEGIN
--EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';


FOR cur IN p_cur
LOOP


OPEN part_cur (cur.table_name);


FETCH part_cur
INTO prec;


CLOSE part_cur;


v_last_partition := prec.partition_name;
DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition);
v_last_part_date := SUBSTR (prec.upperbound, 10, 20);
DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date);
v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS');
DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date);
v_part_tmp_date := v_part_date;
-- v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date);
-- v_missing_months := v_missing_months + 1; -- 2 monthly partition added from last partiton date



-- FOR i IN 1 .. v_missing_months
-- LOOP
-- IF (v_part_date = LAST_DAY (v_part_date + i))
-- THEN
-- v_part_date := v_part_date + i;
v_next_part_date := ADD_MONTHS (v_part_date, 1);
-- ELSE
-- v_next_part_date := ADD_MONTHS (v_part_date, i);
-- END IF;


v_part_tmp_date := v_part_tmp_date + 1;
-- ALO_MAY2009

--select to_char(to_date('28-APR-09','DD-MON-YY'),'MMYYYY') from dual;


v_next_partition :=
REPLACE (v_last_partition,
SUBSTR (v_last_partition,5),
to_char(to_date(v_next_part_date -1,'DD-MON-YY'),'MMYYYY')
);
DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition);
v_sql :=
'ALTER TABLE '
|| prec.table_name
|| ' ADD PARTITION '
|| v_next_partition
|| ' VALUES LESS THAN ( TO_DATE('''
|| v_next_part_date
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) '
|| ' tablespace '
|| prec.tablespace_name;


DBMS_OUTPUT.put_line (v_sql);
EXECUTE IMMEDIATE v_sql;


COMMIT;
END LOOP;
-- END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RAISE;
END pr_add_partitions;
Re: error in adding partition [message #400437 is a reply to message #400432] Tue, 28 April 2009 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
It would make our life a lot easier if you told us which line is line 89.

One thing I did notice:
to_char(to_date(v_next_part_date -1,'DD-MON-YY'),'MMYYYY')


Why are you to_dateing a date?
Re: error in adding partition [message #400441 is a reply to message #400437] Tue, 28 April 2009 04:09 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
line no 89 is


i am doing to_char(to_date to get the output in the below format.

Last partition Name=ALO_052009
Last partition date 2009-06-01 00:00:00
Last Partition Date = 01-JUN-09
New Partition Name = ALO_062009
ALTER TABLE ACE2_LIST_OUTPUT ADD PARTITION ALO_062009 VALUES LESS THAN ( TO_DATE('01-JUL-09 00:00:00', 'DD-MON-YY HH24:MI:SS')) tablespace TBS_SVEN_DATA
ORA-14120: incompletely specified partition bound for a DATE column
Re: error in adding partition [message #400443 is a reply to message #400432] Tue, 28 April 2009 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 12405
Registered: September 2008
Location: Rainy Manchester
Senior Member
line 89 is what?

Never to_date a date. To do so is a bug and is probably the cause of your problem
Re: error in adding partition [message #400446 is a reply to message #400441] Tue, 28 April 2009 04:33 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-14120: incompletely specified partition bound for a DATE column
 *Cause:  An attempt was made to use a date expression whose format
          does not fully (i.e. day, month, and year (including century))
          specify a date as a partition bound for a DATE column.
          The format may have been specified explicitly (using
          TO_DATE() function) or implicitly (NLS_DATE_FORMAT).
 *Action: Ensure that date format used in a partition bound for a
          DATE column supports complete specification of a date
          (i.e. day, month, and year (including century)).
          If NLS_DATE_FORMAT does not support complete
          (i.e. including the century) specification of the year,
          use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-YYYY')
          to fully express the desired date.

Regards
Michel
Previous Topic: drop all objects in a schema
Next Topic: prob
Goto Forum:
  


Current Time: Tue Dec 06 04:30:42 CST 2016

Total time taken to generate the page: 0.30306 seconds