Once upon a time… I needed to check in the source of which objects a particular character string was included.

LONG (the pun!…) story short, non of the {USER|ALL|DBA}_SOURCE views give you a chance to search in the source of the views.

„Well, why is that a problem?!” – I hear you saying – „Don’t you know the {USER|ALL|DBA}_VIEWS views views (wait, one views too many)?”

O my, but of course! There’s a column named TEXT in them with the source of the views! Let’s try that:

SELECT * FROM user_views WHERE INSTR(UPPER(text), 'EMPLOYEE') > 0;

> ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Well, it turns out that the TEXT column of the *_VIEWS views is of a LONG type and we can’t search in it. Maybe either of the below approaches will work?

SELECT * FROM user_views
  WHERE INSTR(UPPER(TO_CHAR(text)), 'EMPLOYEE') > 0;

SELECT * FROM user_views
  WHERE INSTR(UPPER(TO_CLOB(text)), 'EMPLOYEE') > 0;

> ORA-00932: inconsistent datatypes: expected CHAR got LONG
> ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Nope. So, is this the end of the world as we know it? Wellll, no. I struggled quite a bit to be able to search in the source of the views, but finally got the below solution to work and I’m now using it at work:

CREATE TABLE views_source (
  view_name VARCHAR2(30),
  text CLOB
);

BEGIN
  FOR v IN (SELECT view_name, text FROM user_views) LOOP
    INSERT INTO views_source VALUES (v.view_name, v.text);
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM views_source WHERE instr(upper(text), 'EMPLOYEE') > 0;

Result:

VIEW_NAME               TEXT
----------------------- ------------------------
EMP_DETAILS_VIEW        SELECT e.employee_id ...

And that’s it! The trick here is this: in PL/SQL context, Oracle automatically converts for us a LONG value to a CLOB.

Now you can check if there is anyone with a sense of humour at your work, by, for example, issuing the following:

SELECT * FROM views_source WHERE instr(upper(text), 'COOKIE') > 0;

I hope you enjoyed my article. If you have found any errors in it (even typos), you think that I haven’t explained anything clearly enough or you have an idea how I could make the article better – please, do not hesitate to contact me, or leave a comment.