Reputation: 79
Hei, I am having problem with creating partitions using PL/SQL procedure:
ORA-14019: partition bound element must be one of: string, datetime or interval literal, number, or MAXVALUE
It seems like problem is in format of attribute r.partition_date, I already run some tests script and tried different date format, but nothing helped.
Format of column partition_date : 2017-09-01
I am running this procedure:
declare
v_new_part varchar2(30);
v_prev_part varchar2(30);
v_sql varchar2(4000);
v_table varchar(30) := 'RL2_OUTDW_CORP_PRISK_REV_B';
begin
for r in (select distinct a.partition_date , a.partition_name from XX a
where a.PARTITION_NAME = 'PARTITION_20170831'
order by 1)
loop
v_sql := 'alter table ' || v_table || ' SPLIT PARTITION
PARTITION_30000101
at (''N'',to_date(' || r.partition_date || ',''yyyy.mm.dd''))
into (partition ' || r.partition_name || ' ,partition
PARTITION_30000101)';
execute immediate v_sql;
end loop;
end;
/
Even though this one is working:
declare
v_new_part varchar2(30);
v_prev_part varchar2(30);
v_sql varchar2(4000);
v_table varchar(30) := 'RL2_OUTDW_CORP_PRISK_REV_B';
begin
for r in (select distinct a.partition_date , a.partition_name from XX a
where rownum = '1'
order by 1)
loop
v_sql := 'alter table ' || v_table || ' SPLIT PARTITION
PARTITION_30000101
at ("N",to_date(''2017-01-29'',''yyyy.mm.dd''))
into (partition ' || 'PARTITION_20170129' || ' ,partition
PARTITION_30000101)';
execute immediate v_sql;
end loop;
end;
/
Thank you for advice.
Upvotes: 0
Views: 369
Reputation: 59652
I don't understand the purpose of "N"
but I think it should be this:
v_sql := 'alter table ' || v_table || ' SPLIT PARTITION
PARTITION_30000101
at (DATE '''||TO_CHAR(r.partition_date,'yyyy-mm-dd')||''')
into (partition ' || r.partition_name || ' ,partition
PARTITION_30000101)';
Note, when you use DATE
literal then format is fixed YYYY-MM-DD
, otherwise use like
at (TO_DATE('''|| TO_CHAR(r.partition_date,'dd.mm.yyyy') ||''', ''dd.mm.yyyy'')`
Upvotes: 1
Reputation: 65433
first of all remove asterisks in this
(''N'',to_date(' || **r.partition_date** || ',''yyyy.mm.dd''))
part of first sentence.
And format inthe cursor
for r in (select distinct
to_char(a.partition_date,'yyyy.mm.dd')
par_date , a.partition_name from XX a where rownum = '1' order by 1)
And add two quotations around :
(''N'',to_date('
''
|| r.par_date||''
',''yyyy.mm.dd''))
SQL>set serveroutput on;
SQL> declare
v_new_part varchar2(30);
v_prev_part varchar2(30);
v_sql varchar2(4000);
v_table varchar(30) := 'RL2_OUTDW_CORP_PRISK_REV_B';
begin
for r in (select distinct to_char(a.partition_date,'yyyy.mm.dd') par_date , a.partition_name from XX a
where a.PARTITION_NAME = 'PARTITION_20170831'
order by 1)
loop
v_sql := 'alter table ' || v_table || ' SPLIT PARTITION
PARTITION_30000101
at (''N'',to_date('''|| r.par_date || ''',''yyyy.mm.dd''))
into (partition ' || r.partition_name || ' ,partition
PARTITION_30000101)';
dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;
/
Upvotes: 1