spool

[JeanneZ]

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.

 

[SantaMufasa]

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:

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.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:34 (25Aug04) UTC (aka "GMT" and "Zulu"), 12:34 (25Aug04) Mountain Time)

 

[JeanneZ]

Thanks a lot for your help. Please send me some sample code. I need it in detail. Thanks again.

 

[SantaMufasa]

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,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:34 (30Aug04) UTC (aka "GMT" and "Zulu"), 09:34 (30Aug04) Mountain Time)

 

[JeanneZ]

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