Re: PL/SQL (utl_file) problem...

Assuming the table is created correctly, check to insure that the path you specify for the input/output files
is contained in the initsid.ora file. There needs to be a 'utl_file_dir =' entry indicating 'C:\' as a path, and the path must be where oracle can find it (i.e. not on a network it does not have access to).

ashesh48_at_my-dejanews.com wrote:

> Hi everyone,
>
> I got in to trouble with the UTL_FILE package/// on oracle
> v7.3x
> the procedure is loks like as follwos.. it just take the file and
> insert the data in to the another table....
>
> create or replace procedure LS
> ( v_dir in varchar2, v_filename in varchar2)
> as
> --declaration
> v_filehandle UTL_FILE.FILE_TYPE ;
> --v_outfile
> utl_file.file_type ;
> v_newline varchar2(100) ;
> v_acctnum
> cfe.account.acct_number%TYPE ;
> v_internal
> cfe.account.acct_internal%TYPE ;
> v_tab1 number ;
> v_tab2
> number ;
> v_groupid varchar2(4) ;
> --v_ac cfe.account.acct_number%TYPE ;
>
> begin
> -- Open the file for reading...
> v_filehandle := UTL_FILE.FOPEN(v_dir,
> v_filename, 'r' ) ;
> -- v_outfile := utl_file.fopen(v_dir,outputfile.dat,'w'
> ); Open the file to write...
>
> --Loop over the file, reading in each
> line...
> loop
> begin
> utl_file.get_line(v_filehandle , v_newline) ;
>
> exception
> when no_data_found then
> exit ;
> end ;
> v_tab1
> := instr(v_newline , ' ', 1 , 1) ;
> v_tab2 := instr(v_newline , ' ', 1, 2 ) ;
> -- 2 is for 2nd occurance of ' '....
>
> v_acctnum :=
> substr(v_newline,1,v_tab1-1) ;
> v_internal :=
> substr(v_newline,v_tab1+1,v_tab2-1);
>
> insert into account_check (select
> v_acctnum , nvl(ga.group_id,'NULL')
> from
> group_to_account ga
> where ga.acct_number =
> v_acctnum ) ;
>
> end loop ;
> utl_file.fclose (v_filehandle ) ; -- Close
> the input file...
>
> /*
> ***********
> -- Create a cursor for the output file...
>
> declare cursor account_check_cur
> is select * from account_check ;
>
> open
> account_check_cur ;
> loop
>
> fetch account_check_cur into %v_ac,
> %v_groupid ;
> exit when account_check_cur%notfound ;
>
> utl_file.put_line(v_outfile , 'v_ac') ;
>
> end loop ;
> close
> account_check_cur ;
> fclose (v_outfile ) ;
> ************
> */
>
> exception
> when
> utl_file.INVALID_OPERATION THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20000,'invalid operation') ;
>
> when
> utl_file.INVALID_FILEHANDLE THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20001,'invalid FILE HANDLE..') ;
>
> when
> utl_file.READ_ERROR THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20002,'Reading Error..') ;
>
> when
> utl_file.INVALID_PATH THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20003,'INVALID PATH ERR..') ;
>
> when
> utl_file.INVALID_MODE THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20004,'INVALID MODE ERR..') ;
>
> when
> utl_file.INTERNAL_ERROR THEN
> UTL_FILE.FCLOSE (v_filehandle) ;
>
> raise_application_error (-20005,'INTERNAL ERR..') ;
>
> WHEN OTHERS THEN
>
> UTL_FILE.FCLOSE (v_filehandle) ;
> RAISE ;
>
> end;
>
> IT GIVES Me the following
> errors.... eventhough i have a dir and file and table created already.....
>
> SQL> execute ( 'c:\input_files' , 'internal.dat' ) ;
> begin ( 'c:\input_files'
> , 'internal.dat' ) ; end;
>
> *
> ERROR at line 1:
> ORA-20003: INVALID PATH ERR..
> ORA-06512: at "CFE.LS", line 73
> ORA-06512: at line 1
>
> any feedback would be
> gratly appricated...
>
> Thank you in advance...
> Ashesh
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 30 1998 - 16:18:45 CST