Home » SQL & PL/SQL » SQL & PL/SQL » Reading File with UL_FILE (Oracle 11g)
Reading File with UL_FILE [message #589540] Mon, 08 July 2013 07:29 Go to next message
luciano.aol
Messages: 2
Registered: July 2013
Location: Brazil
Junior Member
Hello good day.



I'm trying to utilize the utl file to read a txt file and import the data into a table in Oracle.
I've read in various forums and have researched a lot on oracle documentation site and on the internet but can not find the answer to the problem.

The source follows:


Set serveroutput on

DECLARE

arquivo_ler UTL_File.File_Type;

Linha Varchar2 (1000);

BEGIN

arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);

Loop

UTL_File.Get_Line (arquivo_ler, Linha);

dbms_output.put_line (Linha);

End Loop;

UTL_File.Fclose (arquivo_ler);

DBMS_OUTPUT.PUT_LINE ('File processed with sucesso.');

END;

/



The errors:

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 633

ORA-29283: invalid file operation

ORA-06512: at line 5



What has been done:

Created DIRECTORY (INTRANET_LOAD) and given the GRANT read, write to the user

On Linux where Oracle is installed, was given full access to the Oracle user folder: / u01/app/oracle/product/11.2.0/db_1/adp





When writing the query;

SELECT *

FROM ALL_TAB_PRIVS

WHERE table_name = 'INTRANET_LOAD';





Grateful for any help.



Luciano



The data are returned.



1 FOLLOW INTRANET INTRANET_LOAD SYS READ NO NO

2 FOLLOW INTRANET IN NO WRITE SYS INTRANET_LOAD
Re: Reading File with UL_FILE [message #589543 is a reply to message #589540] Mon, 08 July 2013 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum

Please read and follow How to use [code] tags and make your code easier to read?

Oracle appears to be complaining about this line:
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);

which at first glance is invalid syntax, I assume it really reads like this:
arquivo_ler:= UTL_FILE.FOPEN('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);


In which case the problem is one of the following:
1) directory doesn't exist on the db server
2) file doesn't exist
3) oracle directory object doesn't refer to the directory you think it does.
3) oracle doesn't have read privs on the directory
4) oracle user doesn't have read privs on the directory object.

Now I know you say you've checked these, but I can't think of any other options, so I suggest you recheck.
Re: Reading File with UL_FILE [message #589544 is a reply to message #589540] Mon, 08 July 2013 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session.

The line:
Quote:
arquivo_ler: UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);

is syntactically invalid.

Regards
Michel

[Updated on: Mon, 08 July 2013 07:39]

Report message to a moderator

Re: Reading File with UL_FILE [message #589545 is a reply to message #589544] Mon, 08 July 2013 08:15 Go to previous messageGo to next message
luciano.aol
Messages: 2
Registered: July 2013
Location: Brazil
Junior Member
Michel,

Thanks for your attention.

I´ve changed for this:

arquivo_ler:= UTL_FILE.FOPEN('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);


I´ve already tried for this way, but the error remains.
I´ve checked other condition that you wrote, but still don´t works.

I´ll check again!!

Regards.

Luciano

Re: Reading File with UL_FILE [message #589548 is a reply to message #589545] Mon, 08 July 2013 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use SQL*Plus and copy and paste your session.

But read the links I provided you before.

Regards
Michel
Re: Reading File with UL_FILE [message #589549 is a reply to message #589548] Mon, 08 July 2013 08:57 Go to previous messageGo to next message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
Try the following
DECLARE
arquivo_ler UTL_File.File_Type;
Linha Varchar2 (1000);
BEGIN
arquivo_ler := UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
Loop
UTL_File.Get_Line (arquivo_ler, Linha);
dbms_output.put_line (Linha);
End Loop;
exception
when utl_file.read_error then
  UTL_File.Fclose (arquivo_ler);
  DBMS_OUTPUT.PUT_LINE ('File processed with sucesso.');
END;
/
Re: Reading File with UL_FILE [message #589551 is a reply to message #589549] Mon, 08 July 2013 09:04 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2  arquivo_ler UTL_File.File_Type;
  3  Linha Varchar2 (1000);
  4  BEGIN
  5  arquivo_ler := UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
  6  Loop
  7  UTL_File.Get_Line (arquivo_ler, Linha);
  8  dbms_output.put_line (Linha);
  9  End Loop;
 10  exception
 11  when utl_file.read_error then
 12    UTL_File.Fclose (arquivo_ler);
 13    DBMS_OUTPUT.PUT_LINE ('File processed with sucesso.');
 14* END;
SQL> /
arquivo_ler := UTL_FILE.FOPEN = ('INTRANET_LOAD', 'carga_intranet.txt', 'R', 32767);
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PLS-00306: wrong number or types of arguments in call to 'FOPEN'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

Re: Reading File with UL_FILE [message #589552 is a reply to message #589551] Mon, 08 July 2013 09:06 Go to previous messageGo to next message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
Sorry, made a typo in the open statement. Leave out the second "=". The point was you need an exception handle to catch the error when it tries to read past the last row in the flat file.
Re: Reading File with UL_FILE [message #589553 is a reply to message #589552] Mon, 08 July 2013 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
this EXCEPTION handler does more harm than good.
Re: Reading File with UL_FILE [message #589555 is a reply to message #589553] Mon, 08 July 2013 09:15 Go to previous messageGo to next message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
can also use NO_DATA_FOUND

[Updated on: Mon, 08 July 2013 09:17]

Report message to a moderator

Re: Reading File with UL_FILE [message #589558 is a reply to message #589555] Mon, 08 July 2013 09:23 Go to previous message
cookiemonster
Messages: 10573
Registered: September 2008
Location: Rainy Manchester
Senior Member
should use no_data_found, since that's what it throws when it reaches the end of the file.
But OPs error is pointing to the fopen not get_line, so this isn't the problem.
Previous Topic: coding inside the trigger
Next Topic: Return Receipt in Oracle Email
Goto Forum:
  


Current Time: Mon Apr 21 07:48:45 CDT 2014

Total time taken to generate the page: 0.08121 seconds