When running this query as sys (we use sys to make sure grants/privileges are not causing the problems):
you get this error:
ORA-31603: object "ADD_JOB_HISTORY" of type VIEW not found in schema "HR"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1
Removing the last where clause, or changing it so it doesn't use view_ddl, makes the query work (although not with the result set you were looking for of course). So the problem is not the value of view_ddl itself, but the fact that we're actually using it. Maybe.
Other weird behaviour (or maybe further proof that using view_ddl is the cause): add this line to the end of the query:
Now you get the expected result (1 row).
Note that the error message suggests that ADD_JOB_HISTORY is a view; it is in fact a procedure
in the schema "HR". HR only contains 1 view, EMP_DETAILS_VIEW.
This behaviour is not unique to the HR schema, it happens everywhere. Using user_view instead of dba_view makes no difference either.
Sometimes the object in the error message is a package instead of a procedure, but that doesn't help really :-)
Seems like a bug to me. I have seen this behaviour in XE (11g), 11gR2 and 12c - so if it is a bug, it's a pretty old one.
Note: this seems to be fixed in 18c (XE).
I got the result above because I was trying to do a text search in the view sources, and I tried to avoid the text column (which is of type long). As an alternative, using the "long to clob" function described here
you could do something like this:
This does not return the actual ddl, but would be enough for text search purposes.
This example uses user_views; if you want to use dba_views you'd have to give the schema that houses the function some extra grants.