Hi
I have written a script to truncate oldest partition by inserting partition_name and high_value into a temporary table.

DECLARE
min_highval varchar(1000);
part_name varchar(30);
trunc_part varchar2(1000);
create_part_temp varchar2(2000);
drop_part_temp varchar2(1000);

BEGIN
create_part_temp := 'create table part_temp (partname varchar(30),highval varchar2(1000))';
execute immediate create_part_temp;

FOR a in (select partition_name,high_value from dba_tab_partitions
where (table_name='ORDERITEM_NEW' and NUM_ROWS != 0)) LOOP

insert into part_temp values (a.partition_name,substr(a.high_value,10,11));
END LOOP;

select partname into part_name from part_temp where highval = (select min(highval) from part_temp);

trunc_part := 'alter table orderitem_new truncate partition '|| part_name || ' update global indexes';
execute immediate trunc_part;

drop_part_temp := 'drop table part_temp';
execute immediate drop_part_temp;
end;

while executing this script, I am unable to insert values into temporary table.


Error Message:

Error report:
ORA-06550: line 16, column 19:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 16, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 19, column 39:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 19, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:


Kindly suggest.

select min(partition_name) keep (dense_rank first order by partition_position) 
from   user_tab_partitions 
where  table_name = 'ORDERITEM_NEW';