Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

spool

Status
Not open for further replies.

JeanneZ

Programmer
Joined
May 1, 2003
Messages
55
Location
US
I have a question to consult experts.
I need to spool the data by dept no to excel file and the file name will be with dept no.
For example, I have 10 depts. I would like to spool the data to 10 excel files. The script should include a cursor. I do not know how to do it.

Here is my sql script but it does not work:

Declare
cursor c1 is
select dept_no from dept;
begin
for r1 in c1 loop
spool c:\dept&r1.dept_no.xls;
select emp_id, first_name, last_name
from emp
where dept_no = r1.dept_no;
spool off;
end loop;
end;

Thanks a lot for any help.
 
Jeanne,

First, spool is a SQL*PLus command. PL/SQL understands no SQL*Plus. If you wish to SPOOL output from PL/SQL, you must issue the SPOOL command prior to the beginning of your PL/SQL block's execution; SPOOL OFF must occur after your PL/SQL block ends execution.

Secondly, PL/SQL has no native output commands. If you wish to generate output from PL/SQL so that the SPOOL command can record the output, you must use Oracle's packaged procedure, "DBMS_OUTPUT.PUT_LINE". That package also requires a pre-execution SQL*Plus command to accept output from the server to your client screen. That SQL*Plus command, which must occur prior to PL/SQL block execution is:
Code:
set serveroutput on

Since the SPOOL command must occur outside your PL/SQL block, the renaming of your output files to the names of the respective departments become problematic: No SPOOL-file name changing within the PL/SQL loop. You can, however, output for all departments to one spool file, then manually parse the data to separate files.

Another issue is that PL/SQL or SQL*Plus has no built-in awareness of Excel-file formats. Excel, however, can understand Comma-Separated Values (.CSV) files. You can output your department data using delimiters such as "," (commas); "','" (quote-comma-quote sequences); or "<tab>" to separate your output values.

Now, another alternative that will allow you to change spool-file names within your department loop is to output your .CSV flat file(s) via Oracle PL/SQL's "UTL_FILE" packaged procedures. (We can post code samples that illustrate UTL_FILE usage if you are not familiar with how to use it.) The problem with UTL_FILE usage is that it can see (i.e., READ or WRITE) only files that reside on the database-server machine's file systems. If you have access to your database-server machine's file systems and this sounds more like the solution you want, then let us know.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 19:34 (25Aug04) UTC (aka "GMT" and "Zulu"), 12:34 (25Aug04) Mountain Time)
 
Thanks a lot for your help. Please send me some sample code. I need it in detail. Thanks again.
 
Jeanne,

Before I post code for you, please tell me if you can access server-side file systems. Also, to be able to use the UTL_FILE flat-file-access package, your Oracle instance parameter, "utl_file_dir" must be not null. Can you please confirm those two characteristics of your Oracle environment?

Thanks,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 16:34 (30Aug04) UTC (aka "GMT" and "Zulu"), 09:34 (30Aug04) Mountain Time)
 
Mufasa,
Sorry for replying you late. Yes. I can access server-side and able to use the UTL_FILE. Thanks a lot for your help.
Jeanne
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top