CODEWITHSUNDEEP
oracle-databaseplsql
Robert Lepen
Robert Lepen

Reputation: 79

Creating partitions

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

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

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

Barbaros Özhan
Barbaros Özhan

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

Related Questions