Link to home
Start Free TrialLog in
Avatar of pvillare
pvillare

asked on

Text File with LF between lines and CR at the end.

We need to generate a .txt file, with a LF between segments, each segment is a line in text file.
We are generating the .txt file from Oracle Report. we disable PRT files because it was generating the file with CRLF (chr(13) ||chr(10). We cannot use programs like windows2unix because we need at the of the file CR (chr(13)), and with windows2unix it converts the CR to LF.

Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

Hi pvillare, I see you are new to EE. I will suggest that 50 points is not very much if you want to get the attention of all experts, since many have filters not to receive notifications for low point questions. 50 points is often a typo on the user's part.

As to your question, what API / code are you using to generate the file? Oracle Report? Java? PL/SQL?
What is the data source? Where are the segments coming from?

You can explicitly control output by using Java or PL/SQL file API, such as dbms_output. As you noted above, chr(10) and chr(13) are legal expressions in PL/SQL and you can explicitly control the line separator using PL/SQL in that manner.

dbms_output.put(data||chr(10));

Using UTL_FILE package, you can open a file explicitly, and use UTL_FILE.PUT

See the Oracle documentation for UTL_FILE package for specifics, or provide more details so we can help.




Avatar of pvillare
pvillare

ASKER

Hi! Thanks for the information!!! I´ll let you know how it works!!!

Good luck, let me know if you need more help on this, and also, welcome to Experts Exchange!
Avatar of schwertner
If you have an Oracle report the go to the data model and take the SQL.
There are different possibilities to get what you want.

In Oracle to express CR you can use the function CHR(13)
So you can concatenate the records so
rec1 || CHR(13) || rec2 || CHR(13)....
using loops.
See also the code for creating files using UTIL_FILE package
procedure YOUR_PROC   is
 
    sasha SYS.UTL_FILE.FILE_TYPE;
 
     sanjeev   VARCHAR2(1800);
     /* DEFINE HERE A PL/SQL CURSOR TO SELECT THE DATA FROM TABLES */
begin
 
   sasha := UTL_File.Fopen('D:\staff\cv','56789.txt', 'w');
 
 
   FOR .....      /*this is an explicite loop for the cursor */
    LOOP
  /* put the data from the cursor in the buffer "sanjeev"*/
  /*ensure the proper format, i.e. commas as separators */
 
        UTL_File.put_line(sasha,sanjeev);
 
     END LOOP;
 
     UTL_File.Fclose(sasha);
              
 
                 
                 
EXCEPTION                        
WHEN  UTL_File.invalid_filehandle   THEN ... UTL_File.Fclose(sasha);  
       --   invalid_filehandle - not a valid file handle
WHEN  UTL_File.write_error   THEN ... UTL_File.Fclose(sasha); 
        -- OS error occurred during write
WHEN  UTL_File.invalid_operation   THEN ... UTL_File.Fclose(sasha);                     
 
end  YOUR_PROC ;
 
 
REMARK
Server security for PL/SQL file I/O consists of a restriction on 
the directories that can be accessed.
Accessible directories must be specified in the
instance parameter initialization file (INIT.ORA). 
 
Specify the accessible directories for the UTL_FILE 
functions in the initialization file 
using the UTL_FILE_DIR parameter.
For example:
UTL_FILE_DIR = <directory name>
 
Starting from 9i you can use Oracle directories instead:
 
create or replace directory XMLDIR as 'E:\ICW_TASKS\XML\SOURCE\';
grant read on directory xmldir to my_user with grant option;

Open in new window

We create directory EXPORT_DIR, with grant options.
     CREATE DIRECTORY export_dir as '\\farmacia-server\DesaNuevo\cargaris'; <-- has read and write access
        grant read,write on directory  export_dir to public;
We set the utl_file_dir= 'export_dir' in init.ora and reboot the DB
        ALTER SYSTEM SET utl_file_dir='export_dir' scope=spfile;

Using this instruction in a Pl/sql script  v_ADTfile := UTL_FILE.FOPEN('EXPORT_DIR','prueaba.hl7','W');
we get  the following 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 "SFCLINICO.PROC_EXPORT_FILE", line 46
ORA-06512: at line 1

Is there something we are missing? Or is a permission problem??
Thanks for the help

CREATE OR REPLACE PROCEDURE SFCLINICO.PROC_EXPORT_FILE 
(v_fecha_nac in varchar2, v_codigo_pac in  number,v_tipo in varchar2, v_codigo_ant in number) AS
        
 
    v_ADTfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'EXPORT_DIR';
    v_filename      VARCHAR2(20) := 'prueba.txt';
    v_format        VARCHAR2(2000);
    v_valor         VARCHAR2(2000);
    v_secuencia     number(10);
 
---el directorio donde reside la data se creo y se le dieron permisos
---se hace referencia al mismo en la carga
 
---CREATE DIRECTORY export_dir as '\\farmacia-server\DesaNuevo\cargaris'; ---debe ser la ruta del servidor
---grant read,write on directory  export_dir to public;
 
---tambien se tiene que asignar el directorio en el init.ora de la BD
---alter system set utl_file_dir='export_dir' scope=spfile;
 
 
cursor paciente is
 
select pac.primer_apellido||' '||pac.segundo_apellido||'^'||pac.primer_nombre||' '||pac.segundo_nombre nombre,
decode(pac.pasaporte,null,pac.PROVINCIA||'-'||pac.TOMO||'-'||pac.ASIENTO,pasaporte) Cedula,
to_char(pac.fecha_nacimiento,'yyyymmdd') fecha_nacimiento,
pac.codigo,pac.residencia_direccion,pac.telefono,
pac.fecha_adiciona,pac.sexo
from adm_paciente pac
where to_char(pac.fecha_nacimiento,'dd-mm-yyyy') = v_fecha_nac
and pac.codigo = v_codigo_pac;
 
 
BEGIN
        
 
v_ADTfile := UTL_FILE.FOPEN('EXPORT_DIR','prueba.txt','W');
 
 
select sfclinico.sec_adt.nextval 
into v_secuencia 
from dual;
 
 
for reg in paciente loop
 
  v_valor := 'MSH|^~\&|LAVID||medora|'||to_char(sysdate,'yyyymmddhh24miss')||'|||ADT^A08|'||v_secuencia||'|P|2.3|||||';
  UTL_FILE.PUTF(v_ADTfile,v_format,v_VALOR||chr(10));
 
 
  v_valor := 'EVN|'||v_tipo||'|'||to_char(reg.fecha_adiciona,'yyyymmddhh24miss');
  UTL_FILE.PUTF(v_ADTfile,v_format,v_VALOR||chr(10));
 
 
  v_valor := 'PID|||'||reg.fecha_nacimiento||reg.codigo||'|'||reg.cedula||'|'||reg.nombre||'||'||reg.fecha_nacimiento||'|'||reg.sexo||'|||'||reg.residencia_direccion||'||'||reg.telefono;
  UTL_FILE.PUTF(v_ADTfile,v_format,v_VALOR||chr(10));
 
 
  IF v_TIPO = 'A08' THEN
	v_valor := 'PV1||'||'I'||'|||||||||||||||||'||reg.fecha_nacimiento||reg.codigo;
  ELSif v_tipo = 'A34' THEN
	v_valor := 'MRG|'||v_CODIGO_ANT;
  END IF;
 
 
  UTL_FILE.PUTF(v_ADTfile,v_format,v_VALOR||chr(10)||chr(13));
 
 
end loop;
 
UTL_FILE.FCLOSE(v_ADTfile);
 
 
END proc_export_file;

Open in new window

Try lowercase 'w' in the last parameter to FOPEN


v_ADTfile := UTL_FILE.FOPEN('EXPORT_DIR','prueba.txt','w');

Open in new window

Nevermind, Oracle docs say it is case-insensitive.

Could be due to permissions. Can the Oracle user access this directory? Have you tried outside of Oracle?
we are generating the file in a computer that is not the server.
Yes, but the Oracle EXPORT_DIR directory must be readable/writeable by the server. The PL/SQL runs on the server, not your local computer.
>> CREATE DIRECTORY export_dir as '\\farmacia-server\DesaNuevo\cargaris'; <-- has read and write access


Is that network path writeable from the Oracle database _server_ by the Oracle administrator user?
Yes the path is writeable for everybody,
The folder '\\farmacia-server\DesaNuevo\cargaris'  is shared (public)


Can you explain what exactly you meant by:

>>we are generating the file in a computer that is not the server.


Also, can you test with a non-network share, using a directory on the Oracle server and verify that works?
ok. we are running the script from a workstation, not directly from server.

For test, we execute the script directly from server and it works.
We read the txt file and still have the same problem from the begining: crh(10) still appears as ODOA.

For some reason, chr(10)  becomes 0A0D.

We need 0A (crh(10)) between lines and 0D (chr(13) at the end of file .




THIS IS FILE CONTENT:
MSH|^~\&|LAVID||medora|20090526164229|||ADT^A08|83|P|2.3|||||
EVN|A08|20050429000000
PID|||194608032|P20097465271|ABRIHALL ^CEFORD ERNESTO||19460803|M|||CIUDAD RADIAL, JUAN DIAZ, CASA # 320||220-3397
PV1||I|||||||||||||||||194608032
 
 
THIS IS HOW IS SEEN IN HEXAGECIMAL

Open in new window

OUTPUT.bmp
Use UTL_FILE.PUT_LINE
There are 2 method UTL_FILE.PUT to print the line on the same line and UTL_FILE.PUT_LINE to print the line and and line break
So I will recommend to use UTL_FILE.PUT
because the line separater is commanded by you programmatically.
Hi.
We replaced utl_file.put_line for utl_file.put (using chr(10) at the end of line)
   v_valor := 'EVN|'||v_tipo||'|'||to_char(reg.fecha_adiciona,'yyyymmddhh24miss');
    UTL_FILE.PUT(v_ADTfile,v_VALOR||chr(10));

Later, we  took off chr(10) and  added utl_file.new_line
   v_valor := 'EVN|'||v_tipo||'|'||to_char(reg.fecha_adiciona,'yyyymmddhh24miss');
   UTL_FILE.PUT(v_ADTfile,v_VALOR);
   UTL_FILE.NEW_LINE(v_ADTfile);

Both results were the same.
 
Hi,
We just found this post in another question with the same 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 "SFCLINICO.PROC_EXPORT_FILE", line 46
ORA-06512: at line 1
 "jinesh_kamdar:Wait a minute! Are you trying this on the same machine that is hosting the DB? UTL_FILE works only for server-side directories, not client-side!"
So we cannot use UTL_FILE package, because we need to generate the file from client-side.

What else can we try, we have tried oracle report with sql using chr (10), concatenating the records
rec1 || CHR(10) || rec2 || CHR(10)....
using loops, and we got 0D0A.

Using rawtohex(chr(10)) we got 0A, so we are sure that sql is recognizing chr(10) as 0A, we think maybe the problem is when is saved in a txt file, operating system (windows) changes it. Is there a way to keep as it is in sql?

thanks for the help!!!!
ASKER CERTIFIED SOLUTION
Avatar of schwertner
schwertner
Flag of Antarctica image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
We are using Oracle9i Release 9.2.0.6.0, we found that using PUT_RAW had a bug in this release. We have an Oracle 10 Release 10.2.0.3.0 test database and it worked. we have the txt file with the 0A between lines and 0D at the end as we needed.
But still we have the problem that we need to run this UTL_file from client side, and according to  a previous post it is confirmed it must be run only from server, is this true? Is there a way to use it from client side?


CREATE OR REPLACE PROCEDURE SFCLINICO.PROC_EXPORT_FILE 
(v_fecha_nac in varchar2, v_codigo_pac in  number,v_tipo in varchar2, v_codigo_ant in number) AS
        
 
    v_ADTfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'EXPORT_DIR';
    v_filename      VARCHAR2(20) := 'prueba.txt';
    v_format        VARCHAR2(2000);
    v_valor         VARCHAR2(2000);
    v_secuencia     number(10);
    
    v_raw           raw(32767);
 
 
cursor paciente is
 
select pac.primer_apellido||' '||pac.segundo_apellido||'^'||pac.primer_nombre||' '||pac.segundo_nombre nombre,
decode(pac.pasaporte,null,pac.PROVINCIA||'-'||pac.TOMO||'-'||pac.ASIENTO,pasaporte) Cedula,
to_char(pac.fecha_nacimiento,'yyyymmdd') fecha_nacimiento,
pac.codigo,pac.residencia_direccion,pac.telefono,
pac.fecha_adiciona,pac.sexo
from adm_paciente pac
where to_char(pac.fecha_nacimiento,'dd-mm-yyyy') = v_fecha_nac
and pac.codigo = v_codigo_pac;
 
 
BEGIN
        
 
v_ADTfile := UTL_FILE.FOPEN('EXPORT_DIR','prueba.txt','wb',32767);
 
select sfclinico.sec_adt.nextval 
into v_secuencia 
from dual;
 
 
for reg in paciente loop
 
 
  v_valor := 'MSH|^~\&|LAVID||medora|'||to_char(sysdate,'yyyymmddhh24miss')||'|||ADT^A08|'||v_secuencia||'|P|2.3|||||'||chr(10);
  v_raw := Utl_Raw.Cast_To_Raw(v_valor);
  UTL_FILE.PUT_raw(v_ADTfile,v_raw,false);
 
  v_valor := 'EVN|'||v_tipo||'|'||to_char(reg.fecha_adiciona,'yyyymmddhh24miss'||chr(10));
  v_raw := Utl_Raw.Cast_To_Raw(v_valor);
  UTL_FILE.PUT_raw(v_ADTfile,v_raw,false);
 
 
  v_valor := 'PID|||'||reg.fecha_nacimiento||reg.codigo||'|'||reg.cedula||'|'||reg.nombre||'||'||reg.fecha_nacimiento||'|'||reg.sexo||'|||'||reg.residencia_direccion||'||'||reg.telefono||chr(10);
  v_raw := Utl_Raw.Cast_To_Raw(v_valor);
  UTL_FILE.PUT_raw(v_ADTfile,v_raw,false);
 
 
  IF v_TIPO = 'A08' THEN
  v_valor := 'PV1||'||'I'||'|||||||||||||||||'||reg.fecha_nacimiento||reg.codigo||chr(10)||chr(13);
  ELSif v_tipo = 'A34' THEN
  v_valor := 'MRG|'||v_CODIGO_ANT||chr(10)||chr(13);
  END IF;
 
v_raw := Utl_Raw.Cast_To_Raw(v_valor);
UTL_FILE.PUT_raw(v_ADTfile,v_raw,false);
 
 
end loop;
 
UTL_FILE.FCLOSE(v_ADTfile);
 
END proc_export_file;

Open in new window

You cannot run PL/SQL on the client. Regardless of where you are connecting _from_, the PL/SQL runs inside the server, always.

You can spool putput to a client side file, but not write directly with PL/SQL. For that you could use plain dbms_output but then I do not know where you would be regarding the \n vs \r\n again.

How many clients do you have? Can you map the EXPORT_DIR above to a public, read-only directory for the client machines to connect to?

Sounds like possibly you also might need a web-based solution, or either save it to a CLOB table and let the client select it. You could also use PL/SQL apis to save / email the file to the client email address. Sort of kludgy though.
we can map the export_dir to a public directory.


with this instruction,  UTL_FILE.FOPEN('EXPORT_DIR','prueba.txt','wb')  how we can make it work in Oracle9i Release 9.2.0.6.0?

Thanks!!!!
 
Thanks for all your help, we are generating the files as we needed!!!!!