Script to loop through query and export results to excel

Question

I have the following script which I am using to loop through a query and export the results in an excel file.

begin
    for months in 0..12 loop
      data_dump( query_in     => 'select count(*) from reservation where trunc(update_date) between (select add_months(TRUNC(SYSDATE), -(months) ) from dual) and (select add_months(TRUNC(SYSDATE),-(months+1)) from dual)',
                               file_in      => 'excel_'||months||'.csv',
                              directory_in => 'C:\Users\Administrator\Desktop\test',
                              delimiter_in => '|' );
     end loop;
end;

The data_dump is a procedure that exports the results to a excel file.

I am trying to make the formula I use dynamic like below:

(select add_months(TRUNC(SYSDATE), -(months) ) from dual)

the months variable comes from the loop, but when i run the query it turns with errors.

Help with the syntax would be appreciated.

Please provide the error.

I3rutt
– I3rutt

2018-05-24 10:13:14 +00:00
Commented May 24, 2018 at 10:13 — I3rutt
– I3rutt, Commented May 24, 2018 at 10:13

I3rutt · Accepted Answer · 2018-05-24 10:19:55Z

2

I believe that problem is here:

'select count(*) 
from reservation 
where trunc(update_date) between 
(select add_months(TRUNC(SYSDATE), -(months) ) from dual) 
and (select add_months(TRUNC(SYSDATE),-(months+1)) from dual)'

In your case 'months' is just text. You need change expression in this way:

'select count(*) 
from reservation 
where trunc(update_date) between 
(select add_months(TRUNC(SYSDATE), -('|| months ||') ) from dual) 
and (select add_months(TRUNC(SYSDATE),-('|| months+1 ||')) from dual)'

answered May 24, 2018 at 10:19

I3rutt

6025 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kanagaraj · Accepted Answer · 2018-05-25 04:05:12Z

0

try the months like ''||months||'' to get values dynamically

answered May 25, 2018 at 4:05

kanagaraj

4421 gold badge4 silver badges8 bronze badges

Collectives™ on Stack Overflow

Script to loop through query and export results to excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related