invalid file operation | Newbie | | Join Date: Sep 2009
Posts: 19
| |
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_read (
-
-
direc in varchar2,
-
-
filename in varchar2)
-
-
is
-
-
input_file utl_file.file_type;
-
-
input_buffer varchar2(4000);
-
-
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);
-
-
end;
-
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
Can someone please suggest as i need to do this by EOD
|  | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by 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
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);
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);
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
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
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.
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
Can you please post the output of the below query for my reference: -
select * from dba_directories
-
and -
-
select * from v$parameter where name = 'utl_file_dir'
-
-
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
Firstly, just add OTHERS exception in your procedure and recompile. Print the SQLERRM if any Exception is raised. Something like: -
-
EXCEPTION
-
WHEN NO_DATA_FOUND THEN
-
......
-
WHEN OTHERS THEN
-
DBMS_OUTPUT.PUT_LINE(SQLERRM);
-
-
END;
-
/
-
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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
i think D:\Public\eDR\utl_dir is server directory.
I cannot place the file in that location.
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by raagadeepthi i think D:\Public\eDR\utl_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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
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.
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by raagadeepthi 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. 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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
I dont have access to filesystem where Oracle database is installed.
Can i read the input from file without using UTL packages?
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
Before executing your procedure, try this:
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.
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
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.
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by 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. 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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
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.
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by raagadeepthi 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. 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
| | Newbie | | Join Date: Sep 2009
Posts: 19
| | | re: invalid file operation
I need to read the data from file stored in local machine and insert data into table.
| | Member | | Join Date: Aug 2009 Location: Pune, India
Posts: 76
| | | re: invalid file operation
Quote:
Originally Posted by raagadeepthi I need to read the data from file stored in local machine and insert data into table. 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
| | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: invalid file operation
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.
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,414 network members.
|
