Home » SQL & PL/SQL » Client Tools » output the result to a flat file in local PC (oracle 9i, windows XP SP2)
output the result to a flat file in local PC [message #404283] Thu, 21 May 2009 04:37 Go to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i have a requirement in which i need to provide the client with the flat file containg some data of a PL/SQL . Below the code


begin
	   for record in (select * from employee where city='New York')
	   LOOP
	   dbms_output.put_line( record.FIRST_NAME || record.LAST_NAME );
	   END LOOP;
END;	



Can anyone show me how shall i redirect the o/p of this PL/SQL to a flat file in my local PC
Re: output the result to a flat file in local PC [message #404284 is a reply to message #404283] Thu, 21 May 2009 04:40 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
sql> Spool filename
sql> select * from dual;
sql> spool off
Re: output the result to a flat file in local PC [message #404286 is a reply to message #404283] Thu, 21 May 2009 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use SQL*Plus and SPOOL.
Re: output the result to a flat file in local PC [message #404288 is a reply to message #404283] Thu, 21 May 2009 04:44 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
The destination should be my local C: drive and the file should contain only the data and not anyother things.

Thanks in advance
Re: output the result to a flat file in local PC [message #404291 is a reply to message #404288] Thu, 21 May 2009 04:53 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
So now that you know the tool to use, you can go and look up the documentation, have a wee practice and see if you can do it. Then, if you are still struggling, you can post back with your efforts for further assistance.
Good luck
Re: output the result to a flat file in local PC [message #404292 is a reply to message #404283] Thu, 21 May 2009 04:56 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
What i m asking for is the logic that should be implemented inside the mentioned PL/SQL to put the result/value into a flat file in my LOCAL C: Drive.

Re: output the result to a flat file in local PC [message #404294 is a reply to message #404288] Thu, 21 May 2009 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59410
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusĀ® User's Guide and Reference
Chapter 6 Formatting SQL*Plus Reports
Chapter 12 SQL*Plus Command Reference, section SQL*Plus Command Summary

Maybe you should start to read them.

Regards
Michel
Re: output the result to a flat file in local PC [message #404295 is a reply to message #404292] Thu, 21 May 2009 05:01 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
You do not need to use pl/sql for this. As has already been pointed out (unless you have MASSIVELY over simplified the situation
Re: output the result to a flat file in local PC [message #404296 is a reply to message #404283] Thu, 21 May 2009 05:01 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i have found the below code, dnt know how to implement in the mentione PL/SQL. Please can anyone help

CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';
 
DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/

[Updated on: Thu, 21 May 2009 05:02]

Report message to a moderator

Re: output the result to a flat file in local PC [message #404298 is a reply to message #404296] Thu, 21 May 2009 05:02 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
This will not do as you require. Why are you insisting on ignoring the advice given to you?
Re: output the result to a flat file in local PC [message #404300 is a reply to message #404283] Thu, 21 May 2009 05:05 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
The problem is i want to understand the concept of redirecting the o/p from a PL/SQL to a flatfile. So i posted a simple PL/SQL to understand how the logic works.

Hope everyone understood......

If anyone can provide a simple example related to PL/SQL that would do thanks
Re: output the result to a flat file in local PC [message #404303 is a reply to message #404300] Thu, 21 May 2009 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59410
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
utl_file does not write on client but on server.
This is what papoblee is trying to say you.
The problem is that you are not reading what we say you.

Regards
Michel
Re: output the result to a flat file in local PC [message #404308 is a reply to message #404283] Thu, 21 May 2009 05:19 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
So you mean to say if the oracle 9i is installed on my LOCAL PC and i m writing a PL/SQL Block to output a result in a flat file in my LOCAL c: Drive is not at all posible.

Re: output the result to a flat file in local PC [message #404310 is a reply to message #404308] Thu, 21 May 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59410
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No this is not what I mean.
Read again the first line I posted and think about what it means if client=server.

Regards
Michel
Re: output the result to a flat file in local PC [message #404314 is a reply to message #404308] Thu, 21 May 2009 05:30 Go to previous messageGo to next message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
Nope. Do you think that maybe, just maybe, the fact that your actually working ON THE SERVER might have been relevant information? You used the term CLIENT which implies that you WERE NOT working on the server.
Re: output the result to a flat file in local PC [message #404316 is a reply to message #404283] Thu, 21 May 2009 05:34 Go to previous messageGo to next message
ali560045
Messages: 25
Registered: May 2009
Junior Member
i think i have mis-communicated . See the requirement is simple

1) I have Created the mentioned PL/SQL In My PC where oracle 9i is installed

2) Using the PL/SQL i want to get its result in flat file which should be located in my LOCAL drive C:\

3) I m not using any servers here just working on my local PC

If this simplifies the problem.....................

[Updated on: Thu, 21 May 2009 05:35]

Report message to a moderator

Re: output the result to a flat file in local PC [message #404325 is a reply to message #404316] Thu, 21 May 2009 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59410
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the answer was given.

Regards
Michel
Re: output the result to a flat file in local PC [message #404326 is a reply to message #404316] Thu, 21 May 2009 06:30 Go to previous message
pablolee
Messages: 2655
Registered: May 2007
Location: Scotland
Senior Member
OK, so you found some code that uses utl_file. Have you tried it? played around with it? Had problems? got errors, researched the errors, found solutions, hit more errors, read up on them, didn't understand so decideed to post here? Or did you just find some code and hope that we would write some code for you?
Previous Topic: SQL * Plus report output in vertical allignment
Next Topic: How to save contents?
Goto Forum:
  


Current Time: Tue Oct 21 14:21:19 CDT 2014

Total time taken to generate the page: 0.08910 seconds