Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Convert PL/SQL code to DBMS_SQL code
I have code whose purpose is to add 4 partitions to a table every day 6 hourly. I need to convert this code to one using DBMS_SQL. I am not a good programmer, appreciate any help.
Here is the code.
Thanks,
set serveroutput on
set linesize 512
set feedback off
declare
ndiff number;
ndays_needed number;
max_part_date date;
min_part_date date;
nLoop number;
vLine1 varchar2(512);
vLine2 varchar2(512);
vLine3 varchar2(512);
vLine4 varchar2(512);
vLine5 varchar2(512);
vpart1 varchar2(255);
vpart2 varchar2(512);
vpart3 varchar2(512);
vpart4 varchar2(512);
vpart_value varchar2(100);
vpart_name varchar2(100);
begin
dbms_output.enable(100000);
select to_date(substr(max(partition_name),11,10), 'YYYY_MM_DD'),
to_date(substr(min(partition_name),11,10), 'YYYY_MM_DD') into
max_part_date, min_part_date from user_tab_partitions
where table_name = 'POSTMED_CDR';
ndiff := max_part_date - min_part_date;
ndays_needed := 13 - ndiff;
for nLoop in 1..ndays_needed loop
vpart_name := to_char(max_part_date + nLoop,'YYYY_MM_DD');
vpart_value := to_char(max_part_date + nLoop,'YYYY-MM-DD');
vLine1 := 'ALTER TABLE postmed_cdr ADD PARTITION p_postmed_';
dbms_output.put_line(vLine1);
vLine1 := vLine1 || vpart_name;
dbms_output.put_line(vLine1) ;
vLine2 := ' VALUES LESS THAN (TO_DATE(''';
vLine3 := '''YYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''));'; vpart1 := vLine1 || '_1' || vLine2 || vpart_value ||
' 06:00:00'', ' || vLine3;
dbms_output.put_line(vLine1);
vpart2 := vLine1 || '_2' || vLine2 || vpart_value ||
' 12:00:00'', ' || vLine3;
dbms_output.put_line(vLine1);
vpart3 := vLine1 || '_3' || vLine2 || vpart_value ||
' 18:00:00'', ' || vLine3;
dbms_output.put_line(vLine1);
vpart_value := to_char(max_part_date + nLoop + 1,'YYYY-MM-DD');
vpart4 := vLine1 || '_4' || vLine2 || vpart_value ||
' 00:00:00'', ' || vLine3;
dbms_output.put_line(vLine1);
dbms_output.put_line(vpart1);
dbms_output.put_line(vpart2);
dbms_output.put_line(vpart3);
dbms_output.put_line(vpart4);
end loop;
dbms_output.put_line('exit');
end;
/
G. Nizar A. Baig
Disclaimer: The information in this email and in any files transmitted with it, is intended only for the addressee and may contain confidential and/or privileged material. Access to this email by anyone else is unauthorized. If you receive this in error, please contact the sender immediately and delete the material from any computer. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is strictly prohibited. Statement and opinions expressed in this e-mail are those of the sender, and do not necessarily reflect those of STC.
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jun 17 2007 - 04:16:28 CDT
![]() |
![]() |