Home » SQL & PL/SQL » SQL & PL/SQL » How to read a text file ?
How to read a text file ? [message #197585] Wed, 11 October 2006 22:15 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
how to read a text file in windows platform through pl/sql

In linux utl_file is working. but not in windows.

Re: How to read a text file ? [message #197588 is a reply to message #197585] Wed, 11 October 2006 22:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Is the file on the Oracle Server, or on your Windows client?

UTL_FILE should work the same way whether Oracle is running on Windows or Linux, but it can only access drives mounted on the server - never the client.

What error are you getting? What does your code look like?

Ross Leishman
Re: How to read a text file ? [message #197597 is a reply to message #197588] Wed, 11 October 2006 23:24 Go to previous messageGo to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member

the following code is not working for windows


declare
ufile utl_file.file_type;
s varchar2(200);
begin
ufile := utl_file.fopen('c:\', 'a.txt', 'R');
utl_file.get_line(ufile,s);
utl_file.fclose(ufile);
dbms_output.put_line(s);
end;



Error is:

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 204
ORA-06512: at line 4

Re: How to read a text file ? [message #197614 is a reply to message #197597] Thu, 12 October 2006 01:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can't just specify a directory like that in the FOPEN function. That first parameter ('C:\' in your example) has to be an Oracle directory object created with the CREATE DIRECTORY command.

You almost certainly do not have the privileges to do this. DBAs jealously gaurd the right to create directories, because in the wrong hands you could wipe out the entire database.

Speak to your DBA about having a directory object created that points to the Windows directory on your Oracle Server where the file resides.

Read the UTL_FILE doco.

Ross Leishman
Previous Topic: Easier Way
Next Topic: How to use Distinct clause to create the XML from SQLXML
Goto Forum:
  


Current Time: Mon Dec 05 15:12:57 CST 2016

Total time taken to generate the page: 0.08115 seconds