Home » SQL & PL/SQL » SQL & PL/SQL » How to generate textfiles in local machine using procedure (oracle 9.i , Windows xp )
How to generate textfiles in local machine using procedure [message #443075] Fri, 12 February 2010 07:52 Go to next message
shyjupkandachira
Messages: 3
Registered: February 2010
Location: kerala
Junior Member
Hi, how to generate textfiles in local machine(Client) using oracle procedure
Re: How to generate textfiles in local machine using procedure [message #443077 is a reply to message #443075] Fri, 12 February 2010 08:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL executes on server and doesn't create file on client.
You can use dbms_output package in procedure to bufferize messages and same thing on client program to read the buffer (or serveroutput on with SQL*Plus).
Then you can record these information in a file as you want (or with spool in SQL*Plus).

Regards
Michel

[Edit: typo]

[Updated on: Fri, 12 February 2010 08:02]

Report message to a moderator

Re: How to generate textfiles in local machine using procedure [message #443078 is a reply to message #443075] Fri, 12 February 2010 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't, oracle procedures run on the db server, they can't see the client machine. What are you trying to achieve?
Re: How to generate textfiles in local machine using procedure [message #443130 is a reply to message #443077] Fri, 12 February 2010 22:21 Go to previous messageGo to next message
shyjupkandachira
Messages: 3
Registered: February 2010
Location: kerala
Junior Member
Hi,

Thanks for your support..

This is the code I am using the procedure to create text file.
Now the file is generating in server path. I am executing this procedure in a client PC using a small application , Now the issue is that I want the text file in the client path ,
Can you pls help me.
-- Creation of outputfile starts here

lv_output_path := '/home/frs/NEFT';
--I need in client pc 'C:\Output\'
lv_output_acc_file := 'TEST.TXT' ;
l_out_acc_file := utl_file.fopen(lv_output_path,lv_output_acc_file,'w');
utl_file.put_line(l_out_acc_file,lv_head);
utl_file.put_line(l_out_acc_file,pv_content);---pv_content content of file
utl_file.fclose(l_out_acc_file);
Re: How to generate textfiles in local machine using procedure [message #443159 is a reply to message #443130] Sat, 13 February 2010 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 12 February 2010 15:01
PL/SQL executes on server and doesn't create file on client.
You can use dbms_output package in procedure to bufferize messages and same thing on client program to read the buffer (or serveroutput on with SQL*Plus).
Then you can record these information in a file as you want (or with spool in SQL*Plus).

Regards
Michel

That means you have to change your procedure and small application.
You cannot do it with your current procedure.

Regards
Michel

Re: How to generate textfiles in local machine using procedure [message #443180 is a reply to message #443159] Sat, 13 February 2010 06:11 Go to previous messageGo to next message
shyjupkandachira
Messages: 3
Registered: February 2010
Location: kerala
Junior Member
I am new in sql , can you pls give more hints or code for dbms_output and spool
Re: How to generate textfiles in local machine using procedure [message #443184 is a reply to message #443180] Sat, 13 February 2010 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How many more times do you need to be told that no Oracle PL/SQL procedure can write to client's disk?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: How to generate textfiles in local machine using procedure [message #443186 is a reply to message #443075] Sat, 13 February 2010 08:54 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ok there is one way to achieve what you want to do .. Create the file on server and copy it to the client machine..[use host commnad ]( if you are not using the web application )

this solution will work ONLY for intranet.

Re: How to generate textfiles in local machine using procedure [message #443188 is a reply to message #443186] Sat, 13 February 2010 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>[use host commnad ]

PL/SQL procedure has NO "host" command; but either Java or EXTERNAL PROCEDURE can operate at OS Level.
Re: How to generate textfiles in local machine using procedure [message #443189 is a reply to message #443188] Sat, 13 February 2010 09:03 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan wrote on Sat, 13 February 2010 08:57

PL/SQL procedure has NO "host" command;


My Bad . Razz Thanks for adding "EXTERNAL PROCEDURE can operate at OS Level" so this is doable
Re: How to generate textfiles in local machine using procedure [message #443192 is a reply to message #443180] Sat, 13 February 2010 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shyjupkandachira wrote on Sat, 13 February 2010 13:11
I am new in sql , can you pls give more hints or code for dbms_output and spool

DBMS_OUTPUT.
Maybe you have to be able to refer to documentation.

Regards
Michel

Re: How to generate textfiles in local machine using procedure [message #443298 is a reply to message #443186] Mon, 15 February 2010 00:40 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rahulvb wrote on Sat, 13 February 2010 15:54
Ok there is one way to achieve what you want to do .. Create the file on server and copy it to the client machine..[use host commnad ]( if you are not using the web application )

this solution will work ONLY for intranet.

Even on intranets a server will typically not have any knowledge of individual client-addresses, so using the server as an initiator for copying files is in general a bad idea.
Even if the client's address were known to the server, things like firewalls, OS, etc of the client might interfere.

[Updated on: Mon, 15 February 2010 00:41]

Report message to a moderator

Previous Topic: regexp expected
Next Topic: Recompile invalid object type
Goto Forum:
  


Current Time: Sun Dec 11 02:17:36 CST 2016

Total time taken to generate the page: 0.07428 seconds