SQL Formatter
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Mar 2008 11:17:17 -0700
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
Other related posts:
