invalid file operation

[raagadeepthi]

Getting invalid file operation when using the below code.
Iam running this on client machine and have created one director by name 'DEEPS' and have given read/write privileges ran the below quries for setting path.

If i cannot run this porcedure on client machine. please help me for reading data from .txt file and updating into database with out using UTL packages.

Please help

[code=oracle]

create or replace procedure utl_file_test_r ead (

direc in varchar2,

filename in varchar2)

is

input_file utl_file.file_t ype;

input_buffer varchar2(4000);

begin

input_file := utl_file.fopen( direc ,filename, 'R');



loop

utl_file.get_li ne(input_file,i nput_buffer);

insert into SampleTable values (input_buffer);

c := c + 1;

end loop;


exception

when NO_DATA_FOUND then

utl_file.fclose ( input_file);

end;
[/code]

[raagadeepthi]

Can someone please suggest as i need to do this by EOD

[OraMaster]

Getting invalid file operation when using the below code.
Iam running this on client machine and have created one director by name 'DEEPS' and have given read/write privileges ran the below quries for setting path.

If i cannot run this porcedure on client machine. please help me for reading data from .txt file and updating into database with out using UTL packages.

Please help


create or replace procedure utl_file_test_r ead (

direc in varchar2,

filename in varchar2)

is

input_file utl_file.file_t ype;

input_buffer varchar2(4000);

begin

input_file := utl_file.fopen( direc ,filename, 'R');



loop

utl_file.get_li ne(input_file,i nput_buffer);

insert into SampleTable values (input_buffer);

c := c + 1;

end loop;


exception

when NO_DATA_FOUND then

utl_file.fclose ( input_file);

end;

Hi raagadeepthi,

Verify file and directory access privileges on the file system, and if reading, verify that the file exists.
Have you executed below command?
Let say ur trying to read a file from '/tmp' and ur passing 'ORADIR' in direc .
CONNECT / AS SYSDBA
CREATE OR REPLACE DIRECTORY ORADIR AS '/tmp';
GRANT read, write ON DIRECTORY ORADIR TO scott;

After doing this if you get error again then please check whether file does exists into /tmp or not and full access to Oracle user on that.

Kind Regds,
Bhushan

[amitpatel66]

Invalid File opertaion can be due to few reasons like:

1. file does not exist in the directory
2. you dont have read write access on the particular file

Can you please check on above two points and post back. Also please check that the Directory is in place and you have required privileges to use the DIRECTORY.

Note: The directory that I am talking above is logical directory that I am refering to that we need to create using CREATE DIRECTORY statement

[raagadeepthi]

I have created one directory 'DEEPS' using the below query:

create or replace directory DEEPS as 'D:\InputFile';

and given the privilages using belo query

GRANT READ,WRITE ON DIRECTORY DEEPS TO PUBLIC;

i kept the input file in 'D:\InputFile' but still iam getting this error.

Is there a way to read data from input file with out using UTL packges. If so please suggest me the alternate way.

[amitpatel66]

Can you please post the output of the below query for my reference:

[code=oracle]
select * from dba_directories
[/code]

and

[code=oracle]

select * from v$parameter where name = 'utl_file_dir'

[/code]

[raagadeepthi]

Below is the result:

select * from dba_directories ;


OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS MY_DIR1 D:/tmp
SYS MY_DIR D:\tmp
SYS DEEPS D:\InputFile
SYS DEEP D:\InputFile\sa mple1.txt
SYS EDR d:\
SYS UTL_DIR2 D:PubliceDR
SYS UTL_DIR D:\Public\eDR\u tl_dir
SYS PLP_UTL_DIR D:\Public\PLP
SYS MEDIA_DIR D:\oracle\produ ct\10.1.0\db_1\ demo\schema\pro duct_media\
SYS DATA_FILE_DIR D:\oracle\produ ct\10.1.0\db_1\ demo\schema\sal es_history\
SYS LOG_FILE_DIR D:\oracle\produ ct\10.1.0\db_1\ demo\schema\log \
SYS DM_PMML_DIR D:\oracle\produ ct\10.1.0\db_1\ dm\admin


select * from v$parameter where name = 'utl_file_dir'

NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABL E ISSYS_MODIFIABL E ISINSTANCE_MODI FIABLE ISMODIFIED ISADJUSTED ISDEPRECATED DESCRIPTION UPDATE_COMMENT HASH
692.00 utl_file_dir 2.00 D:\Public\eDR\u tl_dir D:\Public\eDR\u tl_dir FALSE FALSE FALSE FALSE FALSE FALSE FALSE utl_file accessible directories list 379,739,569.00

[amitpatel66]

Firstly, just add OTHERS exception in your procedure and recompile. Print the SQLERRM if any Exception is raised. Something like:

[code=oracle]

EXCEPTION
WHEN NO_DATA_FOUND THEN
......
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE(SQLERRM);

END;
/
[/code]


Then,

Do a test by following the below steps:

1. Place the file sample1.txt in the path "D:\Public\eDR\ utl_dir"
2. Pass the input parameter to the procedure as below:
dir name: UTL_DIR
filename: sample1.txt

Post back the outcome of your test

[raagadeepthi]

i think D:\Public\eDR\u tl_dir is server directory.
I cannot place the file in that location.

[OraMaster]

i think D:\Public\eDR\u tl_dir is server directory.
I cannot place the file in that location.

Hi raagadeepthi

So where u had placed the file earlier?
Your file must be located in one of the server directory.

create or replace directory DEEPS as 'D:\TEMP'

By using above command you are just creating a reference DEEPS for the server directory D:\TEMP.

Kind Regds,
Bhushan

[raagadeepthi]

but now when i execute the below lines procedure is successfull

create or replace procedure utl_file_test_r ead (
direc in varchar2,
filename in varchar2)

is
input_file utl_file.file_t ype;
input_buffer varchar2(4000);
c NUMBER := 0;
begin
input_file := utl_file.fopen( direc ,filename, 'R');

loop
utl_file.get_li ne(input_file,i nput_buffer);
insert into SampleTable values (input_buffer);
c := c + 1;
end loop;

exception
when NO_DATA_FOUND then
utl_file.fclose ( input_file);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE(SQLERRM);
end;


Procedure created.

SQL> exec utl_file_test_r ead('DEEPS','sa mple1.txt');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from SampleTable;

no rows selected

but no rows are select to the table.

I have place the input file in local directory. Iam not sure how to place the file in server directory.

[OraMaster]

but now when i execute the below lines procedure is successfull

create or replace procedure utl_file_test_r ead (
direc in varchar2,
filename in varchar2)

is
input_file utl_file.file_t ype;
input_buffer varchar2(4000);
c NUMBER := 0;
begin
input_file := utl_file.fopen( direc ,filename, 'R');

loop
utl_file.get_li ne(input_file,i nput_buffer);
insert into SampleTable values (input_buffer);
c := c + 1;
end loop;

exception
when NO_DATA_FOUND then
utl_file.fclose ( input_file);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT _LINE(SQLERRM);
end;


Procedure created.

SQL> exec utl_file_test_r ead('DEEPS','sa mple1.txt');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from SampleTable;

no rows selected

but no rows are select to the table.

I have place the input file in local directory. Iam not sure how to place the file in server directory.

Hi
I don't know how your procedure got executed successfully.
But the result you wanted u didn't get by doing this.
Please answer below questions
1. Which operating system is on Oracle database installed machine?
2. Do you have access on filesystem where Oracle database is installed?

Kind Regds,
Bhushan

[raagadeepthi]

I dont have access to filesystem where Oracle database is installed.

Can i read the input from file without using UTL packages?

[amitpatel66]

Before executing your procedure, try this:

[code=oracle]

SET SERVEROUTPUT ON

[/code]

This will show you the real picture. It will throw invalidFileOper ation Error

Here is the catch. I was trying to make you understand that the physical directory is present in the Server and you got that right. You should place your file in the Server Directory and then execute the same. You cannot use the local machine directory the way you are trying to do. The Oracle DB will look for Physical Sirectory D:\InputFile in the Server.

[raagadeepthi]

I got that. But iam not sure where and how exactly i have to place the file in the server directory.

More over iam not sure if i have privileges to do so.