Re: Convert PL/SQL code to DBMS_SQL code

  • From: Dan Norris <dannorris@xxxxxxxxxxxxx>
  • To: exriscer@xxxxxxxxx, gnahmed.c@xxxxxxxxxx
  • Date: Sun, 17 Jun 2007 06:20:58 -0700 (PDT)

You're definitely going to love the partitioning enhancements in 11g! I'm 
guessing that won't help you now, but definitely worth checking it out after 
the release.

Dan

----- Original Message ----
From: LS Cheng <exriscer@xxxxxxxxx>
To: gnahmed.c@xxxxxxxxxx
Cc: oracle-l <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, June 17, 2007 5:53:11 AM
Subject: 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@xxxxxxxxxx> wrote:















 


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.







Other related posts: