Talkin’ ‘Bout My Generation…

With apologies to The Who.

I’ve written before about my obsession with sql.bsq :-)

The genesis of which began during a stint at a customer site where I simply providing on-call support without any specified tasks.  This site made heavy use of Pro*C, with a common problem being making sure that local variables were declared with the same precision as their database column counterparts.

I couldn’t understand why the developers were declaring their variables by hand — didn’t the data dictionary have all of the information to generate this stuff?  And didn’t SQL*Plus have an ability to spool output to a flat file?  How hard could it be to just ask for the table definition and turn it into a .h file?

And so, my first code generation script:

rem
rem SCCS
rem File Name : %W%
rem Check-in Date :
rem SCCS Version : %I%
rem Path : %F%
rem NAME
rem procvars.sql
rem DESCRIPTION
rem Select script that will create Pro*C variable declarations for
rem a given table. All variables except NUMBER are declared as
rem VARCHARs (NUMBERS are declared as long or double, depending on
rem their data scale).
rem
rem This script spools its output to a file with the name
rem [table_name]_vars.h
rem HISTORY
rem Who When Reason
rem —— —— —————————————–
rem Dom 1/10/94 Creation
rem

The interesting thing about this script (I liked source control, even back then), was that it started me down the path of exploring the data dictionary.  I was fascinated by the data dictionary in Oracle 6 — it had EVERYTHING in it.  I’d worked with other databases before (dBase III & IV, SQL/DS), but none of them had anything as detailed as the Oracle data dictionary.  I loved the fact that it was also self-describing — selecting from dba_views gave the names of every view.  To this day, I don’t understand people who have those huge posters of the data dictionary — why would you put up something which would become obsolete so quickly when you could just query the system for the info?

Anyway, the script referenced all_tab_columns, and I wondered why it was so slow — and that’s when I started digging into tab$, col$ and sql.bsq.  I liked going after the base tables instead of the views — and it sped up things considerably.

After that, I began writing scripts (using the same framework) to generate DDL for all kinds of things — since at the time I wasn’t familiar enough with the Unix strings command to pull DDL from Export dump files.

I still have those scripts on my hard drive — they’ve followed me everywhere I go.  Scripts with ALL CAPS names limited to 8 characters long.

MKINDEX.SQL
MKSEQUEN.SQL
MKSYNONY.SQL
MKTABLE.SQL
MKVIEW.SQL

When I first was confronted with the need to maintain source code controlled database scripts, these old scripts became the basis for how I would attack the problem…

Why code stuff by hand when you could generate it?  And not out of thin air either, but based on the actual definition in the system.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder