Dear all,
I tried to do output from pl/sql to a client's box, I tried using
dbms_output and it has a hard limit 1 Megabyte. I tried UTL_FILE
package, but it can only output to a Server's directory. How can I run
an PL/SQl and output to screen or a file in client's machine?? Thanks
for your help!!

888

my888@telstra.com wrote:

> Dear all,
> I tried to do output from pl/sql to a client's box, I tried using
> dbms_output and it has a hard limit 1 Megabyte. I tried UTL_FILE
> package, but it can only output to a Server's directory. How can I run
> an PL/SQl and output to screen or a file in client's machine?? Thanks
> for your help!!
>
> 888
>

AskTom has a topic "how to get around Dbms_output limitations":

http://asktom.oracle.com/pls/ask/f?p...A:146412348066

-Mark Bole

Mark,
I knew that solution, however, the output is getting from an SQL rather
from pl/sql. How do I do it inside PL/SQL??? Thank you!

888

my888@telstra.com wrote:

> Dear all,
> I tried to do output from pl/sql to a client's box, I tried using
> dbms_output and it has a hard limit 1 Megabyte. I tried UTL_FILE
> package, but it can only output to a Server's directory. How can I run
> an PL/SQl and output to screen or a file in client's machine?? Thanks
> for your help!!
>
: AskTom has a topic "how to get around Dbms_output limitations":
>
>> Mark,
>> I knew that solution, however, the output is getting from an SQL rather
>> from pl/sql. How do I do it inside PL/SQL??? Thank you!
>>
>> 888
>>

I'd have to see an example of exactly what you are trying to do,
including version info, and where it is failing, to potentially be of
any further use.

-Mark Bole

my888@telstra.com wrote:
> Dear all,
> I tried to do output from pl/sql to a client's box, I tried using
> dbms_output and it has a hard limit 1 Megabyte. I tried UTL_FILE
> package, but it can only output to a Server's directory. How can I run
> an PL/SQl and output to screen or a file in client's machine?? Thanks
> for your help!!
>
> 888

What version and why? Your question is far less than clear without any
information as to what you are trying to accomplish and the tool you
hope to accomplish it with.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

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

my888@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

my888@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