Re: Storage problems with Oracle Blobs.

Jon wrote:
> Hi.
>
> I am quite new to Oracle and am experiancing a problem with Blobs. We
> are currently running Oracle 9i and have done a bit of a tidy up on
> our server, previously users would connect to a schema with the user
> that created it. The user had dba permissions.
>
> We have changed the setup so that each user now has their own login,
> each user has been granted dba permissions. As soon as we remove the
> dba permissions from the user that created the schema we are unable to
> store any data in blob fields.
>
> The Oracle server is installed in Windows Server 2003 and the
> application is running on a Vista client pc. I believe both systems
> are up to date with patches. The application it's self uses
> SQLExecDirect and SQLPutData to access the database. The error we are
> getting is a generic "ORA-03113: end-of-file on communication
> channel".
>
> I have Googled the error and cannot find anything specific, I would be
> grateful for any assistance in this as it has me very confused.
>
> Regards, Jon.

You have presiding over a nightmare. DBA permissions to users? Check yourself into a 12 step program.

To diagnose the problem you are having you need to start with what version of Oracle you have. 9i is a marketing label not a version. So what version do you have? If the answer is not 9.2.0.8 then apply the appropriate patches.

Also look in the alert log and see what else is happening.

Finally you need to find someone that knows something about the Oracle security model and have them help you set up an appropriate role that supports your application. And, most likely, grant explicit EXECUTE on one or more PL/SQL objects to the users as this can not be done in a role (probably part of the root cause of your issue).

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org