Home » SQL & PL/SQL » SQL & PL/SQL » Utl_file package
Utl_file package [message #37953] Fri, 08 March 2002 07:18 Go to next message
Doris
Messages: 7
Registered: March 2002
Junior Member
hi, i've got a package that doesn't work properly, this is the structure:
CREATE OR REPLACE PACKAGE BODY PK_MIGRA_PRU IS
PROCEDURE PROC_MIGRACION IS
fid UTL_FILE.FILE_TYPE;
v VARCHAR2(200);
v_cod varchar2(10);
v_nombre varchar2(40);
reg_datos migra_pru%rowtype;
PROCEDURE recNgo (str IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Error en esta linea ' || str);
-- UTL_FILE.FCLOSE (fid);
END;

BEGIN
fid := UTL_FILE.FOPEN ('d:fvmigracion', 'acteco.txt', 'R');
loop
begin
UTL_FILE.GET_LINE (fid,v);
reg_datos.cod := substr(v,1,3);
reg_datos.nombre := substr(v,5,33);

insert into migra_pru values(reg_datos.cod,reg_datos.nombre);
end;
end loop;

EXCEPTION
WHEN UTL_FILE.INVALID_PATH
THEN recNgo ('Directorio Invalido');
WHEN UTL_FILE.INVALID_MODE
THEN recNgo ('Modo Invalido');
WHEN UTL_FILE.INVALID_FILEHANDLE
THEN recNgo ('invalid_filehandle');
WHEN UTL_FILE.INVALID_OPERATION
THEN recNgo ('Operacion Invalida');
WHEN UTL_FILE.READ_ERROR
THEN recNgo ('Error de Lectura');
WHEN UTL_FILE.WRITE_ERROR
THEN recNgo ('Error de escritura');
WHEN UTL_FILE.INTERNAL_ERROR
THEN recNgo ('Error Interno');
END;

END PK_MIGRA_PRU;
/**********************/
but returns the error:
ERROR en línea 1:
ORA-01403: no se han encontrado datos
ORA-06512: en "SYS.UTL_FILE", línea 105
ORA-06512: en "SYS.UTL_FILE", línea 255
ORA-06512: en "SYSTEM.PK_MIGRA_PRU", línea 18
ORA-06512: en línea 2*/
Re: Utl_file package [message #37954 is a reply to message #37953] Fri, 08 March 2002 07:30 Go to previous messageGo to next message
Doris
Messages: 7
Registered: March 2002
Junior Member
the problema is that i've got a txt file with 50 diferent records, if i put the utl_file.fopen inside the loop, it works, but inserts 50 rows into my table with the same record, i din't put a primary key restriccion to may table, just to tray how it works, but i'm still trying any posibility to make it work properly...
please, if anybody knows, how can i try with this..let me know...

thanks..
Re: Utl_file package [message #37956 is a reply to message #37954] Fri, 08 March 2002 09:42 Go to previous message
Doris
Messages: 7
Registered: March 2002
Junior Member
thank you so much!!, the package works perfectly!...thank you for helping me...i started working with oracle a few time ago, and i'll need much help from all you guys...

sorry, my english is not so good
thanks again :-)
Previous Topic: Sql
Next Topic: PL/SQL Function
Goto Forum:
  


Current Time: Wed Oct 27 06:03:04 CDT 2021