Oracle Database Discussions

I am trying to write a proc for dropping old partitions.

Can we use dba views(dba_tab_partitions) inside a procedure.

create or replace procedure test_Purge as
part_name varchar2(30);
begin
select PARTITION_NAME into part_name from dba_tab_partitions where TABLE_NAME like 'DET_NOTES' and PARTITION_POSITION=1;
dbms_output.put_line('Partition that will be deleted is :'|| part_name);
execute immediate 'alter table DET_NOTES drop partition'|| part_name ||' update global indexes';
end;
/


Will this work?

Thanks in advance.