Inserting Comuted totals in .csv export

[kc]

Hi,

This may be very simple, I am more of an advanced user than a DBA, so my knowledge is a bit limited.

I have set up a simple query to export a number of reports to multiple locations. It loops through a list of paramters for each report.

But I need to get it to compute a few totals at various points. Obviously this is easy when developing, just tell it to compute, but when it is looping in the background, I can't get it to output the totals to file.

Any ideas?

Thanks

K

 

[oraclelover]

Where is your SQL running? Straight SQL doesn't allow you to do loops. Of course the internal process Oracle uses to process your query may being uses loops to get your data. Please explain your problem in more detail.

 

[kc]

Hi Henkel,
Sorry that probably was a bit vague. (I am using SQL*Plus.)

1. Created query. Say: select unit, account, sum(month_01) as total from table where unit = &unit
group by unit, account /
compute sum of total on report
Break on report

2. Wrote pl/sql code to run that creates a list of parameters for &unit, and it loops through them all, and outputs to nomintated locations as .csv files.

3. However, in the pl/sql, I can't work out how to tell it to include the computed totals on the outputted report. As it is more of a formatting thing, can you create rules for the .csv export that tells it what the format is to be?

Thanks

Katherine

 

[Bo Pedersen]

The commands break on and compute sum are SQL*Plus commands. They are not available in PL/SQL.

If you want to calculate the total in PL/SQL then you have to declare a variable and manually add the total for each loop iteration to this variable.

You should also check out the report facility in PL/SQL Developer. It allows you to write a query and then add breaks, totals, formatting etc to give you a nice looking HTML report. This report can be saved in different formats including CSV.