Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

June 6, 2008

DATABASE is my home and SQL is my path

Filed under: Oracle How To — H.Tonguç Yılmaz @ 1:10 pm

Excel is one of the all time best tools one can develop, I totally agree.

But if you are a database developer and like benefiting from the power of SQL for any kind of your daily need, you also most probably try to accomplish whatever you do inside the your database. For example you also prefer DBMS_SCEDULER to unix cron, in order to query whatever happened by simple SQLs over ALL_SCHEDULER_JOB_LOG and ALL_SCHEDULER_JOB_RUN_DETAILS. Or you also use V$RMAN_BACKUP_JOB_DETAILS to query your RMAN backup logs, not telnet and grep error messages frmo the unix log files.

This morning I was lost inside an Excel document which was designed to guide a warehouse builder developer to map the Siebel source tables and columns. So I decided to attach the information I hardly mined onto my target tables’ and columns’ comments for future just in case :)


create table  SBL_ASSET  (
   ASSET_ID             VARCHAR2(60),
   GSM                  VARCHAR2(200),
   ACCOUNT_ID           VARCHAR2(60), 
[truncated]
   NPRD                 VARCHAR2(400)  
) PCTFREE 10 PCTUSED 89 
[truncated]
NOLOGGING PARALLEL 8 ;

comment on table  SBL_ASSET is 'S_ASSET, S_PROD_INT' ;
comment on column SBL_ASSET.ASSET_ID is 'S_ASSET.ROW_ID' ; 
comment on column SBL_ASSET.GSM is 'S_ASSET.X_GSM_NUMBER' ;
comment on column SBL_ASSET.ACCOUNT_ID is 'S_ASSET.OWNER_ACCNT_ID' ;
[truncated]
comment on column SBL_ASSET.NPRD is 'S_ASSET.ASSET_NUM' ; 
    

So what is the good for all of these extra commenting? Here is the catch, with a simple query now anyone who can SQL will get the mapping informations;


SELECT a.column_name || ',' column_name, a.comments || ',' comments
  FROM dba_col_comments a
 WHERE a.owner = 'ODS'
   AND a.table_name IN ('SBL_ASSET');
 
COLUMN_NAME                     COMMENTS
------------------------------- --------------------------------------------------------------------------------
ASSET_ID,                       S_ASSET.ROW_ID,
GSM,                            S_ASSET.X_GSM_NUMBER,
ACCOUNT_ID,                     S_ASSET.OWNER_ACCNT_ID,
[truncated]
NPRD,                           S_ASSET.ASSET_NUM,  
   

For example you can consume this information in order to build a simple OWB map or SQL loading script, just like PL/SQL commenting; on database documentation.


TRUNCATE TABLE ODS.SBL_ASSET REUSE STORAGE ;

INSERT /*+ APPEND */ INTO ods.sbl_asset
   (asset_id, gsm, account_id, 
[truncated]
   nprd)
   SELECT s_asset.row_id,
          s_asset.x_gsm_number,
          s_asset.owner_accnt_id,
[truncated]
          s_asset.asset_num
     FROM src_siebel.s_asset, src_siebel.s_prod_int
    WHERE s_asset.prod_id = s_prod_int.row_id
      AND s_prod_int.name = 'TC_PRODUCT';

COMMIT;   
    

Like Tom Kyte advices, we, database developers, do things “in” the database. ;)

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: