We’ve have all seen those TV crime shows where the psychiatrist does word association games, such as:
Question: “Blue?”
Answer: “Sky”
Question: “Cat?”
Answer: “Mouse”
Question: “Up?”
Answer: “Down”
etc etc.
And here’s the association that plagues developers and DBAs alike when they are querying the data dictionary.
Question: “LONG?”
Answer: “Aggghhhhhh!!!!”
Anyone that has queried the Oracle data dictionary has probably encountered the following frustrations at least once in their IT career.
SQL> create table t as
2 select * from user_tab_columns;
select * from user_tab_columns
*
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
or whenever you wanted to apply a WHERE clause
SQL> select *
2 from all_mviews
3 where query like '%EMP%';
where query like '%EMP%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
Typically on forums and the like, people will vent their frustration along the lines of “Hey Oracle, you told us to move from LONG to CLOB, why can’t you?” and this is certainly an understandable position. In fact, the motivation for this post came from a recent twitter exchange with some good friends of the Oracle community:
But think about what that would entail, and the risks it might pose to you as a customer. Do you really want a significant reorganisation of the data dictionary that has nearly 40 years of evolution and maturity build into it? No matter how much testing could be done internally, there will always be the small chance that a customer will have a particular niche circumstance where converting the all of the LONGs to CLOB would have a detrimental impact, and what recourse would there then be? Its not like customers can manually tune the internal data dictionary queries. Similarly, anyone running something like Fusion Applications (with its tens of thousands of tables, and hundreds of thousands of columns) probably is not keen on having their upgrade windows take a massive jump in duration to reload all those dictionary views with CLOB. I’m not saying it can be never be done, but its important to realise that the risks are high. Risk versus reward is an important metric to take into account here.
Similarly, I’ve had people reach out and say “Why didn’t they do this back in Oracle 8 when CLOBs first came out?”. That is also a high stakes game to play. We all know that in the world of application development, sometimes a feature misses a release deadline. It might be due to the last minute bug, or simply the timeframe was too tight. The Oracle Database is of course, just a giant application so it lives under those same rules. I didn’t work for Oracle at the time, but things like in-memory arriving in 12.1.0.2 not 12.1, or 128-character length tables names arriving in 12.2 not 12.1, or edition-based redefinition coming in 11.2 not 11.1 are perhaps examples of features missing a cut and arriving in the next deployment. (Note, I don’t know any of those specifics, but I’m just quoting possibilities). Can you imagine if a development team inside Oracle had “hitched their wagon” to the new LOB feature being available in 8.0, and refactored the entire data dictionary to use them, and then being told “Oh, LOBs are being cut from 8.0.4. Is that a problem?” 🙂. Once again, risk versus reward.
12c helps
This is not to say things are not on the improve. Oracle has been making progress over the years, and if you refer to this post, you’ll see that varchar2 columns have been added where possible to make things easier.
But here is a little trick to help you navigate those pesky LONG columns in the data dictionary. If you generate XML from a query, then the DBMS_XMLGEN will happily look after the conversion for you (to a reasonable degree). For example, I can easily see the SEARCH_CONDITION in USER_CONSTRAINTS via the following:
SQL> select dbms_xmlgen.getxmltype('select * from USER_CONSTRAINTS where constraint_type = ''C''') FROM dual;
DBMS_XMLGEN.GETXMLTYPE('SELECT*FROMUSER_CONSTRAINTSWHERECONSTRAINT_TYP
----------------------------------------------------------------------
<ROWSET>
<ROW>
<OWNER>MCDONAC</OWNER>
<CONSTRAINT_NAME>SYS_C0021133</CONSTRAINT_NAME>
<CONSTRAINT_TYPE>C</CONSTRAINT_TYPE>
<TABLE_NAME>ALLOWED_DIRECTORIES</TABLE_NAME>
<SEARCH_CONDITION>"ALLOW_SUBDIRS" IS NOT NULL</SEARCH_CONDITION>
<SEARCH_CONDITION_VC>"ALLOW_SUBDIRS" IS NOT NULL</SEARCH_CONDITION_VC>
<STATUS>ENABLED</STATUS>
<DEFERRABLE>NOT DEFERRABLE</DEFERRABLE>
<DEFERRED>IMMEDIATE</DEFERRED>
<VALIDATED>VALIDATED</VALIDATED>
<GENERATED>GENERATED NAME</GENERATED>
<LAST_CHANGE>27-JUN-20</LAST_CHANGE>
<ORIGIN_CON_ID>3</ORIGIN_CON_ID>
</ROW>
<ROW>
<OWNER>MCDONAC</OWNER>
<CONSTRAINT_NAME>SYS_C0021134</CONSTRAINT_NAME>
<CONSTRAINT_TYPE>C</CONSTRAINT_TYPE>
...
...
Of course, XML is not cool anymore and relational structures are undergoing a resurgence but we can easily convert that back into a relational view now that that the LONG has been cleansed away.
select
x.owner
,x.constraint_name
,x.constraint_type
,x.table_name
,x.search_condition
,x.search_condition_vc
,x.r_owner
,x.r_constraint_name
,x.delete_rule
,x.status
,x.deferrable
,x.deferred
,x.validated
,x.generated
,x.bad
,x.rely
,x.last_change
,x.index_owner
,x.index_name
,x.invalid
,x.view_related
,x.origin_con_id
FROM XMLTABLE('/ROWSET/ROW'
PASSING (
SELECT DBMS_XMLGEN.GETXMLTYPE(
'select * from user_constraints'
) FROM dual
)
COLUMNS
owner varchar2(128)
,constraint_name varchar2(128)
,constraint_type varchar2(1)
,table_name varchar2(128)
,search_condition varchar2(4000)
,search_condition_vc varchar2(4000)
,r_owner varchar2(128)
,r_constraint_name varchar2(128)
,delete_rule varchar2(9)
,status varchar2(8)
,deferrable varchar2(14)
,deferred varchar2(9)
,validated varchar2(13)
,generated varchar2(14)
,bad varchar2(3)
,rely varchar2(4)
,last_change date
,index_owner varchar2(128)
,index_name varchar2(128)
,invalid varchar2(7)
,view_related varchar2(14)
,origin_con_id number
) x
I covered this technique in a video which walks through this a little more slowly.
Solving it globally
I’m guessing you’re not particularly enamoured with the prospect of writing that query every time you want to see what the content of a CHECK constraint is. But no need to worry, the heavy lifting has been done for you. I’ve written a procedure that will “unlong” the data dictionary views for you and create a Z$-prefixed version for you use whenever you like.
SQL> --
SQL> -- Note: user you run this as my have CREATE VIEW privilege granted directly
SQL> --
SQL> create or replace
2 procedure UNLONG_MY_DICTIONARY is
3
4 procedure UNLONG_MY_VIEW(p_owner varchar2, p_table_name varchar2) is
5 cursor c_cols is
6 select cols.column_name,
7 cols.data_type,
8 cols.data_precision,
9 cols.data_scale,
10 cols.data_type_owner,
11 cols.data_length,
12 cols.column_id,
13 case
14 when regexp_replace(cols.data_type,'\(.*\)') in ('CLOB','NCLOB','DATE','TIMESTAMP','ROWID',
15 'BLOB','INTERVAL DAY','INTERVAL YEAR TO MONTH',
16 'TIMESTAMP WITH TIME ZONE','TIMESTAMP WITH LOCAL TIME ZONE','LONG RAW')
17 then cols.data_type
18 when cols.data_type in ('RAW','CHAR','VARCHAR2','NCHAR','NVARCHAR2')
19 then cols.data_type||'('||cols.data_length||')'
20 when (cols.data_type in ('NUMBER') and cols.data_precision is not null)
21 then cols.data_type||'('||cols.data_precision||','||cols.data_scale||')'
22 when (cols.data_type in ('NUMBER','BINARY_DOUBLE','BINARY_FLOAT','FLOAT') and cols.data_precision is null)
23 then cols.data_type||case when cols.data_scale is not null then '(*,'||cols.data_scale||')' end
24 when cols.data_type in ('FLOAT')
25 then cols.data_type||case when cols.data_precision < 126 then '('||cols.data_precision||')' end
26 when cols.data_type in ('LONG')
27 then 'VARCHAR2(4000)'
28 end new_data_type
29 from dba_tab_columns cols
30 where owner = p_owner
31 and table_name = p_table_name
32 order by cols.column_id;
33
34 type col_list is table of c_cols%rowtype;
35 l_cols col_list;
36
37 l_sql1 clob := 'select ';
38 l_sql2 clob := 'columns ';
39 begin
40 open c_cols;
41 fetch c_cols bulk collect into l_cols;
42 close c_cols;
43
44 for i in 1 .. l_cols.count loop
45 l_sql1 := l_sql1 || case when i > 1 then ',' end || 'x.'||l_cols(i).column_name||chr(10);
46 l_sql2 := l_sql2 || case when i > 1 then ',' end || l_cols(i).column_name||' '||l_cols(i).new_data_type||chr(10);
47 end loop;
48
49 l_sql1 := l_sql1 || q'{FROM XMLTABLE('/ROWSET/ROW' }'||chr(10);
50 l_sql1 := l_sql1 || q'{PASSING (SELECT DBMS_XMLGEN.GETXMLTYPE( }'||chr(10);
51 l_sql1 := l_sql1 || '''select * from '||p_table_name||''''||chr(10);
52 l_sql1 := l_sql1 || q'{) FROM dual)}'||chr(10);
53 l_sql2 := l_sql2 || ') x';
54
55 --dbms_output.put_line(l_sql1||l_sql2);
56 execute immediate 'create or replace view Z$'||p_table_name||' as '||l_sql1||l_sql2;
57 execute immediate 'grant select on Z$'||p_table_name||' to public';
58 dbms_output.put_line('Created view: Z$'||p_table_name);
59 end;
60
61 begin
62 for i in (
63 select table_name
64 from dba_tab_columns
65 where data_type = 'LONG'
66 and substr(table_name,1,4) in ('ALL_','USER')
67 and owner = 'SYS'
68 order by 1
69 )
70 loop
71 --dbms_output.put_line(i.table_name);
72 unlong_my_view('SYS',i.table_name);
73 end loop;
74 end;
75 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec UNLONG_MY_DICTIONARY
Created view: Z$ALL_ARGUMENTS
Created view: Z$ALL_CLUSTER_HASH_EXPRESSIONS
Created view: Z$ALL_CONSTRAINTS
Created view: Z$ALL_IND_EXPRESSIONS
Created view: Z$ALL_IND_PARTITIONS
Created view: Z$ALL_IND_SUBPARTITIONS
Created view: Z$ALL_MVIEWS
Created view: Z$ALL_MVIEW_AGGREGATES
Created view: Z$ALL_MVIEW_ANALYSIS
Created view: Z$ALL_NESTED_TABLE_COLS
Created view: Z$ALL_REGISTERED_MVIEWS
Created view: Z$ALL_REGISTERED_SNAPSHOTS
Created view: Z$ALL_SNAPSHOTS
Created view: Z$ALL_SQLSET_PLANS
Created view: Z$ALL_SUBPARTITION_TEMPLATES
Created view: Z$ALL_SUMMARIES
Created view: Z$ALL_SUMMARY_AGGREGATES
Created view: Z$ALL_TAB_COLS
Created view: Z$ALL_TAB_COLS_V$
Created view: Z$ALL_TAB_COLUMNS
Created view: Z$ALL_TAB_PARTITIONS
Created view: Z$ALL_TAB_SUBPARTITIONS
Created view: Z$ALL_TRIGGERS
Created view: Z$ALL_TRIGGERS_AE
Created view: Z$ALL_VIEWS
Created view: Z$ALL_VIEWS_AE
Created view: Z$ALL_ZONEMAPS
Created view: Z$ALL_ZONEMAP_MEASURES
Created view: Z$USER_ADVISOR_SQLPLANS
Created view: Z$USER_ARGUMENTS
Created view: Z$USER_CLUSTER_HASH_EXPRESSIONS
Created view: Z$USER_CONSTRAINTS
Created view: Z$USER_IM_EXPRESSIONS
Created view: Z$USER_IND_EXPRESSIONS
Created view: Z$USER_IND_PARTITIONS
Created view: Z$USER_IND_SUBPARTITIONS
Created view: Z$USER_MVIEWS
Created view: Z$USER_MVIEW_AGGREGATES
Created view: Z$USER_MVIEW_ANALYSIS
Created view: Z$USER_NESTED_TABLE_COLS
Created view: Z$USER_OUTLINES
Created view: Z$USER_REGISTERED_MVIEWS
Created view: Z$USER_REGISTERED_SNAPSHOTS
Created view: Z$USER_SNAPSHOTS
Created view: Z$USER_SQLSET_PLANS
Created view: Z$USER_SQLTUNE_PLANS
Created view: Z$USER_SUBPARTITION_TEMPLATES
Created view: Z$USER_SUMMARIES
Created view: Z$USER_SUMMARY_AGGREGATES
Created view: Z$USER_TAB_COLS
Created view: Z$USER_TAB_COLS_V$
Created view: Z$USER_TAB_COLUMNS
Created view: Z$USER_TAB_PARTITIONS
Created view: Z$USER_TAB_SUBPARTITIONS
Created view: Z$USER_TRIGGERS
Created view: Z$USER_TRIGGERS_AE
Created view: Z$USER_VIEWS
Created view: Z$USER_VIEWS_AE
Created view: Z$USER_ZONEMAPS
Created view: Z$USER_ZONEMAP_MEASURES
PL/SQL procedure successfully completed.
SQL>
You can get the source for the routine at my github repo . Note that these views are intended for adhoc use rather than smashing away at them at high frequency, because remember that we’re building the XML for the view each time you execute a query. You could materialize these views if needed, but be aware that they would then be a little stale.
Hopefully you find this workaround useful although I understand if it is not the solution you have been …… (pauses for comedic effect)…. LONG-ing for 🙂
Learning is not a spectator sport 
Got some thoughts? Leave a comment