Re: Problem of executing DBMS_LOB and UTL_FILE

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
markg_at_mymail.co.uk (Mark) wrote in message news:<ddb31653.0205090205.1ff4b253_at_posting.google.com>...
> 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
>
> wucf_at_hotmail.com (Michael) wrote in message news:<d08d4e18.0205071921.24315e75_at_posting.google.com>...
> > 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
Received on Thu May 09 2002 - 17:06:46 CEST