USING UTL_FILE not working

[mgpr]

got a problem with a procedure listen below.
there compalition will be without errors, but after i will execute it, it will just run into exception mode 'Fehler3'

Create table na_import (NETZ varchar2(100),
IO2 varchar2(100),
IO3 varchar2(100),
IO4 varchar2(100),
IO5 varchar2(100),
IO6 varchar2(100),
IO7 varchar2(100),
IO8 varchar2(100),
IO9 varchar2(100),
IO10 varchar2(100),
IO11 varchar2(100),
IO12 varchar2(100),
IO13 varchar2(100),
IO14 varchar2(100),
IO15 varchar2(100),
IO16 varchar2(100),
IO17 varchar2(100)) ;


create or replace procedure na_import_proc is
type array1 is varray(17) of varchar2(100);
feld array1;
datensatz varchar2(2000);
datensatz_orig varchar2(2000);
pos number;
count_good number := 0;
count_bad number := 0;
count_all number := 0;
in_file UTL_file.file_type;

loaddate date := to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD'); /* loaddate */

BEGIN

in_file := UTL_FILE.FOPEN(‘\\pcID75\Temp’, ‘OraDataLoad.txt’,’r’);

UTL_FILE.GET_LINE(in_file,datensatz);

count_all := 1;
datensatz_orig := datensatz;

loop
feld := array1(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

for i in 1..17 loop
if datensatz is null then
feld(i) := null;
else
pos := instr(datensatz,';');
if pos = 0 then
feld(i) := substr(datensatz,1,100);
else
feld(i) := substr(substr(datensatz,1,pos-1),1,100);
datensatz := substr(datensatz,pos+1,2000);
end if;
end if;
end loop;

begin

insert into trvp.na_import (
NETZ,
IO2,
IO3,
IO4,
IO5,
IO6,
IO7,
IO8,
IO9,
IO10,
IO11,
IO12,
IO13,
IO14,
IO15,
IO16,
IO17)
values (feld(1),
feld(2),
feld(3),
feld(4),
feld(5),
feld(6),
feld(7),
feld(8),
feld(9),
feld(10),
feld(11),
feld(12),
feld(13),
feld(14),
feld(15),
feld(16),
feld(17) );
/* OK */
count_good := count_good + 1;
insert into trvp.na_import (NETZ) values (’OK’);

exception
when others then
/* FEHLER 1 */
count_bad := count_bad + 1;
insert into trvp.na_import (NETZ) values (’Fehler1’);

end;

UTL_FILE.GET_LINE(in_file,datensatz);
count_all := count_all + 1;
datensatz_orig := datensatz;
end loop;

UTL_FILE.Fclose (in_file);

EXCEPTION
when no_data_found then
/* FEHLER 2 */
insert into trvp.na_import (NETZ) values (’Fehler2’);


UTL_FILE.Fclose (in_file);
when others then
/* FEHLER 3 */
insert into trvp.na_import (NETZ) values (’Fehler3’);

UTL_FILE.Fclose (in_file);
END;
/

 

[lewisp]

How are you running this script? Are you using SqlPlus on a client PC or on a unix server? Make sure you can write to \\pcID75\Temp from the box you are on.

Try removing the WHEN OTHERS exception and see what Oracle error its returning.

Maybe you need to add an extra backslash in the directory of the FOPEN command, like '\\pcID75\Temp\'

 

[mgpr]

i did copy this line and forgot to correct it right, should be: '\\pcID75\e$\temp' and there should be np to access to this folder.
But without exception i will got following errors:

SQL> execute na_import_proc;
BEGIN na_import_proc; END;

*
FEHLER in Zeile 1:
ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Ausnahmebedingung
(exception)
ORA-06512: in "SYS.UTL_FILE", Zeile 98
ORA-06512: in "SYS.UTL_FILE", Zeile 157
ORA-06512: in "TRVP.NA_IMPORT_PROC", Zeile 16
ORA-06512: in Zeile 1

 

[stefanhei]

Hi.
Does your utl_file_dir - parameter in your init.ora include the directory you try to write to?

Stefan

 

[stefanhei]

Hi.
Does your utl_file_dir - parameter in your init.ora include the directory you try to write to?

Stefan

 

[mgpr]

Yeah, you was right, path was invalid:
ORA-20000: INVALID_PATH exception attempting to open "\\PCID75\Temp"
ORA-06512: at "TRVP.NA_IMPORT_PROC", Line 20
ORA-06512: at Line 1

so, you got any idea i could put this file from a blob(table) down to filesystem at unix-server, so i could get them from there with my application?
or perhaps you got an other idea i could get text data from a blob into a table(extractet to 17 fields)?
i tried to drop down file to file system and load them in into a table, but when there is any other methode i could use or you know please tell me.
ps: i cannt use java, because we didnt install them on server.