title: How can I avoid errors with dbms_metadata.GET_DEPENDENT_DDL
body: <p>I'm writing a custom program to dump the database metadata to files in order to manage them with version control.  The default way that data pump or export works isn't ideal for a few reasons (eg. I'd like a separate directory per table).  </p>
  
  <p>Sql Developer provides a number of ways of creating export scripts for any object.  One way is just by right-clicking the object and selecting <code>Quick DDL</code>.  By viewing the logs it creates, one can see the actual SQL it issues to create the DDL script.  I've used these scripts to write my custom program and for the most part, they've been perfect.</p>
  
  <p>When I generate the DDL for a materialized view, the SQL it generates is:</p>
  
  <pre><code>SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW',:name,:owner) FROM DUAL 
  UNION ALL 
  SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER) FROM (
      SELECT table_name, table_owner FROM all_indexes 
      WHERE table_owner = :owner AND table_name = :name 
      AND index_name NOT IN (
          SELECT constraint_name FROM sys.all_constraints 
          WHERE table_name = table_name AND constraint_type = 'P'
      ) AND ROWNUM = 1
  ) 
  UNION ALL 
  SELECT dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', :name,:owner ) FROM DUAL
  </code></pre>
  
  <p>For this script, when executed via SQL Developer <code>Quick DDL</code>, it generates the metadata for the materialized view properly.  When I run this script in a program (or even manually with SQL Developer itself), it produces the following errors:</p>
  
  <pre><code>ORA-31608: specified object of type COMMENT not found
  ORA-06512: at "SYS.DBMS_METADATA", line 5805
  ORA-06512: at "SYS.DBMS_METADATA", line 8436
  ORA-06512: at line 1
  31608. 00000 -  "specified object of type %s not found"
  *Cause:    The specified object was not found in the database.
  *Action:   Correct the object specification and try the call again.
  </code></pre>
  
  <p>This particular materialized view doesn't have any comments (obviously), but I would have expected this part of the clause to just return 0 rows instead of generating an error (especially since SQL Developer uses this itself seemingly without errors).  </p>
  
  <p><strong>Is there a way I can avoid this error, while still including comments in the metadata if they exist?</strong></p>
  
  <p>This issue exists on both Oracle 10g &amp; 11g databases.</p>