I need to do the following:
Have a table that has 92 partitions. They are named as part001, part002..... part092.
Want to do insert into the partition directly. The partition i want to insert into will be called based on the value of a parameter i send in to the stored procedure.
A simple way is to use IF..THEN ..ELSE... but for 92 times its a little too repetitious.
ie., if value = 1 then
insert into emp partition (part001).....
elsif value = 2 then
insert into emp partition (part002)....
Can I build my insert string dynamically? ie.,
create or replace procedure doinsert as
So, if VALUE = 2, then
if length(value) = 1 then
i = '00' || to_char(VALUE)
elsif length(calue) = 2 then
i = '0' || to_char(VALUE)
elsif length(value) = 3 then
i = to_char(VALUE)
v_part = 'part' || i
Insert into partition v_part ... ..........
The above doesn't seem to work. Any suggestions please....
vTable Varchar2 (10);
vTable := 'part' || LPAD (value, 3, '0');
Insert into partition vTable...
Márcio de Souza Almeida
DBA Oracle / SQLServer / PostgreSQL
Rua Cupa, 139 Apto 85 A
Penha - São Paulo - SP
03640-000 - Brasil
Click Here to Expand Forum to Full Width