[SOLVED] ORA-00904: "DBMS_METADATA"."GET

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

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

Solution

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.