Oracle 10g - Write queries results to file

Question

Hello,

I want to run 200+ select queries and append the results to a file. All queries are the same the only difference in the date-time variable. I don't have privileges to create a routine that's why I had to create all the queries. I don't have privileges to create a view or another table to store the results in. I don't have access to PL/SQL.

Now I need to create a report with the results of each one of this queries (all results are integer numbers) but I don't seem to find another solution but to run one by one and copy the results one by one.

Any of you marvelous brains can give me a hand on this? It's kind of urgent.

Answer 1

You can spool your output to a file.

See the spool (URL - Oracle 10.2 user's guide) command.

Also:

http://www.praetoriate.com/t_garmany_easysql_the_spool_command.htm

And what appears to be some layout tips:

http://www.oracle.com/technology/oramag/code/tips2004/020904.html

Answer 2

1 - Put your queries in a text file like so:

set pagesize 0;

select some_field
from some_table;

select another_field
from another_table;
/

2 - Save it somewhere (let's say c:\my_file.sql)

3 - Run this at the command prompt:

c:\>sqlplus -s username/[email protected] < tmp.sql > output.txt

4 - Look inside "output.txt"

Answer 3

If you have access to sqlplus, you can run anonymous PL/SQL blocks.

DECLARE
 v_cnt number;
BEGIN
 select ... into v_cnt ...;
 dbms_output.put_line(v_cnt);
END;
.
spool out.log
/
spool off