Doodles

Fortran Notes - Spring'1982

Dave's Surreal Idiom

My friend Dave has a site.. It's the worst thing I've seen on the net... probably ever on the net! It's basically a collection of 'fancy' words and dead links punctuated by a lack of desire. A fact that Dave's proud of! DO NOT GO HERE! If you are willing to waste a 'mouse-click' then click on yet another of my 'strange' sketches. (It's not Dave). It's a cover of an album riddled with lousy songs! Very fitting to link to Dave's Surreal Idiom!

NOTE: Dave has developed a complex Access Database that reflects PeopleSoft HRMS and Mainframe Sales Data. If he really wanted to his site could be awesome! But... No chance of that happening this lifetime!

A 'Strange' Oracle sample

The example SQL*Plus script below is strange in that it serves no useful purpose - Oracle has a command to describe a database table (DESCRIBE) already. But my SQL*Plus script does demonstrate Oracle's Data Dictionary structure as well as the use of Oracle's DECODE function. As a learning tool you may find this example beneficial especially if you're just getting started with Oracle.

Using DECODE to simulate the Oracle DESCRIBE command

/* ***************************************************************** */
/* *                                                               * */
/* *    MODULE: ALLCOL01.SQL                                       * */
/* *    AUTHOR: TONY DELIA.                                        * */
/* *      DATE: 08/27/96.                                          * */
/* *      DESC: ALL TABLE COLUMNS 01.                              * */
/* *     USAGE: start allcol01 <table name> <owner>                * */
/* *                                                               * */
/* ***************************************************************** */

START rptbeg02.sql

TTITLE 'ALL_TAB_COLUMNS Describe      ALLCOL01.SQL'   SKIP 2 -
       'Table: ' ttab                                 SKIP 2

COL htab  NEW_VALUE ttab                              NOPRINT
COL hcol  HEADING 'Name'                              FORMAT A31
COL hnul  HEADING 'Null?'                             FORMAT A8
COL htyp  HEADING 'Type'                              FORMAT A16
COL hid   HEADING 'ID#'                               FORMAT 999
COL hdis  HEADING 'Distinct|Rows'                     FORMAT 999,999

BREAK ON REPORT ON htab SKIP PAGE

COMPUTE COUNT   OF hcol ON REPORT
COMPUTE COUNT   OF hcol ON htab

/* ***************************************************************** */
/* *   Select Table Columns                                        * */
/* ***************************************************************** */

SELECT table_name                                     htab,
       column_name                                    hcol,
       DECODE(nullable,'N','NOT NULL',NULL)           hnul,
       data_type ||
         DECODE(data_type,
           'DATE',NULL,
           'LONG',NULL,
           'NUMBER',
              DECODE(data_precision,
                NULL,DECODE(data_scale,
                       NULL,NULL,'(38)'),
                '(' || TO_CHAR(data_precision) ||
                DECODE(data_scale,
                  NULL,NULL,
                  ','||TO_CHAR(data_scale) ) || ')' ),
           '(' || TO_CHAR(data_length) || ')' )       htyp,
       column_id                                      hid,
       num_distinct                                   hdis
  FROM all_tab_columns
 WHERE table_name LIKE UPPER('&&1')
   AND owner         = UPPER('&&2')
 ORDER BY table_name   ASC,
          column_id    ASC

/* ***************************************************************** */
/

START rptend01.sql

/* ***************************************************************** */
/* *     END OF SQL PROGRAM                                        * */
/* ***************************************************************** */

Sample Output

 
ALL_TAB_COLUMNS Describe      ALLCOL01.SQL

Table: PS_LIFE_ADD_BEN

                                                               Distinct
Name                            Null?    Type              ID#     Rows
------------------------------- -------- ---------------- ---- --------
EMPLID                          NOT NULL VARCHAR2(11)        1
EMPL_RCD#                       NOT NULL NUMBER(38)          2
PLAN_TYPE                       NOT NULL VARCHAR2(2)         3
BENEFIT#                        NOT NULL NUMBER(38)          4
EFFDT                           NOT NULL DATE                5
DEDUCTION_END_DT                         DATE                6
COVERAGE_BEGIN_DT               NOT NULL DATE                7
COVERAGE_END_DT                          DATE                8
COVERAGE_ELECT                  NOT NULL VARCHAR2(1)         9
COVERAGE_ELECT_DT               NOT NULL DATE               10
BENEFIT_PLAN                    NOT NULL VARCHAR2(6)        11
LIFE_ADD_COVRG                  NOT NULL VARCHAR2(1)        12
FLAT_AMOUNT                     NOT NULL NUMBER(38)         13
FACTOR_XSALARY                  NOT NULL NUMBER(4,3)        14
BENEFITS_BASE                   NOT NULL VARCHAR2(1)        15
-------------------------------
                             15

Note

These pages were developed using Microsoft FrontPage. There may be some side-effects when using a browser other than MicroSoft Internet Explorer. An example of this would be smaller font sizes in the above script. This problem has been placed on my backlog... Now that Spring is here things like cutting the lawn comes first...

Tony DeLia - Updated March 19, 1999