Home » SQL & PL/SQL » SQL & PL/SQL » Error Reading A File (Oracle 10.2.0.4.0, AIX Version 5)
Error Reading A File [message #446129] Fri, 05 March 2010 09:18 Go to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
i have a procedure that reads a file from a server. this works in all tiers except for the production, which when we recently migrated the procedure in production and run it from there it appears to be failing. because i do not have access to production tier i have asked our database administrator to run the debug script to diagnose the issue.


set servertoutput on;
declare
  pFileName    varchar2(80) := 'marchiselli.txt';
  vSFile       utl_file.file_type; -- source file
  vNewLine     VARCHAR2(200);      -- line of character strings
  vLineCtr     number := 0;        -- line counter
begin
  vSFile := utl_file.fopen('DIR_MRCH_IN', pFileName,'r');
  if utl_file.is_open(vSFile) then
    dbms_output.put_line('read file '||pFileName||' success');
  end if;
  utl_file.fclose(vSFile);
exception
  when utl_file.invalid_mode then
    RAISE_APPLICATION_ERROR (-20051,'Invalid Mode Parameter');
  when utl_file.invalid_path then
    RAISE_APPLICATION_ERROR (-20052,'Invalid File Location');
  when utl_file.invalid_filehandle then
    RAISE_APPLICATION_ERROR (-20053,'Invalid Filehandle');
  when utl_file.invalid_operation then
    RAISE_APPLICATION_ERROR (-20054,'Invalid Operation');
  when utl_file.read_error then
    RAISE_APPLICATION_ERROR (-20055,'Read Error');
  when utl_file.internal_error then
    RAISE_APPLICATION_ERROR (-20057,'Internal Error');
  when utl_file.charsetmismatch then
    RAISE_APPLICATION_ERROR (-20058,'Opened With FOPEN_NCHAR But Later I/O Inconsistent');
  when utl_file.file_open then
    RAISE_APPLICATION_ERROR (-20059,'File Already Opened');
  when utl_file.invalid_maxlinesize then
    RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
  when utl_file.invalid_filename then
    RAISE_APPLICATION_ERROR (-20061,'Invalid File Name');
  when utl_file.access_denied then
    RAISE_APPLICATION_ERROR (-20062,'File Access Denied By');
  when utl_file.invalid_offset then
    RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
  when others then
    --RAISE_APPLICATION_ERROR(-20099, 'Unknown UTL_FILE Error');
    raise_application_error (-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end;
/


when the code above is run from the development and test tier it was success:
read file marchiselli.txt success

PL/SQL procedure successfully completed.


however in production tier it failed:
declare
*
ERROR at line 1:
ORA-20054: Invalid Operation
ORA-06512: at line 20


our database administrator had check and the database account has the read and write directories in all tiers. the file marchiselli.txt is also existing and had the same access mode in all tiers:
axmo10:/u02/app/mrch/prd/in # ls -l
total 1544
-rw-rw-rw-    1 mrchftpd mrchgrp      785174 Mar  4 12:52 marchiselli.txt


what could possibly we had missed on production server?

thanks,
warren
Re: Error Reading A File [message #446132 is a reply to message #446129] Fri, 05 March 2010 09:29 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+utl_file.invalid_operation
Re: Error Reading A File [message #446135 is a reply to message #446132] Fri, 05 March 2010 10:32 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
that's basically where i patterned the code however there is something our server administrator or database administrator had missed to put on production that causes this error:
File Access Denied By


they can't seem to see what they have missed and i was hoping that i could find some solution in this posting.
Re: Error Reading A File [message #446140 is a reply to message #446129] Fri, 05 March 2010 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
run following SQL on Production server

select DIRECTORY_PATH from dba_directories where directory_name = 'DIR_MRCH_IN';

post results here
Re: Error Reading A File [message #446480 is a reply to message #446140] Mon, 08 March 2010 14:48 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
ORP2> select * from all_directories where directory_name = 'DIR_MRCH_IN';
 
OWNER       DIRECTORY_NAME                 DIRECTORY_PATH
----------- ------------------------------ ---------------------------------------------
SYS         DIR_MRCH_IN                    /u02/app/mrch/prd/in
 
1 row selected.
 
ORP2> show user
USER is "MRCH_BATCH"
Re: Error Reading A File [message #446481 is a reply to message #446480] Mon, 08 March 2010 15:02 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
OWNER='SYS'

Should NOT be using SYS schema for anything; including application support or functionality
Re: Error Reading A File [message #446482 is a reply to message #446481] Mon, 08 March 2010 15:09 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Actually, directories are always owned by SYS.
Re: Error Reading A File [message #446483 is a reply to message #446482] Mon, 08 March 2010 15:10 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
ThomasG wrote on Mon, 08 March 2010 13:09
Actually, directories are always owned by SYS.


Needless to say, I did not know that & now I do.

THANKS!
Re: Error Reading A File [message #446484 is a reply to message #446481] Mon, 08 March 2010 15:11 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
in other tiers it is also owned by SYS and that was because the directory is created by a database administrator account.

[Updated on: Mon, 08 March 2010 15:12]

Report message to a moderator

Re: Error Reading A File [message #446485 is a reply to message #446480] Mon, 08 March 2010 15:13 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
ls -ld /u02/app/mrch/prd/in
ls -ld /u02/app/mrch/prd/
ls -ld /u02/app/mrch/
ls -ld /u02/app/
ls -ld /u02/

post results from commands above

Re: Error Reading A File [message #446486 is a reply to message #446485] Mon, 08 March 2010 15:38 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also: Has the DBA perhaps missed to run the

GRANT READ ON DIRECTORY DIR_MRCH_IN TO MRCH_BATCH;


?
Re: Error Reading A File [message #446626 is a reply to message #446486] Tue, 09 March 2010 12:35 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member

yes there is grants.
Re: Error Reading A File [message #446627 is a reply to message #446485] Tue, 09 March 2010 12:38 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
$ ls -lad /u02/app/mrch/prd/in
drwxrwxr-x    2 oracle   mrchgrp         256 Mar 09 01:00 /u02/app/mrch/prd/in
$ ls -lad /u02/app/mrch/prd
drwxrwxr-x    4 oracle   mrchgrp         256 Apr 01 2009  /u02/app/mrch/prd
$ ls -ld /u02/app/mrch
drwxrwxr-x    4 oracle   mrchgrp         256 Dec 07 11:10 /u02/app/mrch
$ ls -ld /u02/app
drwxrwxr-x   36 oracle   oracle         4096 Aug 20 2009  /u02/app
$ ls -ld /u02
drwxr-xr-x    4 oracle   oracle         4096 Jun 05 2009  /u02
$
Re: Error Reading A File [message #446629 is a reply to message #446129] Tue, 09 March 2010 13:03 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
wtolentino wrote on Fri, 05 March 2010 10:18
which when we recently migrated the procedure in production and run it from there it appears to be failing.


Couple of dumb questions.

1. What does "migrate the procedure in production?
2. Directories must be one server, not client. You said you do not have access to production yet you provide a directory list. How?
3. How do you know it was run as MRCH_BATCH in production by your DBAs?
4. How do you know the grants were given to MRCH_BATCH in production. Just because you say so or your DBAs told you is not proof enough to me.
Re: Error Reading A File [message #446644 is a reply to message #446629] Tue, 09 March 2010 15:35 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
1. we had an internal migration form that we fill-up and submit to our change process group then when approved it will be forwarded to our appropriate group who will handled the migration. in this case our server group and database administrator group.
2. i do not have access to production and the list that i have provide was that i asked our server administrator to provide it to me.
3. they do SQL> show user;
4. they sent me this:
ORP2> select * from all_directories where directory_name = 'DIR_MRCH_IN';
 
OWNER       DIRECTORY_NAME                 DIRECTORY_PATH
----------- ------------------------------ ---------------------------------------------
SYS         DIR_MRCH_IN                    /u02/app/mrch/prd/in
 
1 row selected.
 
ORP2> show user
USER is "MRCH_BATCH"


thanks.
Re: Error Reading A File [message #446822 is a reply to message #446129] Wed, 10 March 2010 10:59 Go to previous message
wtolentino
Messages: 196
Registered: March 2005
Senior Member

it is resolved now. our database group missed to give the other MRCH schema which owns the objects being used in the procedure and the procedure itself.
Previous Topic: Materialize View
Next Topic: SQL Count and Sort?
Goto Forum:
  


Current Time: Tue Sep 27 11:06:11 CDT 2016

Total time taken to generate the page: 0.12667 seconds