As Oracle developers, most of us have learned to harness the power of the data dictionary to make our own lives easier and we all tend to have a number of "goto" scripts that save us time and effort that is better spent being productive. One such script in your arsenal may be to query the DBA_VIEWS (or the ALL/USER alternatives) to find which views have that regular expression you have used numerous times before but have forgotten the exact syntax.
If so, then you have surely come up against the LONG datatype and what a pain it is to work with and quite why Oracle continue to use it in their own internal structures when they have long (pardon the pun) been instructing us developers to abandon it in our own use is still a head-scratcher. So how do we search a LONG datatype?
Well, one option is quite simply don't! Since Oracle 12, the dictionary view has provided a VARCHAR2 representation of the view definition that you can query directly imaginatively named TEXT_VC. Great, end of blog.......hang on what about if the text column isn't big enough to hold the entire view definition, I might not find what I was looking for.
So, we need a generic way to access the LONG column but still allow us to have a SQL script that we can execute to return all the matching views and their definitions. That's where a PIPELINED function can come to our rescue. It takes little set up and once done it could be housed in a schema that has permissions to see all view definitions and granted out to other schemas to invoke as needed.
Firstly we will need to create a global temporary table to hold all the view definitions and, most importantly, convert the TEXT column to a CLOB rather than the LONG datatype:
CREATE GLOBAL TEMPORARY TABLE DBA_VIEWS_GTT
( "OWNER" VARCHAR2(128),
"VIEW_NAME" VARCHAR2(128),
"TEXT_LENGTH" NUMBER,
"TEXT_VC" VARCHAR2(4000),
"TYPE_TEXT_LENGTH" NUMBER,
"TYPE_TEXT" VARCHAR2(4000),
"OID_TEXT_LENGTH" NUMBER,
"OID_TEXT" VARCHAR2(4000),
"VIEW_TYPE_OWNER" VARCHAR2(128),
"VIEW_TYPE" VARCHAR2(128),
"SUPERVIEW_NAME" VARCHAR2(128),
"EDITIONING_VIEW" VARCHAR2(1),
"READ_ONLY" VARCHAR2(1),
"CONTAINER_DATA" VARCHAR2(1),
"BEQUEATH" VARCHAR2(12),
"ORIGIN_CON_ID" NUMBER,
"TEXT_CLOB" CLOB
) ON COMMIT PRESERVE ROWS ;
Notice that final column called TEXT_CLOB, that is where the LONG data will land and be converted so it can be used in a query's predicate. It is imperative that this table is defined as ON COMMIT PRESERVE ROWS because we are going to have to make sure the data remains available to the calling session when we issue a commit in the function but more on that later.
A user-defined object type needs to be defined that matches the definition of the global temporary table. You can create this at the database level or in a package specification but for simplicity I will go with the packaged implementation. Here is the spec:
CREATE OR REPLACE PACKAGE UTIL_DBA_VIEW IS
TYPE DBA_VIEW_REC IS RECORD(
OWNER DBA_VIEWS_GTT.OWNER%TYPE,
VIEW_NAME DBA_VIEWS_GTT.VIEW_NAME%TYPE,
TEXT_LENGTH DBA_VIEWS_GTT.TEXT_LENGTH%TYPE,
TEXT_VC DBA_VIEWS_GTT.TEXT_VC%TYPE,
TYPE_TEXT_LENGTH DBA_VIEWS_GTT.TYPE_TEXT_LENGTH%TYPE,
TYPE_TEXT DBA_VIEWS_GTT.TYPE_TEXT%TYPE,
OID_TEXT_LENGTH DBA_VIEWS_GTT.OID_TEXT_LENGTH%TYPE,
OID_TEXT DBA_VIEWS_GTT.OID_TEXT%TYPE,
VIEW_TYPE_OWNER DBA_VIEWS_GTT.VIEW_TYPE_OWNER%TYPE,
VIEW_TYPE DBA_VIEWS_GTT.VIEW_TYPE%TYPE,
SUPERVIEW_NAME DBA_VIEWS_GTT.SUPERVIEW_NAME%TYPE,
EDITIONING_VIEW DBA_VIEWS_GTT.EDITIONING_VIEW%TYPE,
READ_ONLY DBA_VIEWS_GTT.READ_ONLY%TYPE,
CONTAINER_DATA DBA_VIEWS_GTT.CONTAINER_DATA%TYPE,
BEQUEATH DBA_VIEWS_GTT.BEQUEATH%TYPE,
ORIGIN_CON_ID DBA_VIEWS_GTT.ORIGIN_CON_ID%TYPE,
TEXT_CLOB DBA_VIEWS_GTT.TEXT_CLOB%TYPE);
TYPE DBA_VIEW_TBL IS TABLE OF DBA_VIEW_REC;
FUNCTION GET_DBA_VIEWS RETURN UTIL_DBA_VIEW.DBA_VIEW_TBL
PIPELINED;
END UTIL_DBA_VIEW;
You will notice the PIPELINED FUNCTION definition that returns the user-defined type/collection. We will dive into that now in the package body:
CREATE OR REPLACE PACKAGE BODY UTIL_DBA_VIEW IS
FUNCTION GET_DBA_VIEWS RETURN UTIL_DBA_VIEW.DBA_VIEW_TBL
PIPELINED IS
CURSOR CUR_DBA_VIEW IS
SELECT V.OWNER,
V.VIEW_NAME,
V.TEXT_LENGTH,
V.TEXT_VC,
V.TYPE_TEXT_LENGTH,
V.TYPE_TEXT,
V.OID_TEXT_LENGTH,
V.OID_TEXT,
V.VIEW_TYPE_OWNER,
V.VIEW_TYPE,
V.SUPERVIEW_NAME,
V.EDITIONING_VIEW,
V.READ_ONLY,
V.CONTAINER_DATA,
V.BEQUEATH,
V.ORIGIN_CON_ID,
V.TEXT_CLOB
FROM DBA_VIEWS_GTT V;
V_VIEW_REC UTIL_DBA_VIEW.DBA_VIEW_REC;
PROCEDURE REFRESH_GTT IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DELETE FROM DBA_VIEWS_GTT;
INSERT INTO DBA_VIEWS_GTT
SELECT V.OWNER,
V.VIEW_NAME,
V.TEXT_LENGTH,
V.TEXT_VC,
V.TYPE_TEXT_LENGTH,
V.TYPE_TEXT,
V.OID_TEXT_LENGTH,
V.OID_TEXT,
V.VIEW_TYPE_OWNER,
V.VIEW_TYPE,
V.SUPERVIEW_NAME,
V.EDITIONING_VIEW,
V.READ_ONLY,
V.CONTAINER_DATA,
V.BEQUEATH,
V.ORIGIN_CON_ID,
TO_LOB(V.TEXT) AS TEXT_CLOB
FROM DBA_VIEWS V
WHERE V.OWNER = USER;
INSERT INTO DBA_VIEWS_GTT
SELECT M.OWNER,
M.MVIEW_NAME,
M.QUERY_LEN,
'For MV use TEXT_CLOB' AS TEXT_VC,
NULL AS TYPE_TEXT_LENGTH,
NULL AS TYPE_TEXT,
NULL AS OID_TEXT_LENGTH,
NULL AS OID_TEXT,
NULL AS VIEW_TYPE_OWNER,
NULL AS VIEW_TYPE,
NULL AS SUPERVIEW_NAME,
NULL AS EDITIONING_VIEW,
'1' AS READ_ONLY,
NULL AS CONTAINER_DATA,
NULL AS BEQUEATH,
NULL AS ORIGIN_CON_ID,
TO_LOB(M.QUERY) AS TEXT_CLOB
FROM DBA_MVIEWS M
WHERE M.OWNER = USER;
COMMIT;
END REFRESH_GTT;
BEGIN
REFRESH_GTT;
OPEN CUR_DBA_VIEW;
FETCH CUR_DBA_VIEW
INTO V_VIEW_REC;
WHILE CUR_DBA_VIEW%FOUND LOOP
PIPE ROW(V_VIEW_REC);
FETCH CUR_DBA_VIEW
INTO V_VIEW_REC;
END LOOP;
CLOSE CUR_DBA_VIEW;
RETURN;
END GET_DBA_VIEWS;
BEGIN
NULL;
END UTIL_DBA_VIEW;
Let's break down what it is doing. The CURSOR CUR_DBA_VIEW is simply going to be used later to get the full list of view definitions to be PIPE'd out to the calling SQL statement so let's skip to the main body of the function to see what it is actually doing.
It first calls REFRESH_GTT which is a local procedure that will populate the global temporary table and it must be an AUTONOMOUS TRANSACTION otherwise you will get an error when invoking the function from SQL that you cannot issue DML in a SQL statement. You can see it involves a delete statement to clear the temporary global table and then two inserts to populate it. The first is to get the data from the DBA_VIEWS dictionary view filtered for just those owned by the invoking USER and the key trick here is the TO_LOB(TEXT) function call that allows us to easily convert the LONG datatype to a CLOB and store that in our table.
Why not just use the SELECT with the TO_LOB directly in our SQL you may ask? Well, we need the PL/SQL engine to perform the conversion as pure SQL will balk at this with an "inconsistent datatypes" error but PL/SQL is more than happy to do it.
The second insert is optional for you but I like to include materialized view definitions in my search results and to be safe I simply dump their definition into the TEXT_CLOB column but you could DBMS_LOB.SUBSTR it and also populate the TEXT column if you so wish. Why not a single insert with a UNION ALL? Oracle seems to dislike that approach and complains about the pesky LONG datatype in a set operation but not in a single SELECT, hence the two separate transactions.
That's it really, all we do then is loop through the global temporary table's contents and spit the results out to the invoking SQL statement. So something like this will now allow you to query the view definitions and find all those views you are looking for:
SELECT OWNER,
VIEW_NAME,
TEXT_VC,
TEXT_CLOB
FROM TABLE(UTIL_DBA_VIEW.GET_DBA_VIEWS)
WHERE TEXT_CLOB LIKE '%REGEXP_SUBSTR%';
Hopefully at some point Oracle will convert the LONG datatype column to CLOB or at least provide an additional CLOB helper column to the DBA_VIEWS data dictionary view and we won't need to work around it ourselves, but until then, maybe this will help make things a little easier for you.