Problem of executing DBMS_LOB and UTL_FILE

[Michael]

Recently, I have created a stored procedure that utilizes dbms_lob
package to load a file on server into CLOB in a table. I can execute
the procedure on the server from slq*plus. But, when I call the
procedure on the client program,like Forms60, client side sql*plus or
TOAD, I got an error and it is related to permission on fileopen
inside dbms_lob package. I used the same database user for both server
and client side. I have tried to use both file locations that are in
utl_file_dir path and DIRECTORY created by DB user. The file access on
UNIX is set to 755 or 777. In fact, the user has DBA privilege.

The same problem happened when I use UTL_FILE pacakge. I can execute
stored procedure on server using UTL_FILE package to create a log
file, but the procedure cannot be called from client side. I know I
cannot call DBMS_LOB and UTL_FILE package directly from client, but
this procedure is on server. Can anyone give me some help and tell me
what cause the problem or what I have missed? What do I need to do so
that I can call it from client side program?

And, similar problem happens to java stored procedure too. I created a
java program to execute OS command and then create pl/sql procedure
for the java program. All file permission reauired is granted for
running the java program. And, I can execute from server side
sql*plus, but not from any client side program call.

Thanks

mike

[Mark]

Interesting problem. I'd expect it to work, have done similar things in the past.

Could you paste the exact error code and message?

M

wu...@hotmail.com (Michael) wrote in message news:<d08d4e18.02050...@posting.google.com>...

[Michael]

Thanks for reply.

if I execeute the stored procedure itself from HPUX server side
sql*plus,
exec load_sqlldr_log_file('XML_LOGFILE','xml_file_loader_200205091025.log',200205091025);

it shows: PL/SQL procedure successfully completed.

however, when I run the same program from client sql*plus or TOAD,
exec load_sqlldr_log_file('XML_LOGFILE','xml_file_loader_200205091025.log',200205091025);

I got this:

begin load_sqlldr_log_file('XML_LOGFILE','xml_file_loader_200205091025.log',200205091025);
end;

*
ERROR at line 1:
ORA-20010: error in populating sqlldr file log information into
table.ORA-22288: file or LOB
operation FILEOPEN failed
Permission denied
ORA-06512: at "G001SCHEMA.LOAD_SQLLDR_LOG_FILE", line 61
ORA-06512: at line 1

Here, XML_LOGFILE, is the directory name for the file. Directory read
privilege is granted. The user has DBA role privilege. Read and write
privileges have been granted to any OS user on the file.

The error from Oracle is ORA-22288 and 6512.

I also created another stored procedure to call the above one, I still
got the same result. It seems to me that Oracle is trying to verify
the file access permission, but the Oracle user is not OS user.

Thanks.

mike
ma...@mymail.co.uk (Mark) wrote in message news:<ddb31653.02050...@posting.google.com>...

[Mark]

Hi,

This one is really leaving me puzzled!
Looked on MetaLink, doesn't say too much about the problem.

Looked at Google/DejaNews archive, you may be interested in the
following (cut and paste the whole link in your browser)

http://groups.google.com/groups?q=ora+22288&hl=en&selm=8n3nN8uXUqaYiryKD%3DFOE1OAZKG0%404ax.com&rnum=2

If you look at your errors, 6512 is the famous stack error message
meaning that something else is causing your problem, it doesn't look
like the corresponding ORA message is being propogated through
although the 'Permission Denied' is a clue.

Silly questions time?

1. Are you SURE its the same database being acessed from the client?
2. Does the client install version match the server db install (e.g
You havent got Oracle client 7.3 trying to access an Oracle 8i server
database).
3. What OS is your client? Win95/98/NT/200/XP? If *heaven forbid*
you are using Win 3.1, maybe it cannot handle the long filename?
4. What version is your DB?
5. In your DBMS_LOB procedure, if using Oracle Directories, have you
granted read privs to the Oracle users?

Otherwise, try logging a call to Oracle.

Let us know if you get any joy.