Home » SQL & PL/SQL » SQL & PL/SQL » read and validate file (Oracle Data Base 11g r2)
icon1.gif  read and validate file [message #675268] Tue, 19 March 2019 19:28 Go to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
I have to read a file with UTL_FILE in a stored procedure but I also have to do other things for example if the file is empty I must return in variables a number for example 1 and a message "The file is empty", or if the file name that I am sending does not exist then it must return in variables a code 2 and a message "The file does not exist" otherwise I must return the contents of the file in a variable.
Re: read and validate file [message #675269 is a reply to message #675268] Tue, 19 March 2019 20:51 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

This is not a free coding service site & we don't do homework.
You don't need permission from anyone here to do as your desire.
Please post your code & SHOW any problem that may exist.
Re: read and validate file [message #675272 is a reply to message #675268] Wed, 20 March 2019 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PL/SQL User's Guide and Reference

Search for "CREATE FUNCTION".

Re: read and validate file [message #675282 is a reply to message #675272] Wed, 20 March 2019 11:58 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Sorry I forgot to put my code, here it is:

CREATE OR REPLACE PROCEDURE hcm_estructures AS
  /*-Files validation variables-*/
  l_file_exists BOOLEAN;
  l_file_len    NUMBER;
  l_blocksize   BINARY_INTEGER;
  /*Variables read file*/
  v_archivo utl_file.file_type;
  v_linea clob;
  v_path varchar2(100):= 'PREPROCESSOR_DIRECTORY';
BEGIN
  utl_file.fgetattr(
    location    => v_path,
    filename    => 'Worker_19032019.dat',
    fexists     => l_file_exists,
    file_length => l_file_len,
    block_size  => l_blocksize);
   if l_file_exists then
    if l_file_len > 0 then
      --dbms_output.put_line('The file will read correctly');
      v_archivo := utl_file.fopen ('PREPROCESSOR_DIRECTORY', 'Worker_19032019.dat', 'r',32767);
      loop
         utl_file.get_line (v_archivo, v_linea);
         dbms_output.put_line (v_linea);
      end loop;
      --utl_file.fclose(v_archivo);
    else
      dbms_output.put_line('The file is empty');
    end if;
  else
    dbms_output.put_line('The file does not exists');
  end if;
END;

ORA-29284: file read error ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at "HCM_ESTRUCTURES", line 23
ORA-06512: at line 3
Re: read and validate file [message #675283 is a reply to message #675282] Wed, 20 March 2019 12:11 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well for starters you should use a single variable/parameter for each of path and filename and use that throughout to avoid any possibility of typos messing you up.
Secondly - you've got an infinite loop in there so you should probably be glad it errored out. Plain loops need exit statements.

Fix those and if it still errors let us know and tell us which line the error stack is pointing to.
Re: read and validate file [message #675284 is a reply to message #675282] Wed, 20 March 2019 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also:
ORA-29284: file read error
 *Cause:  An attempt to read from a file failed.
 *Action: Verify that the file exists, and that it is accessible, and
          that it is open in read mode.
Re: read and validate file [message #675285 is a reply to message #675283] Wed, 20 March 2019 12:35 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Hi cookiemonster,

How can I add the exit statement for the loop?

I'm new to PL / SQL, I still do not have many clear things although in point one about handling a single variable for the file name and another one for the path so try to do it but when I put the variable v_path inside the if I throw an error that says that the variable v_path must be declared.....



Re: read and validate file [message #675286 is a reply to message #675285] Wed, 20 March 2019 19:26 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Consider to start with a working code example & then customize to meet your requirements

https://ora-data.blogspot.com/2016/11/read-and-writ-text-file-using-UTLFILE.html
Re: read and validate file [message #675287 is a reply to message #675286] Wed, 20 March 2019 19:30 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Thanks BlackSwan, but I have already solved my problem
Re: read and validate file [message #675288 is a reply to message #675287] Wed, 20 March 2019 19:42 Go to previous messageGo to next message
BlackSwan
Messages: 26722
Registered: January 2009
Location: SoCal
Senior Member
Bartholomew Kuma wrote on Wed, 20 March 2019 17:30
Thanks BlackSwan, but I have already solved my problem

If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Re: read and validate file [message #675289 is a reply to message #675288] Wed, 20 March 2019 20:00 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Of course here is the code, I hope it helps future apprentices like me, regards.

CREATE OR REPLACE PROCEDURE hcm_estructures(p_file_name in varchar2,p_line out clob,p_retcode OUT number,p_errmes out varchar2) AS
  /*-Files validation variables-*/
  l_file_exists BOOLEAN;
  l_file_len    NUMBER;
  l_blocksize   BINARY_INTEGER;
  /*Variables read file*/
  v_file utl_file.file_type;
  v_path varchar2(100):= 'PREPROCESSOR_DIRECTORY';
  v_line clob;
BEGIN
  utl_file.fgetattr(
    location    => v_path,
    filename    => p_file_name,
    fexists     => l_file_exists,
    file_length => l_file_len,
    block_size  => l_blocksize);
   if l_file_exists then
    if l_file_len > 0 then
      v_file := utl_file.fopen (v_path, p_file_name, 'r',32767);
      LOOP
    BEGIN
      utl_file.get_line(v_file, v_line);
      if p_line is null then
         p_line := v_line;
      else
         p_line := p_line ||chr(10)|| v_line;
      end if;  
    EXCEPTION
      WHEN no_data_found THEN
        exit;
    END;
  END LOOP;
    else
      p_errmes := 'The file is empty';
      p_retcode := 2;
    end if;
  else
    p_errmes := 'The file does not exists';
    p_retcode := 1;
  end if;
END;
/
Re: read and validate file [message #675290 is a reply to message #675289] Thu, 21 March 2019 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the source of ORA-29284 was?

Re: read and validate file [message #677005 is a reply to message #675290] Tue, 06 August 2019 12:09 Go to previous messageGo to next message
Bartholomew Kuma
Messages: 12
Registered: March 2019
Junior Member
Michel Cadot wrote on Thu, 21 March 2019 00:36

And the source of ORA-29284 was?


This happened because of the way you try to save each recovered line in line only with this instruction:

utl_file.get_line (file_v, v_line);

When I added another instruction to concatenate the new line with the previously retrieved line then that was when it worked correctly.

utl_file.get_line(v_file, v_line);
if p_line is null then
      p_line: = v_line;
else
      p_line: = p_line || chr (10) || v_line;
end if;
Re: read and validate file [message #677008 is a reply to message #677005] Tue, 06 August 2019 12:57 Go to previous message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thank you for future readers.

Previous Topic: Oracle insert statement
Next Topic: Is it possible to insert a folder from a directory into a table?
Goto Forum:
  


Current Time: Fri Jul 10 02:20:03 CDT 2020