Home » SQL & PL/SQL » SQL & PL/SQL » Extract pl/sql Result into a text file without UTL_FILE pkg and spool (oracle 10g)
icon7.gif  Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440496] Mon, 25 January 2010 03:52 Go to next message
pradeep2002gs
Messages: 19
Registered: August 2009
Junior Member
Hello all,

I using UTL_file i can copy the contents of pl/sql to a file that is in the oracle server where the instance is present but is there any option other than spooling to copy the file to client system where the query is executed..?

Thanks in advance
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440501 is a reply to message #440496] Mon, 25 January 2010 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same thing as for reading: PL/SQL LOOP and UTL_FILE.

Regards
Michel
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440505 is a reply to message #440501] Mon, 25 January 2010 04:36 Go to previous messageGo to next message
pradeep2002gs
Messages: 19
Registered: August 2009
Junior Member
can u explain me in detail.?
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440506 is a reply to message #440505] Mon, 25 January 2010 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, rereading your question I am no more sure you want the result on the client or the server.

Regards
Michel
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440507 is a reply to message #440506] Mon, 25 January 2010 04:47 Go to previous messageGo to next message
pradeep2002gs
Messages: 19
Registered: August 2009
Junior Member
i need the results only at client using pl/sql is there any option..?
thanks in advance
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440509 is a reply to message #440507] Mon, 25 January 2010 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_OUTPUT + SPOOL

Regards
Michel
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440575 is a reply to message #440496] Mon, 25 January 2010 20:08 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
pradeep2002gs

as Michel Said Spool the Dbmsoutput But , make sure that you give a spool file path in common directory , if end user does not have C:\ write rights then you might face problem in spooling the file.

one overhead I think of is you have to always need to make sure that path of end file ( Dir Structure ) is exists on client machine.

-Rahul
Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440610 is a reply to message #440496] Tue, 26 January 2010 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Basically, Oracle lives on the Server, and as such has no access to the client pc.
If you want to write file onto the client, then you need to use some software running on the client to do so.

If you're using SQL*Plus, then you're pretty much limited to SPOOL.
Oracle Forms has TEXT_IO - a package similar to Utl_File, but working on the client (ie from within the form running on your pc).

The options available to you depend entirely on the client software that you're running to connect to ORacle.
icon12.gif  Re: Extract pl/sql Result into a text file without UTL_FILE pkg and spool [message #440740 is a reply to message #440496] Tue, 26 January 2010 22:19 Go to previous message
pradeep2002gs
Messages: 19
Registered: August 2009
Junior Member
Thanks for the reply. Let me try with spool already we are using a software but objective is to eliminate that.
thanks once again for the reply Smile
Previous Topic: case insensitive search
Next Topic: schema export using dbms_metadata
Goto Forum:
  


Current Time: Fri Dec 02 14:30:58 CST 2016

Total time taken to generate the page: 0.22749 seconds