SQL Formatter

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 25 Mar 2008 11:17:17 -0700
Message-ID: <bf46380803251117i6a6fcb2wed2174fefd976fc0@mail.gmail.com>


It is not too unusual to see post here that are largely ignored for a couple reasons.

  1. The post was hastily typed and the intent is unclear. All too often there is not a concise question asked at the end of the post.
  2. A lot of SQL or trace file output is included, and no effort was made to format it.

Others may not agree with this, but personally, I generally ignore posts that require
me to reformat the SQL or trace file so that I can read it.

There is help for formatting SQL, and it is quite easy to use.

http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl

Paste your SQL into the top box, press <format>, and retrieve the formatted SQL from
the lower box.

To use a recent unreadable example, this:

CREATE GLOBAL TEMPORARY TABLE PS_DEPR_RP4_TMP ( "ASSET_ID" VARCHAR2(12 byte) NOT NULL,

"PROCESS_INSTANCE" NUMBER(10) NOT NULL,
"BUSINESS_UNIT"  VARCHAR2(5 byte) NOT NULL,
"BOOK" VARCHAR2(10 byte) NOT NULL,
"CF_SEQNO" NUMBER NOT NULL,
"FISCAL_YEAR" NUMBER NOT NULL,
"ACCOUNTING_PERIOD" NUMBER NOT NULL,
"TRANS_TYPE" VARCHAR2(3     byte) NOT NULL,
"TRANS_DT" DATE, "DTTM_STAMP" DATE,
"PROJECT_ID" VARCHAR2(15 byte) NOT NULL,
"DEPTID" VARCHAR2(10    byte) NOT NULL,
"CATEGORY" VARCHAR2(5 byte) NOT NULL,
"COST_TYPE" VARCHAR2(1 byte) NOT NULL,
"OPERATING_UNIT"    VARCHAR2(8 byte) NOT NULL,
"PRODUCT" VARCHAR2(6 byte) NOT     NULL,
"FUND_CODE" VARCHAR2(5 byte) NOT NULL,
"CLASS_FLD"     VARCHAR2(5 byte) NOT NULL,
"PROGRAM_CODE" VARCHAR2(5 byte)    NOT NULL,
"BUDGET_REF" VARCHAR2(8 byte) NOT NULL,
"AFFILIATE"    VARCHAR2(5 byte) NOT NULL, "AFFILIATE_INTRA1" VARCHAR2(10

    byte) NOT NULL, "AFFILIATE_INTRA2" VARCHAR2(10 byte) NOT NULL,     "CHARTFIELD1" VARCHAR2(10 byte) NOT NULL, "CHARTFIELD2"     VARCHAR2(10 byte) NOT NULL, "CHARTFIELD3" VARCHAR2(10 byte)     NOT NULL, "ACTIVITY_SW" NUMBER NOT NULL, "RETIRE_SW" NUMBER     NOT NULL, "DEPR" NUMBER(26, 3) NOT NULL, "DEPR_YTD" NUMBER(26,     3) NOT NULL, "DEPR_LTD" NUMBER(26, 3) NOT NULL, "CURRENCY_CD"     VARCHAR2(3 byte) NOT NULL, "MANUAL_DEPR_TYPE" VARCHAR2(1     byte) NOT NULL, "SPEC_DEPR" NUMBER(26, 3) NOT NULL,     "INITIAL_DEPR" NUMBER(26, 3) NOT NULL, "ACCEL_DEPR" NUMBER(26,     3) NOT NULL, "INCREASE_DEPR" NUMBER(26, 3) NOT NULL,     "STANDARD_DEPR" NUMBER(26, 3) NOT NULL, "COST" NUMBER(26, 3)     NOT NULL, "SALVAGE_VALUE" NUMBER(26, 3) NOT NULL, "GRP_COST"     NUMBER(26, 3) NOT NULL, "GRP_SALVAGE_VALUE" NUMBER(26, 3) NOT     NULL, "DEPR_PDP" NUMBER(26, 3) NOT NULL, "ACCUM_DEPR_ADD"     NUMBER(26, 3) NOT NULL, "ACCUM_DEPR_TRF" NUMBER(26, 3) NOT     NULL, "ACCUM_DEPR_RCT" NUMBER(26, 3) NOT NULL,     "ACCUM_DEPR_RET" NUMBER(26, 3) NOT NULL, "ACCUM_DEPR_REI"     NUMBER(26, 3) NOT NULL, "COST_ADD" NUMBER(26, 3) NOT NULL,     "COST_ADJ" NUMBER(26, 3) NOT NULL, "COST_TRF" NUMBER(26, 3)     NOT NULL, "COST_RCT" NUMBER(26, 3) NOT NULL, "COST_RET"     NUMBER(26, 3) NOT NULL, "COST_REI" NUMBER(26, 3) NOT NULL,     "ACCOUNT_AD" VARCHAR2(10 byte) NOT NULL, "ACCOUNT_FA"     VARCHAR2(10 byte) NOT NULL, "LOCATION" VARCHAR2(10 byte) NOT     NULL, "PROFILE_ID" VARCHAR2(10 byte) NOT NULL,     "GROUP_ASSET_FLAG" VARCHAR2(1 byte) NOT NULL, "TRANS_CODE"     VARCHAR2(5 byte) NOT NULL)

