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.
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.
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!
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
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;
ASKER
We create directory EXPORT_DIR, with grant options.
CREATE DIRECTORY export_dir as '\\farmacia-server\DesaNue vo\cargari s'; <-- 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_FIL E", line 46
ORA-06512: at line 1
Is there something we are missing? Or is a permission problem??
Thanks for the help
CREATE DIRECTORY export_dir as '\\farmacia-server\DesaNue
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
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_FIL
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;
Try lowercase 'w' in the last parameter to FOPEN
v_ADTfile := UTL_FILE.FOPEN('EXPORT_DIR','prueba.txt','w');
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?
Could be due to permissions. Can the Oracle user access this directory? Have you tried outside of Oracle?
ASKER
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\DesaNue vo\cargari s'; <-- has read and write access
Is that network path writeable from the Oracle database _server_ by the Oracle administrator user?
Is that network path writeable from the Oracle database _server_ by the Oracle administrator user?
ASKER
Yes the path is writeable for everybody,
The folder '\\farmacia-server\DesaNue vo\cargari s' is shared (public)
The folder '\\farmacia-server\DesaNue
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?
>>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?
ASKER
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 .
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
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.
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.
ASKER
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_ch ar(reg.fec ha_adicion a,'yyyymmd dhh24miss' );
UTL_FILE.PUT(v_ADTfile,v_V ALOR||chr( 10));
Later, we took off chr(10) and added utl_file.new_line
v_valor := 'EVN|'||v_tipo||'|'||to_ch ar(reg.fec ha_adicion a,'yyyymmd dhh24miss' );
UTL_FILE.PUT(v_ADTfile,v_V ALOR);
UTL_FILE.NEW_LINE(v_ADTfil e);
Both results were the same.
We replaced utl_file.put_line for utl_file.put (using chr(10) at the end of line)
v_valor := 'EVN|'||v_tipo||'|'||to_ch
UTL_FILE.PUT(v_ADTfile,v_V
Later, we took off chr(10) and added utl_file.new_line
v_valor := 'EVN|'||v_tipo||'|'||to_ch
UTL_FILE.PUT(v_ADTfile,v_V
UTL_FILE.NEW_LINE(v_ADTfil
Both results were the same.
ASKER
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_FIL E", 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!!!!
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_FIL
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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;
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.
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.
ASKER
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!!!!
with this instruction, UTL_FILE.FOPEN('EXPORT_DIR
Thanks!!!!
ASKER
Thanks for all your help, we are generating the files as we needed!!!!!
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(
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.