How to execute dynamic sql code 2

[rogers42]

Hi Folks,

I would like to package the following code in a file (on Unix) and to execute it via the Oracle schedular. However, I can't seem to make the dynamic part of the code to execute.

Can somebody please make a suggestion.

The code is as follows

select case when to_char(SYSDATE, 'DY') = 'MON' then 'select count(*) from test partition(monday);'
when to_char(SYSDATE, 'DY') = 'TUE' then 'select count(*) from test partition(tuesday);'
when to_char(SYSDATE, 'DY') = 'WED' then 'select count(*) from test partition(wednesday);'
when to_char(SYSDATE, 'DY') = 'THU' then 'select count(*) from test partition(thursday);'
when to_char(SYSDATE, 'DY') = 'FRI' then 'select count(*) from test partition(friday);'
when to_char(SYSDATE, 'DY') = 'SAT' then 'select count(*) from test partition(saturday);'
when to_char(SYSDATE, 'DY') = 'SUN' then 'select count(*) from test partition(sunday);'
end
from dual;


Thanks in advance

rogers42

 

[LKBrwnDBA]

Try EXECUTE IMMEDIATE:

declare
 str Varchar2(512);
 cnt Number;
begin
  str:='Select Count(*) From test_partition('
     ||To_Char(SYSDATE,'day')||')'; 
  EXECUTE IMMEDIATE str INTO cnt;  
  dbms_output.put_line("Todays count is '||cnt);
end;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

An alternate method to LK's excellent PL/SQL script uses a combination of SQL*Plus and SQL:

Sample Data, partitioned on START_DAY:

select first_name||' '||last_name Employee, start_day from test;

EMPLOYEE             START_DAY
-------------------- ---------
Midori Nagayama      monday
Roberta Menchu       monday
Colin Magee          monday
Yasmin Sedeghi       monday
Vikram Patel         tuesday
Marta Havel          wednesday
Mai Nguyen           wednesday
Andre Dumas          wednesday
Radha Patel          wednesday
LaDoris Ngao         thursday
George Smith         thursday
Sylvie Schwartz      thursday
Molly Urguhart       friday
Elena Maduro         friday
Eddie Chang          friday
Carmen Velasquez     saturday
Mark Quick-To-See    saturday
Ben Biri             saturday
Henry Giljum         saturday
Akira Nozaki         saturday
Audry Ropeburn       sunday
Antoinette Catchpole sunday
Chad Newman          sunday
Alexander Markarian  sunday
Bela Dancs           sunday

Script contents:

col Day_of_Week new_value Day_of_week
select to_char(sysdate,'fmday') Day_of_Week from dual;
select count(*) from test partition(&Day_of_Week);

Output from script:

  COUNT(*)
----------
         1

Let us know your reactions to these methods.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[rogers42]

Perhaps I had not stated the question quite clearly. But, "exec immediate" was what I was looking for in order to execute the code.

Thanks for the prompt replies guys

rogers42