Re: How to output to screen or a file in client's machine via PL/SQL

my888_at_telstra.com wrote:

> my example:
>
> declare
> fielname varchar2(256);
> begin
> filename := '/tmp/myoutput';
>
> for x in (select from dept) loop
> output_to_screen(x.emp_no|| x.emp_name);
> save_to_file_in_clients_machine(filename, x.emp_no|| x.emp_name);
> end loop;
> end;
>
> of course I can use the solution from asktom to replace
> output_to_screen to his my_dbms_output.put_line, however, the output
> would not be displayed on the screen until the next select from an SQL
> statement. How can I output it to screen??? and save to a file in the
> client's machine????
>

Still not clear on the underlying problem you're trying to solve....so here go some suggestions, use or not as you see fit.

What client software is the user running? Why not just use plain SQL queries under SQL*Plus (or SQL*Plus Worksheet if they need a Windows tool) on the client side with SPOOL (and other formatting) options and forget PL/SQL? This seems the easiest and best way to do what you ask.

Is your problem with the PL/SQL approach strictly one of flushing the buffered output more frequently? What is the importance to the user of having the screen and file written to simultaneously? (In other words, is the problem really a problem?)

If the user is trying to get this data into MS Excel or Access, just use ODBC. Or, generate the output file on the server using UTL_FILE (as you already considered) and then let the user download it using FTP or HTTP (both available through most browsers), or, heck, even e-mail it to them.

-Mark Bole Received on Fri Dec 31 2004 - 13:47:35 CST