ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier

Question

I get the ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier error when I run dbms_metadata.

That error is for having the wrong column name or alias. I am not sure why I am getting it.

Here is some code to show the error: I create a table:

create table test_table (
   column1 varchar2(300));

I insert a row of data:

insert into test_table values (55);

I try to get the DDL for it:

SELECT DBMS_METADATA.GET_DDL('TABLE','test_table') FROM dual;

Here is the error:

>> SELECT DBMS_METADATA.GET_DDL('TABLE','test_table') FROM dual
       *
Error at line 1
ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier

I checked permissions and it appears I have the correct permissions:

select * FROM DBA_ROLE_PRIVS where granted_role = 'SELECT_CATALOG_ROLE';

My user (aka schema) is in that list.

Here are, what I believe, the docs: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867

Probably different user. You could explicitly state the user who owns the table `SELECT DBMS_METADATA.GET_DDL('TABLE','test_table', 'XYZ') FROM dual;` — Lukasz Szozda, May 17 '19 at 14:09
I wish that was it. I have tried explicitly writing the user. I get the same error. — Harry L, May 17 '19 at 14:40
I assume you're relying on the line "users with SELECT_CATALOG_ROLE can see all objects". Which is true... but you would be able to get the DDL for your own objects even without that. However, you do still also need execute privileges on the `dbms_metadata` package. Do you have that privilege? (You *may* also need to refer to it as `sys.dbms_metadata` but there should be a public synonym that avoids the need for a schema prefix.) — Alex Poole, May 17 '19 at 15:02
Thanks @AlexPoole. How do I grant execute privileges on the dbms_metadata package? — Harry L, May 17 '19 at 16:07
`grant execute on dbms_metadata to xyz`, using your actual name of course. A privileged user (i.e. a DBA) will have to do that; you can't grant privileges to yourself, obviously *8-) — Alex Poole, May 17 '19 at 16:15
That makes sense. I tried that and got this error: ORA-00942: table or view does not exist — Harry L, May 17 '19 at 17:53

score 2 · Accepted Answer · answered May 18 '19 at 03:27

Run this command as SYS:

grant execute on sys.dbms_metadata to public;

Granting access to your specific user may have solved your current problem, but that package really needs to be available to the entire system. Many third party programs depend on DBMS_METADATA. By default that package should be granted to PUBLIC.

Some old versions of the STIG (Secure Technical Implementation Guidelines, which almost every auditor uses as the basis for their security scripts), would revoke access from public packages. But that was a stupid idea even 10 years ago, and it's not in the current STIGs anymore anyway.

ORA-00904: "DBMS_METADATA"."GET_DDL": invalid identifier

1 Answers1