UNLONG my dictionary – those pesky LONG columns

Posted by

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:

imageimage

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>&quot;ALLOW_SUBDIRS&quot; IS NOT NULL</SEARCH_CONDITION>
    <SEARCH_CONDITION_VC>&quot;ALLOW_SUBDIRS&quot; 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 🙂

6 comments

  1. I would be fine with leaving LONG datatypes in the dictionary if Oracle had provided a function for converting LONG to CLOB in a SELECT statement. Instead, TO_LOB is restricted to an INSERT statement. So when I want to search a dictionary view for something in a LONG column, I write the contents to a table. But it’s ridiculous to need any of these workarounds.

    1. Just my thoughts, why are there no functions available to filter on LONG ie. TO_LOB? Is it so hard to implement?

      1. My *guess* is – consider a query: SELECT TO_LOB(col) FROM …

        In a CTAS, the moment I write a block, the LOBs I built for that block can be discarded. So at most I’d need “n” LOB’s where “n” is the max number of rows in any one block in that table.

        But with a query, and given that cursors can scroll throughout a resultset, I might need a temporary LOB (built from the LONG) for every row in the table. That could be millions of temporary lobs, all of which have to be created/retained throughout the query.

        I stress – I don’t know if *that* is the reason, but I can see how things could get messy

Leave a Reply to kevin Zhang Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.