0

I have a long series of calls like:

DBMS_OUTPUT.put_line(v_1||','||v_2);

I have only read priveleges to the database and invision writing the ouptut from the statement above to a common location on all computers that I might read from later (using VBA).

I've found UTL_FILE packages that Oracle seems to support but I am having trouble understanding how to get it to do what I want.

Could someone provide me with a simple example of how I could use the put_line method and UTL_FILE packsage in tandem to write to a common location such as the desktop of a computer?

ZAX
  • 968
  • 3
  • 21
  • 49
  • 1
    `utl_file` can only write data to files on the database server's file system (or a file system available to the database server). Unless the database server is running Windows and your goal is to write a file on the desktop of the Windows database server (which seems very odd), you can't use `utl_file` to do so. If you want to create a file on the client file system, you'd need a program that runs on the client. That client application could be SQL*Plus. It could be something you write. – Justin Cave Aug 13 '14 at 16:12
  • @JustinCave I am using Toad if that is what you mean by client application. My issue is that every time I run the procedure that involves the dbms_output, I want the output to be saved automatically somewhere. However I see no Toad utilities out there that can accomplish this – ZAX Aug 13 '14 at 16:15
  • 1
    TOAD has a dbms_output panel that certainly has a file save function. – OldProgrammer Aug 13 '14 at 16:50

1 Answers1

1

Spooling is a SQL*Plus feature one can perform on your desktop without invoking the UTL_FILE database package. Toad (utilizing the SQL*Plus feature) can do this as well.

As Justin Cave commented, UTL_FILE is an Oracle database package intended for reading and writing to the database server (e.g. 11g documentation http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_file.htm#BABGGEDF).

Spooling is documented here for SQL*Plus (associated with the Oracle 11g database http://docs.oracle.com/cd/E11882_01/server.112/e27507/sqlplus.htm#DFSUG144 section 3.1.7).

You could select 'Run as a script' in TOAD as follows: 

set serveroutput on
spool c:\temp.lst
begin
  dbms_output.put_line('My text');
end;
/
spool off

Spooling is a client side feature (SQL*Plus), thus if one wanted to have invocations to dbms_output within a procedure (below, I call it my_procedure), I would just create a sql script that drives the procedure.

One could make this the contents of a sql script (e.g. test_dbms_output.sql):

SET serveroutput ON
spool c:\temp.lst
BEGIN
  my_procedure(params);
END;
/
spool OFF

Then, one just can invoke this script with the SQL*Plus run command (or 'Run as a script' in Toad):

@test_dbms_output.sql;

or 

run test_dbms_output.sql;
Patrick Bacon
  • 4,490
  • 1
  • 26
  • 33