(PL)SQL - Dbms

When running this query as sys (we use sys to make sure grants/privileges are not causing the problems):

select a.*
from
(
  select v.*
  ,      dbms_metadata.get_ddl(object_type => 'VIEW', name => v.view_name, schema => v.owner) as view_ddl
  from dba_views v
  where v.owner = 'HR'
) a
where dbms_lob.instr(a.view_ddl, 'SOME_STRING') <> 0

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:

or 1=1

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:

select a.*
from
(
  select v.*
  ,      tedstruik.ted_p1026.column_long_to_clob
           ( p_table_or_view_name => 'user_views'
           , p_column_name        => 'text'
           , p_where_clause       => 'view_name='''||v.view_name||''''
           ) as view_ddl
  from user_views v
  where v.view_name like 'TED%'
) a
where dbms_lob.instr(a.view_ddl, 'SOME_STRING') <> 0

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.

Using dbms_metadata to get the source of views can produce strange results sometimes.
I use this package to retrieve the source of views for my Show Source Code package. When using this package directly from the tedstruik schema where all the objects are located, it works fine. When using it from this application in Apex however, which uses tedstruik as its parsing schema, things get a bit weird...

Called from Apex, dbms_metadata will truncate views that are 4000 characters or less, after "... AS ".
So this:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEDSTRUIK"."TED_P1132_V" ("WORKSPACE", "APPLICATION_ID", "PAGE_ID", "OBJECT_TYPE", "OBJECT_SEQUENCE", "OBJECT_NAME", "AUTHORIZATION_SCHEME_ID", "AUTHORIZATION_SCHEME", "MAYBE_AUTHORIZATION_SCHEME_ID", "MAYBE_AUTHORIZATION_SCHEME_NAME") AS 
  with app1 as
(
[ ... etc ... ]

becomes this:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEDSTRUIK"."TED_P1132_V" ("WORKSPACE", "APPLICATION_ID", "PAGE_ID", "OBJECT_TYPE", "OBJECT_SEQUENCE", "OBJECT_NAME", "AUTHORIZATION_SCHEME_ID", "AUTHORIZATION_SCHEME", "MAYBE_AUTHORIZATION_SCHEME_ID", "MAYBE_AUTHORIZATION_SCHEME_NAME") AS

Note that dbms_metadata works just fine when the view is bigger than 4000 chars. So I suspect it has something to do with a SQL query that uses varchar2 instead of clob, but of course that is just speculation.
To get my Show Source Code package to work properly, I used user_views.text_vc for views of 4000 characters or less. Not ideal but it works.

The query below can be used to test/demonstrate this. When run directly from the tedstruik schema this produces 0 for both diff_1 and diff_2, as expected. When run from Apex, you will see for the sub-4000 views that diff_1 becomes negative (because text_ddl_1 is truncated), and that diff_2 becomes null (because get_xml does not return a result).

with a as
(
  select v.view_name
  ,      v.text_length
  ,      dbms_metadata.get_ddl(object_type => 'VIEW', name => v.view_name) as text_ddl_1
  ,      dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml('VIEW', v.view_name)).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval(), 1) as text_ddl_2
  from user_views v
)
select a.view_name
,      a.text_length
,      length(a.text_ddl_1) - instr(a.text_ddl_1,' AS ') - a.text_length - 6 as diff_1
,      length(a.text_ddl_2) - a.text_length as diff_2
from a
order by 1

ORA-31603

Truncated Views