Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_LOB problem
icon7.gif  DBMS_LOB problem [message #259687] Thu, 16 August 2007 04:42 Go to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Hello,

I have the following Problem:

Using oracle 9i I try to read out an entry in the ALERTLOG.

The following code should work, but I get an error:
line 14: Table or view does not exist...
line 18: Table or view does not exist...

If anyone could test the code or give me a hint, where I have to search for the problem.

I checked the select - Statements on their own. They did.

The user posseses all the necessary rights:
select any table
create any directory

Thanks in advance for your help!
Re: DBMS_LOB problem [message #259689 is a reply to message #259687] Thu, 16 August 2007 04:47 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Check the grant permission of v$parameter and dba_directories select.
If you want to successfully compile this procedure then connect to sys as sysdba,
then execute the below statements
grant select on v$parameter to <YourSchemaName>;
grant select on dba_directories to <YourSchemaName>;

and recompile the procedure.
Cheers
Sanka

[Updated on: Thu, 16 August 2007 05:07]

Report message to a moderator

Re: DBMS_LOB problem [message #259707 is a reply to message #259689] Thu, 16 August 2007 05:17 Go to previous messageGo to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Hallo sanka_yanka!

Thanks for yout reply:

I granted select on dba_directories to <MY_SchemaName>;
But I got an error when I tried to grant select on v$parameter

ORA-02030: can only select from fixed tables/views

Do you have an idea, why I get this?

Regards

Dagmar
Re: DBMS_LOB problem [message #259710 is a reply to message #259707] Thu, 16 August 2007 05:27 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Another idea may come out.
Connect to SYS as SYSDBA
First create a public synonym of v$parameter. like:

CREATE PUBLIC SYNONYM SYS_PARAMETER FOR SYS.V_$PARAMETER;

Now grant the SYNONYM SYS_PARAMETER.

Use SYS_PARAMETER on your procedure instead of v$parameter.

Hope this will give you solution of your problem.

Cheers
Sanka
Re: DBMS_LOB problem [message #259717 is a reply to message #259710] Thu, 16 August 2007 05:46 Go to previous messageGo to next message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Hallo sanka_yanka,

Terrifi, I can compile the procedure without errors.
Unfortunately calling the procedure evokes another error:

ORA-22288: file or LOB operation FILEOPEN failed
ORA-06512: in "SYS.DBMS_LOB", line 504
ORA-06512: in "REF1.GET_ALERT_FILE", line 29
ORA-06512: in line 1

Any idea what this could be?

Tanks in Advance!

Dagmar

Re: DBMS_LOB problem [message #259723 is a reply to message #259717] Thu, 16 August 2007 05:59 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Check on your DUMP location i.e. ALERTLOG_DIR physical location and make sure orclALRT.LOG file exists or not. May be it can store by another name. Check it and replace that name if you find other name and recompile/run the procedure.


Cheers
Sanka
Re: DBMS_LOB problem [message #259748 is a reply to message #259717] Thu, 16 August 2007 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-22288: file or LOB operation %s failed\n%s
 *Cause: The operation attempted on the file or LOB failed.
 *Action: See the next error message in the error stack for more detailed
          information.  Also, verify that the file or LOB exists and that
          the necessary privileges are set for the specified operation. If
          the error still persists, report the error to the DBA.

Regards
Michel
Re: DBMS_LOB problem [message #259761 is a reply to message #259748] Thu, 16 August 2007 07:10 Go to previous message
dheuschkel
Messages: 14
Registered: March 2007
Junior Member
Hallo back again!

Thank you very much for your help.

Now it works.

In fact the name of the alertfile was different and as I changed the name the procedure worked!

Bye Dagmar

Previous Topic: Conditional Compilation?
Next Topic: Batch File
Goto Forum:
  


Current Time: Fri Dec 09 01:49:05 CST 2016

Total time taken to generate the page: 0.14595 seconds