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????

I think you are a bit confused between SQL query executed from a client SQL*Plus session and executed within a PL/SQL block. Executing a query from client SQL*Plus session (e.g. select * from dept;) returns a resultset to the client SQL*Plus session from the server... which can be either displayed on the screen and/or spooled to a file. Query executed inside a PL/SQL block... like in your example above... entirely runs on the server side without returning a resultset to the client machine. Client SQL*Plus session will be unable to grab hold of this resultset from a PL/SQL block unless it is somehow buffered up (see dbms_output) which can be retrieved by the client SQL*Plus session by "set serverout on". Ofcourse there is a limit to this buffer and the buffered lines are ONLY displayed once the PL/SQL block has been executed.

Depending on how you are connecting to the database i.e. OCI, ODBC, JDBC etc, there could be workarounds to resolve this. AFAIK, you can't do what you want to do in a SQL*Plus session. You would have to provide more information i.e. your client/middleware, purpose of doing this sort of thing etc. if you want any suggestions that may suit your needs.

Regards
/Rauf Received on Fri Dec 31 2004 - 18:09:50 CST