Mike Schaeffer's Blog

Articles with tag: sql
May 15, 2007

The other day, I created a table in Oracle with the following command. This is written in the subset of SQL known as DDL, or Data Defintion Language.

CREATE TABLE COMMON.SAMPLE_TABLE (
  NAME      VARCHAR(64) NOT NULL,
  STATUS    CHAR(1),
  X_C       NUMBER (10),
  Y_C       NUMBER (*,10) NOT NULL, 
  Z_C       NUMBER (*,10),
  FOO       VARCHAR2 (18) NOT NULL, 
  BAR       DATE,
  BAZ       TIMESTAMP
 )
/

Once the table is created, it is then possible to ask the database to describe the table:

common@XE> desc COMMON.SAMPLE_TABLE;
 Name                          Null?    Type
 ----------------------------- -------- ----------------------------
 NAME                          NOT NULL VARCHAR2(64)
 STATUS                                 CHAR(1)
 X_C                                    NUMBER(10)
 Y_C                           NOT NULL NUMBER(38,10)
 Z_C                                    NUMBER(38,10)
 FOO                           NOT NULL VARCHAR2(18)
 BAR                                    DATE
 BAZ                                    TIMESTAMP(6)

For some reason, the syntax of Oracle's description of the table's definition is entirely different than the syntax of the DDL used to define the table in the first place. Not only does the description not use DDL, minor details are different too. For example, the relative placement of the nullability (NOT NULL) of a column and its data type is reversed from one representation to the other. This makes converting a table description into corresponding DDL a trickier process than it would be otherwise. Another difference (loss?) is that the DDL syntax allows for table specific attributes and the description syntax does not. That means that the table's full description really might look something like this:

CREATE TABLE COMMON.SAMPLE_TABLE (
  NAME      VARCHAR(64) NOT NULL,
  STATUS    CHAR(1),
  X_C       NUMBER (10),
  Y_C       NUMBER (*,10) NOT NULL, 
  Z_C       NUMBER (*,10),
  FOO       VARCHAR2 (18) NOT NULL, 
  BAR       DATE,
  BAZ       TIMESTAMP
 )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
/

So, if you rely on a table description as the basis for creating a duplicate copy of a table, you not only have to do specific work to convert the description from description syntax to DDL, the DDL you end up with will likely be incomplete. While I am sure that there is an excellent reason for the syntactic split between the two types of table descriptions, I honesly cannot think of it. My current best theory is that SQL*Plus and SQLNET cannot handle non-table returns from a database request. Because of this, the table description has to itself be a table. You could even make the argument that this is the 'right' way to do things, since it gives you a table description in a form (a table) that database code should easily be able to manipulate. However, the description is itself incomplete, so I'm not sure how useful that explanation is.

I'm not a database guru, but it seems like another way to handle this possible limitation is to have the table description query return a one row, one column table with a BLOB or VARCHAR2 containing the DDL description. SQL*Plus could then special case the display of this query to make it look nice on the screen. (SQL*Plus already does special case desc queries, since their display does not honor calls to SET PAGESIZE. If you really do need table information in tabular form, there are always the ALL_TABLES and ALLTABCOLS views. (Of course, a really wonderful solution to all of this would be to make those views writable, somehow standardize them, and then skip the DDL entirely. :-)