469,311 Members | 2,482 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,311 developers. It's quick & easy.

invalid file operation

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. create or replace procedure utl_file_test_read (
  3.  
  4.   direc       in varchar2,
  5.  
  6.   filename   in varchar2)
  7.  
  8. is
  9.  
  10.   input_file   utl_file.file_type;
  11.  
  12.   input_buffer varchar2(4000);
  13.  
  14. begin
  15.  
  16.   input_file := utl_file.fopen(direc ,filename, 'R');
  17.  
  18.  
  19.  
  20.   loop
  21.  
  22.         utl_file.get_line(input_file,input_buffer);
  23.  
  24.         insert into SampleTable values (input_buffer);
  25.  
  26.         c := c + 1;
  27.  
  28.     end loop;
  29.  
  30.  
  31.  exception
  32.  
  33.     when NO_DATA_FOUND then
  34.  
  35.         utl_file.fclose( input_file);
  36.  
  37. end;
  38.  
Sep 23 '09 #1
21 5163
Can someone please suggest as i need to do this by EOD
Sep 23 '09 #2
OraMaster
135 100+
@raagadeepthi
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
Sep 23 '09 #3
amitpatel66
2,367 Expert 2GB
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
Sep 23 '09 #4
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.
Sep 23 '09 #5
amitpatel66
2,367 Expert 2GB
Can you please post the output of the below query for my reference:

Expand|Select|Wrap|Line Numbers
  1. select * from dba_directories
  2.  
and

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from v$parameter where name = 'utl_file_dir'
  3.  
  4.  
Sep 23 '09 #6
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\sample1.txt
SYS EDR d:\
SYS UTL_DIR2 D:PubliceDR
SYS UTL_DIR D:\Public\eDR\utl_dir
SYS PLP_UTL_DIR D:\Public\PLP
SYS MEDIA_DIR D:\oracle\product\10.1.0\db_1\demo\schema\product_ media\
SYS DATA_FILE_DIR D:\oracle\product\10.1.0\db_1\demo\schema\sales_hi story\
SYS LOG_FILE_DIR D:\oracle\product\10.1.0\db_1\demo\schema\log\
SYS DM_PMML_DIR D:\oracle\product\10.1.0\db_1\dm\admin


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

NUM NAME TYPE VALUE DISPLAY_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISINSTANCE_MODIFIABLE ISMODIFIED ISADJUSTED ISDEPRECATED DESCRIPTION UPDATE_COMMENT HASH
692.00 utl_file_dir 2.00 D:\Public\eDR\utl_dir D:\Public\eDR\utl_dir FALSE FALSE FALSE FALSE FALSE FALSE FALSE utl_file accessible directories list 379,739,569.00
Sep 23 '09 #7
amitpatel66
2,367 Expert 2GB
Firstly, just add OTHERS exception in your procedure and recompile. Print the SQLERRM if any Exception is raised. Something like:

Expand|Select|Wrap|Line Numbers
  1.  
  2. EXCEPTION
  3. WHEN NO_DATA_FOUND THEN
  4. ......
  5. WHEN OTHERS THEN
  6. DBMS_OUTPUT.PUT_LINE(SQLERRM);
  7.  
  8. END;
  9. /
  10.  

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
Sep 23 '09 #8
i think D:\Public\eDR\utl_dir is server directory.
I cannot place the file in that location.
Sep 23 '09 #9
OraMaster
135 100+
@raagadeepthi
Hi raagadeepthi

So where u had placed the file earlier?
Your file must be located in one of the server directory.
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 23 '09 #10
but now when i execute the below lines procedure is successfull

create or replace procedure utl_file_test_read (
direc in varchar2,
filename in varchar2)

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

loop
utl_file.get_line(input_file,input_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_read('DEEPS','sample1.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.
Sep 23 '09 #11
OraMaster
135 100+
@raagadeepthi
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
Sep 23 '09 #12
I dont have access to filesystem where Oracle database is installed.

Can i read the input from file without using UTL packages?
Sep 23 '09 #13
amitpatel66
2,367 Expert 2GB
Before executing your procedure, try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SET SERVEROUTPUT ON
  3.  
  4.  
This will show you the real picture. It will throw invalidFileOperation 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.
Sep 23 '09 #14
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.
Sep 23 '09 #15
OraMaster
135 100+
@raagadeepthi
Hi raagadeepthi

It's not very difficult to put a file on server directory. You can take help from your DBA's for this. If this is also not possible then leave it.
Another way to load data into an Oracle table is using SQL Loader. For this you need not to have a file located on server :)

Happy coding.
Let me know if this solves your problem

Kind Regds,
Bhushan
Sep 23 '09 #16
amitpatel66
2,367 Expert 2GB
You can use command line FTP to ftp the file to the server. Something like:

ftp <server>
User: <username>
331 Password required for server.
Password: <password>
230 User <username> logged in.
ftp> lcd d:\
ftp> cd D:\Public\eDR\utl_dir
ftp> put sample1.txt
ftp> bye
Sep 23 '09 #17
Any idea on how to read data from input file normally without UTL pakages as iam new to oarcle.

I know how to load using Loader and toad but as i need to provide DBA guys a script i need to do this.

Please help.
Sep 23 '09 #18
OraMaster
135 100+
@raagadeepthi
Hi raagadeepthi

Please decide on where you are going to read a file from? If your file is on server then external table is one way to get the file data into an Oracle table. You can provide external table script to your DBA's but again you need to put your file on Oracle Server. Other than this I don't think altenative way to achieve this.
Best of LUCKS!!!

Kind Regds,
Bhushan
Sep 23 '09 #19
I need to read the data from file stored in local machine and insert data into table.
Sep 23 '09 #20
OraMaster
135 100+
@raagadeepthi
Hi raagadeepthi

The Oracle DBMS can only read data from files on the same server
via UTL_FILE or external tables. You can "push" the data from a
client machine into Oracle using SQL*LOADER (which as a
client-side tool), or FTP the files from your local machine to
the data server where you can use UTL_FILE or external tables.

Otherwise if it is necessary, you can design a form (client server- 6i) that can be load file in a clob in database and a storage procedure to load data from clob
into database tables,or if your client is web like design a form in 9i or 10g version that can be load file in a clob in database and a storage procedure to load data from clob into database tables, in this case you mast use webutil package, or Use HTMLDB for load file from client in a clob in database and a storage procedure to load data from clob into database tables or
make a loader script on client and run it in a form with host in
client-server 6i version or webutil_host in Forms 9i or 10g.

BOL!!!

Kind Regds,
Bhushan
Sep 23 '09 #21
amitpatel66
2,367 Expert 2GB
Oracle Database Server is installed in another machine. So the UTL_FILE will be able to operate on Physical directories that are in that particular machine and not in client local machine. You connect to database using Client Tool SQL Plus.

Either FTP the file as I said using Command Line if you have Server details
Else ask the Server Admin to Place the file in the Server and then you can execute your procedure to upload the data.
Sep 23 '09 #22

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by Allin Cottrell | last post: by
reply views Thread by suresh | last post: by
reply views Thread by John Jenkins | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.