I hate longs

Now long's have been around since Oracle was running around in short trousers and while not really used by anyone any more they are still there in the dictionary. Aaargh. I guess Oracle have just decided it's not worth messing around with - the whole dictionary stuff just works why mess around with it - and to be honest that's probably the view i would take too.

So this week one of our testers has been wanting to build something that lets us compare the code installed in many of the different environments by extracting ddl definitions from all of them and storing it a central repository.

Now there is more than one approach to this but the way he chose to do it was to create a db link to each database and just pull the definitions over that link and store them. This is all fine until you start trying to do it for views like DBA_VIEWS where the view definition is stored in a long..... and we want to store the result in varchar columns.

We tried a few different ways of doing this but there seemed to be an issue in every step. Longs and db links (and in fact lobs and db links have quite a few limitations......)

In the end we came up with this approach which uses a built in package sys.dbms_metadata_util (one that i've rarely used before - but it does seem to have some useful stuff in it).

The following code extracts the view text in varchar format from a remote database

select db.owner,
db.view_name,
sys.dbms_metadata_util.long2varchar@dblink(4000,'VIEW$','TEXT',sy.rowid)
from sys.view$@dblinksy,
dba_objects@dblinkobj,
 dba_views@dblinkdb
where obj.owner=db.owner
and obj.object_name=db.view_name
and obj.object_id=sy.obj#

However..... this only works up to 4000 bytes - after that the code works but returns null for the text - to cope with longer fields there is another function called long2vcmax which returns an array of varchar2 which you'd have to store as separate rows - however this works the opposite way - anything less than 4000 returns null......

Anyway - i think this does have it's uses

I just wish longs and clobs were easier to work with sometimes.....

Update - here is a query for constraints too.....

select db.OWNER,
       CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       TABLE_NAME,
       sys.dbms_metadata_util.long2varchar@dblink(4000,
                                                        'cdef$',
                                                        'CONDITION',
                                                        cdef.rowid),
       R_OWNER,
       R_CONSTRAINT_NAME,
       DELETE_RULE,
       db.STATUS,
       DEFERRABLE,
       DEFERRED,
       VALIDATED,
       db.GENERATED,
       BAD,
       RELY,
       LAST_CHANGE,
       INDEX_OWNER,
       INDEX_NAME,
       INVALID,
       VIEW_RELATED
  from dba_constraints@dblink db,
       sys.cdef$@dblink cdef,
       sys.con$@dblink    con,
       dba_users@dblink usr   
 where con.con# = cdef.con#
       and db.constraint_name=con.name
       and usr.user_id=con.owner#
       and usr.username=db.owner


and another update for triggers

select db.OWNER,
       TRIGGER_NAME,
       TRIGGER_TYPE,
       TRIGGERING_EVENT,
       TABLE_OWNER,
       BASE_OBJECT_TYPE,
       TABLE_NAME,
       COLUMN_NAME,
       referencing_names,
       when_clause,
       db.status,
       description,
       action_type,
       sys.dbms_metadata_util.long2varchar@dblink(4000,
                                                        'trigger$',
                                                        'action#',
                                                        tr.rowid),
       crossedition,
       BEFORE_STATEMENT,
       before_row,
       after_row,
       after_statement,
       instead_of_row,
       fire_once,
       apply_server_only
  from dba_triggers@dblink db,
       sys.trigger$@dblink tr,
       dba_users@dblink us,
       dba_objects@dblink obj
 where 
       us.username = db.owner
       and tr.obj# = obj.OBJECT_ID
       and obj.OBJECT_NAME=db.trigger_name

       and db.owner=obj.owner