| Error Reading A File [message #446129] |
Fri, 05 March 2010 09:18  |
wtolentino
Messages: 398
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 #446135 is a reply to message #446132] |
Fri, 05 March 2010 10:32  |
wtolentino
Messages: 398
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:
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 #446480 is a reply to message #446140] |
Mon, 08 March 2010 14:48 |
wtolentino
Messages: 398
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 #446627 is a reply to message #446485] |
Tue, 09 March 2010 12:38 |
wtolentino
Messages: 398
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 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
wtolentino wrote on Fri, 05 March 2010 10:18which 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 |
wtolentino
Messages: 398
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
