| New - Not using Oracle Reporting Tools - Need Help Please [message #8587] |
Tue, 02 September 2003 18:58  |
Tracy
Messages: 43
Registered: January 2000
|
Member |
|
|
Hi. I am new to PL*SQL and know TSQL. I have created a script that will sum data from various tables in my database, the output needs to be spooled from sqlplus and I am not using the Oracle reporting tools.
I have two different issues:
1st - For each table I am pulling data from, I need a header - I have tried the dbms_output, but I do not think I am using correctly, or there is another command I should be using.
2nd - When spooling the data, I noticed that the totals are not lining up with the proper column.
Example:
Select count(*) "row_count",
sum(column a) "column a,
sum(column b) "column b
from table_one
Select count (*) "row_count",
sum(column c) "column c",
sum(column d) "column d",
sum(column e) "column e"
from table_two
I would like the results to be:
Table One:
Row Count Column A Column B
457 225.36 458.96
Table Two:
Row Count Column C Column D Column E
1452 0.00 1458.97 452698.96
Any assistance would be appreciated!
Thanks,
Tracy
|
|
|
|
| Re: New - Not using Oracle Reporting Tools - Need Help Please [message #8588 is a reply to message #8587] |
Wed, 03 September 2003 01:05  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Before you can use dbms_output.put_line, you should enable it by the sql*plus command [b]Set Serveroutput On [[size]]. The size is the buffersize you need, but, dbms_output will store all output in a buffer and will display it in the end of the pl/sql block it is used in. In a script, I prefer SELECT 'something' FROM dual instead of EXEC(UTE) dbms_output.put_line('something');
I find the 'select from dual' approach much more flexible (dbms_output.put_line is limited to 255 chars per line).
If still not clear on this, it would help if you'd say whether you're doing something in PL/SQL or in an SQL script.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
