| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Convert PL/SQL code to DBMS_SQL code
Hi
Try execute immediate 'alter table xx ad partition..........'
That would be simpler, I would use DBMS_SQL for heavy dynamic SQL statements, but for your requirements, 4 execution per day execute immediate is more than enough
Thanks
-- LSC On 6/17/07, Nizar Ahmed <gnahmed.c_at_stc.com.sa> wrote:Received on Sun Jun 17 2007 - 05:53:11 CDT
>
>
>
> 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;
>
>
>
> -- dbms_output.put_line('Difference Days: ' || ndiff);
>
> -- dbms_output.put_line('Days to Add: ' || ndays_needed);
>
> -- dbms_output.put_line('Last Partition Date: ' || max_part_date);
>
>
>
> 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);
>
> -- partition 4 format is 1 more day
>
> 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-l
 |
 |