Re: problems with dbms_metadata

On Fri, 20 Jun 2003 19:57:26 +0200, Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote:

>On 19 Jun 2003 22:36:13 -0700, sct_at_picknowl.com.au (Ecce Nihil) wrote:
>
>>I'm having problems using dbms_metadata.get_ddl. I have a table called
>>agreements with the following entry out of user_objects...
>>
>>select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where
>>object_name='AGREEMENTS';
>>
>>OBJECT_NAME OBJECT_TYPE STATUS
>>-------------- ------------------ -------
>>AGREEMENTS TABLE VALID
>>
>>Then I execute...
>>
>>select dbms_metadata.get_ddl('TABLE','AGREEMENTS') from dual
>>
>>and get...
>>
>>ORA-31603: object "AGREEMENTS" of type TABLE not found in schema
>>"MY_SCHEMA"
>>
>>Any ideas?
>
>Evidently this must be a procedure. Not sure why you are calling in a
>redundant select.

 Do you mean 'evidently this must be _called from_ within a procedure'? (since dbms_metadata.get_ddl is clearly a function, as documented) and that this is due to roles not applying to definer-rights stored procedures?

 Assuming it is in a stored procedure (even though the posting implies, but does not actually say, that it's being run from SQL*Plus, from the formatting of the user_objects query and the fact that the function is being selected from DUAL in the first place), the call does not state a SCHEMA parameter, so it's fetching metadata of a table in the same schema as the procedure. So additional privileges aren't needed, since a schema owner has rights on his own objects.

 If it were in a stored procedure, attempting to fetch metadata of another user's table (i.e. specifying the SCHEMA parameter) then it would be an issue (and the select from dual would be redundant). But that wasn't what was posted...

 To Ecce Nihil:  

 Can you confirm how you're running this?  You are running it as the MY_SCHEMA user, right?  What's the definition of the AGREEMENTS table? (including constraints)  Database version could help, too; 9.0 or 9.2, and which patch?

 Can you reduce it down to a script to reproduce it starting from 'create user' and ending up with the call that fails? (You'd have to do that anyway if it were a problem in Oracle, but the process of reducing it down often points out any mistakes)

--
Andy Hassall (andy_at_andyh.co.uk)