Becomes this:

CREATE GLOBAL TEMPORARY TABLE ps_depr_rp4_tmp (
"ASSET_ID" VARCHAR2(12 BYTE) NOT NULL,
"PROCESS_INSTANCE" NUMBER(10) NOT NULL,
"BUSINESS_UNIT" VARCHAR2(5 BYTE) NOT NULL,
"BOOK" VARCHAR2(10 BYTE) NOT NULL,
"CF_SEQNO" NUMBER NOT NULL,
"FISCAL_YEAR" NUMBER NOT NULL,
"ACCOUNTING_PERIOD" NUMBER NOT NULL,
"TRANS_TYPE" VARCHAR2(3 BYTE) NOT NULL,
"TRANS_DT" DATE,
"DTTM_STAMP" DATE,
"PROJECT_ID" VARCHAR2(15 BYTE) NOT NULL,
"DEPTID" VARCHAR2(10 BYTE) NOT NULL,
"CATEGORY" VARCHAR2(5 BYTE) NOT NULL,
"COST_TYPE" VARCHAR2(1 BYTE) NOT NULL,
"OPERATING_UNIT" VARCHAR2(8 BYTE) NOT NULL,
"PRODUCT" VARCHAR2(6 BYTE) NOT NULL,
"FUND_CODE" VARCHAR2(5 BYTE) NOT NULL,
"CLASS_FLD" VARCHAR2(5 BYTE) NOT NULL,
"PROGRAM_CODE" VARCHAR2(5 BYTE) NOT NULL,
"BUDGET_REF" VARCHAR2(8 BYTE) NOT NULL,
"AFFILIATE" VARCHAR2(5 BYTE) NOT NULL,
"AFFILIATE_INTRA1" VARCHAR2(10 BYTE) NOT NULL,
"AFFILIATE_INTRA2" VARCHAR2(10 BYTE) NOT NULL,
"CHARTFIELD1" VARCHAR2(10 BYTE) NOT NULL,
"CHARTFIELD2" VARCHAR2(10 BYTE) NOT NULL,
"CHARTFIELD3" VARCHAR2(10 BYTE) NOT NULL,
"ACTIVITY_SW" NUMBER NOT NULL,
"RETIRE_SW" NUMBER NOT NULL,
"DEPR" NUMBER(26,3) NOT NULL,
"DEPR_YTD" NUMBER(26,3) NOT NULL,
"DEPR_LTD" NUMBER(26,3) NOT NULL,
"CURRENCY_CD" VARCHAR2(3 BYTE) NOT NULL,
"MANUAL_DEPR_TYPE" VARCHAR2(1 BYTE) NOT NULL,
"SPEC_DEPR" NUMBER(26,3) NOT NULL,
"INITIAL_DEPR" NUMBER(26,3) NOT NULL,
"ACCEL_DEPR" NUMBER(26,3) NOT NULL,
"INCREASE_DEPR" NUMBER(26,3) NOT NULL,
"STANDARD_DEPR" NUMBER(26,3) NOT NULL,
"COST" NUMBER(26,3) NOT NULL,
"SALVAGE_VALUE" NUMBER(26,3) NOT NULL,
"GRP_COST" NUMBER(26,3) NOT NULL,
"GRP_SALVAGE_VALUE" NUMBER(26,3) NOT NULL,
"DEPR_PDP" NUMBER(26,3) NOT NULL,
"ACCUM_DEPR_ADD" NUMBER(26,3) NOT NULL,
"ACCUM_DEPR_TRF" NUMBER(26,3) NOT NULL,
"ACCUM_DEPR_RCT" NUMBER(26,3) NOT NULL,
"ACCUM_DEPR_RET" NUMBER(26,3) NOT NULL,
"ACCUM_DEPR_REI" NUMBER(26,3) NOT NULL,
"COST_ADD" NUMBER(26,3) NOT NULL,
"COST_ADJ" NUMBER(26,3) NOT NULL,
"COST_TRF" NUMBER(26,3) NOT NULL,
"COST_RCT" NUMBER(26,3) NOT NULL,
"COST_RET" NUMBER(26,3) NOT NULL,
"COST_REI" NUMBER(26,3) NOT NULL,
"ACCOUNT_AD" VARCHAR2(10 BYTE) NOT NULL,
"ACCOUNT_FA" VARCHAR2(10 BYTE) NOT NULL,
"LOCATION" VARCHAR2(10 BYTE) NOT NULL,
"PROFILE_ID" VARCHAR2(10 BYTE) NOT NULL,
"GROUP_ASSET_FLAG" VARCHAR2(1 BYTE) NOT NULL,
"TRANS_CODE" VARCHAR2(5 BYTE) NOT NULL)
Not perfect, but *much* easier to read.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 25 2008 - 13:17:17 CDT

Original text of this message