Search code examples
sqloracleutl-filedbms-output

Write Output of DBMS.OUTPUT.put_line To Specified Common Location Like Desktop

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?

Solution

  • 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;