Home » Developer & Programmer » Data Integration » Read File (Oracle EBS, R12.0.0.6, Windows)
Read File [message #661337] Wed, 15 March 2017 08:13 Go to next message
Mraouf
Messages: 14
Registered: June 2016
Location: Egypt
Junior Member
--CREATE or replace DIRECTORY USER_DIR AS 'D:\Reading_File';
CREATE or replace DIRECTORY USER_DIR AS '/ora/oracle/';
GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;
--GRANT read, write ON DIRECTORY USER_DIR TO Public ;

DECLARE
V1 VARCHAR2(200); --32767
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1 := UTL_FILE.FOPEN('USER_DIR','TEST.txt','R');
Loop
BEGIN
UTL_FILE.GET_LINE(F1,V1);
dbms_output.put_line(V1);
EXCEPTION WHEN No_Data_Found THEN EXIT; END;
end loop;

IF UTL_FILE.IS_OPEN(F1) THEN
dbms_output.put_line('File is Open');
end if;

UTL_FILE.FCLOSE(F1);
END;


it gives an error >>

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 6


Please help
Re: Read File [message #661338 is a reply to message #661337] Wed, 15 March 2017 09:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
  1  DECLARE
  2  V1 VARCHAR2(200); --32767
  3  F1 UTL_FILE.FILE_TYPE;
  4  BEGIN
  5  F1 := UTL_FILE.FOPEN('USER_DIR','TEST.txt','R');
  6  Loop
  7  BEGIN
  8  UTL_FILE.GET_LINE(F1,V1);
  9  dbms_output.put_line(V1);
 10  EXCEPTION WHEN No_Data_Found THEN EXIT; END;
 11  end loop;
 12  IF UTL_FILE.IS_OPEN(F1) THEN
 13  dbms_output.put_line('File is Open');
 14  end if;
 15  UTL_FILE.FCLOSE(F1);
 16* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5


SQL> !touch /tmp/TEST.txt

SQL> /

PL/SQL procedure successfully completed.


Re: Read File [message #661342 is a reply to message #661338] Wed, 15 March 2017 10:23 Go to previous messageGo to next message
Mraouf
Messages: 14
Registered: June 2016
Location: Egypt
Junior Member



Many thanks for support.. i can't understand the below code and also be noted that am working on PL/SQL
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5


SQL> !touch /tmp/TEST.txt

SQL> /

PL/SQL procedure successfully completed.
Re: Read File [message #661343 is a reply to message #661342] Wed, 15 March 2017 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>F1 := UTL_FILE.FOPEN('USER_DIR','TEST.txt','R');
above throws error when FOPEN fails; such as when file does not exist or Oracle process does not have required OS permission to access the file.
Re: Read File [message #661345 is a reply to message #661343] Wed, 15 March 2017 11:09 Go to previous messageGo to next message
Mraouf
Messages: 14
Registered: June 2016
Location: Egypt
Junior Member
i will be appreciated if you told me the steps i have to do to resolve this error
Re: Read File [message #661347 is a reply to message #661345] Wed, 15 March 2017 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Verify/Ensure the file exists and Oracle has access to it.

[Updated on: Wed, 15 March 2017 11:26]

Report message to a moderator

Re: Read File [message #661348 is a reply to message #661347] Wed, 15 March 2017 11:28 Go to previous messageGo to next message
Mraouf
Messages: 14
Registered: June 2016
Location: Egypt
Junior Member
the file already exist but how to know if oracle has access or not..
NB. i have 2 files testing on them one of the on my local machine and the other one on the oracle test server..
Re: Read File [message #661349 is a reply to message #661348] Wed, 15 March 2017 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The database server (that is PL/SQL code) can access only database server file.

Re: Read File [message #661407 is a reply to message #661337] Thu, 16 March 2017 14:23 Go to previous message
mikek
Messages: 29
Registered: January 2017
Junior Member
Just from Observation of the Post Contents.

I would make sure the file TEST.txt is in the directory specified by the
Create Directory Command:

CREATE OR REPLACE DIRECTORY USER_DIR AS '/ora/oracle/';

The sample output provided indicate that the touch command is creating a
file in the /tmp/ directory and then the procedure is successful.

This leads me to believe that the Create Directory is using the /tmp/ Directory
so the TEST.txt file needs to be in the /tmp/ Directory to work.

Check Oracle Directory Info, use the following SQL:
SELECT * FROM all_directories;

I reformatted/restructured the code for ease of review.

--CREATE OR REPLACE DIRECTORY USER_DIR AS 'D:\Reading_File';
--GRANT read, write ON DIRECTORY USER_DIR TO PUBLIC ;
 
CREATE OR REPLACE DIRECTORY USER_DIR AS '/ora/oracle/';
GRANT read ON DIRECTORY USER_DIR TO PUBLIC;

DECLARE
 v1 VARCHAR2(200); --32767
 f1 UTL_FILE.FILE_TYPE;
BEGIN
  f1 := UTL_FILE.FOPEN('USER_DIR', 'TEST.txt', 'R');

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(f1, v1);

      DBMS_OUTPUT.PUT_LINE(v1);
    EXCEPTION
      WHEN no_data_found THEN EXIT;
    END;
  END LOOP;

  IF UTL_FILE.IS_OPEN(f1) THEN
    DBMS_OUTPUT.Put_line('File is Open');
  END IF;

  UTL_FILE.FCLOSE(f1);

END;
/

Previous Topic: Oracle EBS integration with CRM
Next Topic: Save Query results into text file
Goto Forum:
  


Current Time: Mon Mar 18 22:24:38 CDT 2024