Home » SQL & PL/SQL » SQL & PL/SQL » Using internal procedures and spooling output (10g windows/unix)
Using internal procedures and spooling output [message #349688] Mon, 22 September 2008 12:15 Go to next message
Messages: 1
Registered: September 2008
Location: Baltimore, MD
Junior Member

In a nutshell, here's my problem:
I need a way to spool output from within an internal procedure that is called from a java class file. I'd prefer the logs on the application server, not the Db server.

I know (or can't seem to get working) I cannot spool from within an internal procedure (set serveroutput on... spool C:\test.txt is not allowed). I can use the UTL_FILE package, but that dumps to the db server and I would really prefer the logging on the app server. I can setup a java class file to fork a process that calls a bat/shell script that, in turn, calls a sql file. This sql file could setup the spooling and call the internal proc. This works, but creates some security issues.

My pseudo program flow is as follows:
Package P as

Procedure CallP as

set serveroutput on
spool C:\out.txt
{Call CallP}
spool off

bat/sh file...
sqlplus scott/tiger@db @C:\test.sql

The java file can do this:
cs = con.prepareCall("CALL CALLP()");
Or this:
Process p = rt.exec("C:\\testbat.bat");

The prepareCall is cleaner and more secure, but only allows me to use UTL_FILE pkg. Spawning process to the bat file give me use of spooling, but is not the best way to go.

Is there a way to utilize spooling/dbms_output (or some app server logging) and still use the prepareCall?

Thank you all.
Re: Using internal procedures and spooling output [message #349689 is a reply to message #349688] Mon, 22 September 2008 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65082
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no way.

Re: Using internal procedures and spooling output [message #350333 is a reply to message #349688] Wed, 24 September 2008 14:56 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe you could consider sending messages to some kind of logging agent? Your plsql writes a message to somewhere. The agent picks it up and does whatever you want with it, including dumping it to a log.

Good luck, Kevin
Previous Topic: Problem with calling statement level after insert trigger
Next Topic: Query improvement (avoiding code duplication, etc)
Goto Forum:

Current Time: Fri Jul 21 10:08:03 CDT 2017

Total time taken to generate the page: 0.09353 seconds