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

 
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;
 

[raagadeepthi]

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

[OraMaster]

@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

[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:

select * from dba_directories
 

and

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

[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\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

[amitpatel66]

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

[raagadeepthi]

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

[OraMaster]

@raagadeepthi
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_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.

[OraMaster]

@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

[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:

 
SET SERVEROUTPUT ON
 
 

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.

[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.

[OraMaster]

@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

[amitpatel66]

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

[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.

[OraMaster]

@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

[raagadeepthi]

I need to read the data from file stored in local machine and insert data into table.

[OraMaster]

@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

[amitpatel66]

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.