Junk DNA - Junk Oracle, part 0000001

Not so many years ago, junk DNA was thought of as the DNA remnants from some evolutionary path, perhaps where we had fewer appendages or organs (nowadays junk DNA is more loosely thought of as DNA that doesn’t code for a protein, that may or may not still have some purpose). The Oracle database is absolutely jam packed with this latter type of software evolutionary junk, junk that I am going to term junk Oracle (and no, it isn’t technical debt). Furthermore this junk even litters Oracle 12c (eg. data types that were deprecated DECADE’s ago) and, well as a developer, I feel quite embarrassed for them considering this is the latest-and-greatest flagship version of their database.

So what is the junk Oracle that that I’m writing about, and how does it impact on me?

There are many examples (the blog article is suffixed with ‘part 0000001’, so expect more writings from me in the future), but here is one.

I had a recent requirement to decommission a database package, where decommission means drop/delete it, tidy things up, and put any functionality that should be retained elsewhere.

The first question I asked myself was – what uses or references this database package (that for the purposes of clarity I will name  pkg_decommission) ? I need this information. I don’t want to break other applications or processes.

Oracle provide an out-of-the-box database VIEW named ALL_SOURCE, the name indicating that it is a view on all source(code) within the database. First impressions – how very handy, and also just what I need.  So, what if anything references the database package, named pkg_decommission, could be retrieved by the following simple query. Right?

SELECT DISTINCT name, type
  FROM all_source
    WHERE UPPER(text) LIKE '%PKG\_DECOMMISSION.%' ESCAPE '\'

Wrong! Or perhaps Yes, or No, or maybe is the better answer.

The view is named ALL_SOURCE, but with typical Oracle clarity, all doesn’t mean ‘all’. What is the English dictionary definition of ‘all’ ? I don’t need to look it up. It means everything, no exclusions, the whole shebang, etc. A 5 year old understands the meaning of the word ‘all’. In Oracle world, the word ‘all’ has been redefined. It doesn’t mean ‘all’ at all (I have deliberately used the word ‘all’ in a different context, just for any pedants reading this blog that might want to pick up on my earlier definition of all). ‘All’ in Oracle actually means ‘Some’.

Really?

Yes, really.

Firstly ALL_SOURCE refers to the text source code that is accessible to the current user, so no privileges means …. no privileges. This seems quite valid, except of course, again, that ‘All’ means ‘Some’, or maybe ‘All’ you are allowed to see. Regardless it is a redefinition of a word with little regard to clarity.

Secondly, and here’s a partial screenshot from the Oracle 10.2 documentation,

and you can see, what!, do I read that right, ALL_SOURCE is for only ‘some’ database objects that contain source code (oh no, I’m using the word ‘Some’ again, and not ‘All’).  Really? This cannot be right. This must be a documentation error, VIEW’s are source code, of course this source code can be found in ALL_SOURCE. I need to do a quick test – I’m losing confidence in ‘some’ or ‘all’ of my English, my mother tongue, and Oracle too.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW foo as
  2    SELECT 1 x,
  3           'Pink Floyd' magicNo
  4     FROM dual;
View created.
SQL>
SQL> SELECT *
  2    FROM all_source
  3      WHERE text LIKE '%Pink Floyd%';
no rows selected
SQL>

Ugh, no Oracle documentation error, and again ‘All’ means ‘Some’, yet this time it has nothing to do with privileges, it’s just …. shoddy, perhaps misleading; both seem appropriate words..  To query if any VIEW’s (forget about SYNONYM’s or other objects that may reference pkg_decommission, that are also excluded from ALL_SOURCE), I will need to approach things differently.

To retrieve the source code for database VIEW’s I will have to query view ALL_VIEWS.

SQL>
SQL>
SQL> DESC all_views
 Name                 Null? Type 
------------------ -------- --------------------------------- 
OWNER              NOT NULL VARCHAR2(30)
VIEW_NAME          NOT NULL VARCHAR2(30)
TEXT_LENGTH                 NUMBER
TEXT                        LONG 
TYPE_TEXT_LENGTH            NUMBER
TYPE_TEXT                   VARCHAR2(4000)
OID_TEXT_LENGTH             NUMBER
OID_TEXT                    VARCHAR2(4000)
VIEW_TYPE_OWNER             VARCHAR2(30)
VIEW_TYPE                   VARCHAR2(30)
SUPERVIEW_NAME              VARCHAR2(30)
EDITIONING_VIEW             VARCHAR2(1)
READ_ONLY                   VARCHAR2(1)
SQL>

Oh damn – I’ve encountered a junk Oracle (ie. junk data types left behind from Oracle’s developmental evolution) in the form of a LONG datatype. LONG is wrong – nowadays you should not be using this datatype, nor need to use this datatype, but instead opt for a LOB of some flavour (not to solely pick out the LONG datatype, there’s still the RAW datatype scattered everywhere within Oracle too – it’s pretty lazy programming to leave this in the database codebase really – and note too this has nothing to do with an upgrade breaking change).  To retrieve the source code for database VIEW’s, and query it to determine references to pkg_decommission, I will have to convert the LONG data into a CLOB. How …. well (and there are utility packages in Oracle that remain for manipulating LONG and RAW‘s too) it can be done like this:

CREATE TABLE tblViewSourceCode AS 
  SELECT owner, view_name, TO_LOB(text) sourceCode
    FROM all_views

(hmmmm, there’s a thought, I wonder if ALL_VIEWS is ‘All’ of the database views. The answer is also No, but at least I now know that All doesn’t mean All, so am cautious about these things).

then

SELECT DISTINCT view_name .... WHERE UPPER(sourceSource) LIKE ....

should be all I need to do to search through the source code for database VIEW’s. So, once all that has been done, and I have looked in a few other places for the database source code, in PL/SQL anyway, I now have all the source code in the database, for PACKAGE‘s, PROCEDURE‘s, FUNCTION‘s, VIEW‘s, TRIGGER‘s, perhaps, I can search it all, and determine what other PSM’s reference pkg_decommission?

Of course this is only half the story – references to pkg_decommission could be stored in table data, references from client applications outside the database or task schedulers such as Control-M.  Putting aside all the junk Oracle, and the misleading use of the word ‘All’ in Oracle, how is it possible to determine what references pkg_decommission? The only guaranteed way is to DROP the package, and see what else breaks. Stay near the phone! The latest and greatest technology my profession uses is indefensible for the 1990’s isn’t it, on multiple fronts? Worst of all I am writing this blog article in 2016.

— Published by Mike, 14:46:12 01 October 2016