Re: Problem of executing DBMS_LOB and UTL_FILE

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.

M

wucf_at_hotmail.com (Michael) wrote in message news:<d08d4e18.0205090706.73df4209_at_posting.google.com>...
> 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 Fri May 10 2002 - 10:34:26 CEST