DIY on a public holiday is something akin to a Great British tradition. Unfortunately, my DIY skills can best be summed up as being able to move heavy stuff about and do a bit of light destruction.
Fortunately, Deb is great at that sort of thing. As a result, I seem to have spent rather a lot of time lately playing Igor to her
mad scientist visionary genius.
On an unusually sunny Bank Holiday Monday recently, The Lady of the House sent me packing out into the back garden with instructions to “tidy it up a bit”.
There followed an afternoon of intensive Chemical Warefare on the weeds ( the one thing I do seem to have a talent for growing) followed by judicious application of mower and strimmer.
Needless to say, the end result resembled less the restoration of verdant sward that she was secretly hoping for and more an example of slash-and-burn agriculture.
Fortunately, the next Bank Holiday isn’t until the end of May.
This gives me some time to continue in my attempts to persuade my better half to move into the 21st century garden-wise.
My argument is that, most people will only ever see our back garden on Google Maps. Provided it looks OK from the satellite view then that’s really what matters.
So, all we need to do is concrete the lawn over then paint the light and dark green stripes in.
This way, rather than constantly cutting the grass, we just need to repaint it once a year or so.
I’ve even offered to paint in some flowerbeds.
Ironically, this argument appears to have fallen on stony ground.
On top of this, the Oracle database has also required me to do a bit of DIY lately. This takes the form of trying to search the text in a column defined as LONG.
You may be wondering what on earth I’m doing with a LONG column. After all, Oracle introduced the CLOB way back in 8i, before the turn of the century. Surely no-one uses LONGs any more ?
Well, almost no-one…except Oracle.
Long columns in Data Dictionary Views
First of all, let’s see just how widely used LONG columns still are in the Data Dictionary :
select count(*) from dba_tab_columns where owner = 'SYS' and data_type = 'LONG' /
This comes back with the grand total of 229 on 11gXE. Obviously, this will include multiple views based on a single table. However, it still represents a significant presence for the venerable LONG datatype.
This presence does become particularly apparent when you want to – for example – have a look at the constraints in a given schema.
The Test Schema
Taking inspiration from some of Deb’s shopping lists for our planned trips to the garden centre…
create user igor identified by handy default tablespace users / grant create session, create table to igor / alter user igor quota unlimited on users /
Now to create the tables in the new schema :
create table garden_checklists ( gc_name varchar2(50) constraint gc_pk primary key ) / create table garden_checklist_items ( item_name varchar2(50) constraint gci_pk primary key, gc_name varchar2(50) not null constraint gci_gc_fk references garden_checklists( gc_name), description varchar2(4000), safe_for_mike varchar2(1) not null, cost number(9,2) constraint gci_cost_ck check (cost < 50.0) ) /
So, connected as Igor, we can see that we have the following constraints in place :
select constraint_name, constraint_type, search_condition from user_constraints where table_name = 'GARDEN_CHECKLIST_ITEMS' / CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION -------------------- --------------- ------------------------------------------------------- SYS_C0010851 C "GC_NAME" IS NOT NULL SYS_C0010852 C "SAFE_FOR_MIKE" IS NOT NULL GCI_COST_CK C cost < 50.0 GCI_PK P GCI_GC_FK R
Now, say we want to just list the Check constraints that other than NOT NULL constraints.
Simple enough you might think …
select constraint_name from user_constraints where constraint_type = 'C' and search_condition not like '%IS NOT NULL' /
However, when you run this you’ll get :
and search_condition not like '%IS NOT NULL' * ERROR at line 4: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
That’s right, the SEARCH_CONDITION column is defined as a long. It looks like we’re going to need a different approach.
TO_LOB or not TO_LOB ? That is the question
The TO_LOB function has been around…well, as long as the LOB datatypes. That should do the job…
select constraint_name from user_constraints where constraint_type = 'C' and to_lob(search_condition) not like '%IS NOT NULL' / and to_lob(search_condition) not like '%IS NOT NULL' * ERROR at line 4: ORA-00932: inconsistent datatypes: expected - got LONG
…or not, as the case may be.
In fact, TO_LOB does work, but only in a CREATE TABLE statement :
create table my_constraints as select constraint_name, constraint_type, to_lob( search_condition) as search_condition, r_owner, r_constraint_name from user_constraints / Table created. SQL> desc my_constraints Name Null? Type ----------------------------------------- -------- ---------------------------- CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) SEARCH_CONDITION CLOB R_OWNER VARCHAR2(120) R_CONSTRAINT_NAME VARCHAR2(30)
Going back to our problem – how to get a list of Check Constraints other than NOT NULL constraints, things are now a whole lot easier :
select constraint_name from my_constraints where constraint_type = 'C' and search_condition not like '%IS NOT NULL' / CONSTRAINT_NAME ------------------------------ GCI_COST_CK
OK, so we do have some kind of solution to our problem. However, creating a table every time we want to check the contents of a long column isn’t ideal.
Fortunately, there is another way of getting DDL for a constraint…
DBMS_METADATA.GET_DDL – Does Exactly What it Says On the Tin ?
Of course, DBMS_METADATA.GET_DDL will return DDL for a given object in the form of a CLOB. As it’s a DDL statement that we’re looking at, we will have to amend our predicate slightly, but the following should be fine :
select constraint_name from user_constraints where constraint_type = 'C' and dbms_metadata.get_ddl ( object_type => 'CONSTRAINT', name => constraint_name, schema => user ) not like '%NOT NULL%' /
This is where things start getting a little bit silly. Running this query will give you :
and dbms_metadata.get_ddl * ERROR at line 4: ORA-31603: object "SYS_C003" of type CONSTRAINT not found in schema "IGOR" ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5843 ORA-06512: at line 1
Now, it is true to say that SYS_C003 does not exist in the IGOR schema, it is valid to ask why on earth the function is even attempting to look it up, seeing as it’s being passed in a constraint name from USER_CONSTRAINTS.
If you’re interested, you can lookup this constraint ( as a user with sufficient privileges) :
select owner, table_name, constraint_name, constraint_type, search_condition from dba_constraints where constraint_name = 'SYS_C003' /
You will find that this is a Check Constraint on the FILE# column of SYS.TAB$.
Interestingly, USER_CONSTRAINTS has an OWNER column. If we use this in the predicate, we hit a slightly different issue…
select constraint_name from user_constraints where constraint_type = 'C' and owner = user and dbms_metadata.get_ddl ( object_type => 'CONSTRAINT', name => constraint_name, schema => user ) not like '%NOT NULL%' / and dbms_metadata.get_ddl * ERROR at line 5: ORA-31603: object "GCI_GC_FK" of type CONSTRAINT not found in schema "IGOR" ORA-06512: at "SYS.DBMS_METADATA", line 4018 ORA-06512: at "SYS.DBMS_METADATA", line 5843 ORA-06512: at line 1
GCI_GC_FK does, in fact, exist in the current schema. The issue here is that retrieving DDL for a Foreign Key constraint requires a slightly different call to the function :
select dbms_metadata.get_ddl( 'REF_CONSTRAINT', 'GCI_GC_FK', user) from dual;
Perhaps a more pertinent question to ask would be, why is the function once again ignoring the query predicate and looking up something that is not a check constraint ?
Now, whilst I do accept that I could just be being as dumb as a bag of hammers ( to continue the D.I.Y theme), I can assure you dear reader, that I have not been drinking. This eliminates at least one of Flavio Casetta’s hypotheses as to what is actually going on here 🙂
I think we can conclude from this that using the DBMS_METADATA.GET_DDL function in-line in a query is possibly not entirely reliable. However, it seems to behave itself if we enclose it in the straight-jacket of a correlated sub-query :
select cons.constraint_name from user_constraints cons where cons.owner = user and cons.constraint_type = 'C' and ( select dbms_metadata.get_ddl( 'CONSTRAINT', cons1.constraint_name, user) from user_constraints cons1 where cons1.owner = cons.owner and cons1.constraint_type = cons.constraint_type and cons1.constraint_name = cons.constraint_name ) not like '%NOT NULL%' / CONSTRAINT_NAME ------------------------------ GCI_COST_CK
Once again, we have a solution to our problem of finding all of the Check constraints in the current schema that are not simple NOT NULL constraints.
Once again, the solution we have doesn’t feel entirely satisfactory…
If you can do it in PL/SQL…
Coming toward the end of our LONG and winding road, let’s see what PL/SQL would make of all this…
set serveroutput on size unlimited begin for r_cons in ( select constraint_name, table_name, search_condition from user_constraints where constraint_type = 'C' ) loop if r_cons.search_condition not like '%IS NOT NULL' then dbms_output.put_line(r_cons.constraint_name||' on '||r_cons.table_name||' - '||r_cons.search_condition); end if; end loop; end; /
Now, you might expect to hit the same problem as we started with here, namely ORA-00932. However, running this returns :
GCI_COST_CK on GARDEN_CHECKLIST_ITEMS - cost < 50.0 PL/SQL procedure successfully completed.
So, it seems that PL/SQL happily performs an implicit conversion from LONG to VARCHAR2 or CLOB to allow the string comparison to work without error.
This works even if you declare a scalar variable of type long :
set serveroutput on size unlimited declare cursor c_constraints is select constraint_name, table_name, search_condition from user_constraints where constraint_type = 'C'; l_constraint_name user_constraints.constraint_name%type; l_table_name user_constraints.table_name%type; l_search_condition user_constraints.search_condition%type; begin open c_constraints; loop fetch c_constraints into l_constraint_name, l_table_name, l_search_condition; if l_search_condition not like '%IS NOT NULL' then dbms_output.put_line(l_constraint_name||' on '||l_table_name||' - '||l_search_condition); end if; exit when c_constraints%notfound; end loop; close c_constraints; end; /
Other possible solutions
There are other ways of tackling the long problem.
The ever-excellent Adrian Billington has a solution which employs DBMS_XMLGEN, as well as a custom utility, which you may well find useful.
Alternatively, you could take a look at the DBMS_METADATA_UTIL package which contains both a long to varchar2 and long to clob conversion function…although they require you to pass in the name of the base table rather than a view, and supply a rowid…all a bit of a faff for something as simple as the specific problem I’ve looked at here.
Of course, there’s always the option of an open letter to Oracle….
I know you’re probably busy this weekend, sorting out the shed, mowing the lawn, or possibly learning your lines for the next Iron Man movie, but do you think you could take a few moments to get this LONG columns in the Data Dictionary issue sorted ?
I know it’s probably on your “To Do” list and has been for the last 15 years or so.
If it’s going to be too much trouble to solve the problem once and for all, then maybe you might consider an interim solution such as :
- tweaking the TO_LOB function so that it works against LONG columns in a straight select statement
- persuading DBMS_METADATA.GET_DDL to behave itself
Any assistance you could offer would be much appreciated.
Love and Kisses,