The following SQL formatting behaves unexpectedly when using the Whole SQL Lowercase option with explicit table name:

CREATE TABLE PWRPLANT."MIGRATION_TABLES" (
"TABLE_ID" NUMBER(22,0) NOT NULL
, "TABLE_GROUP" VARCHAR2(31) NOT NULL
, "TABLE_NAME" VARCHAR2(31) NOT NULL
, "WHERE_CLAUSE" VARCHAR2(4000)
, "ORDER_BY_CLAUSE" VARCHAR2(4000)
, "PROCESS_ORDER" NUMBER(22,0) NOT NULL
, "TABLE_GRP_SORT_ORDER" NUMBER(22,0) NOT NULL
, "ACTIVE_STATUS" NUMBER(1,0) DEFAULT 1
, "TIME_STAMP" DATE
, "USER_ID" VARCHAR2(35)
) ;

Expected results would look like the following:

create table pwrplant."MIGRATION_TABLES"
	(
	   "TABLE_ID"             number (22, 0) not null
	 , "TABLE_GROUP"          varchar2 (31) not null
	 , "TABLE_NAME"           varchar2 (31) not null
	 , "WHERE_CLAUSE"         varchar2 (4000)
	 , "ORDER_BY_CLAUSE"      varchar2 (4000)
	 , "PROCESS_ORDER"        number (22, 0) not null
	 , "TABLE_GRP_SORT_ORDER" number (22, 0) not null
	 , "ACTIVE_STATUS"        number (1, 0) default 1
	 , "TIME_STAMP"           date
	 , "USER_ID"              varchar2 (35)
	) ;

Additionally, I would assume that most of Oracle's explicit references should remain unaltered if qualified by double-quotes. Otherwise, this could potentially cause confusion with case-sensitive names of objects.



Edited 1 time(s). Last edit at 12/27/2010 01:10PM by guidomarcel.

I just noticed this also applies to ALTER and UPDATE statements as well.

Guido, have you had a chance to look at this issue?

Thanks

Hi Jcrane,
thanks for the message. Which release are you working with? This is what I get:

create table pwrplant."migration_tables"
             (
                          "TABLE_ID"             number(22,0) not null ,
                          "TABLE_GROUP"          varchar2(31) not null ,
                          "TABLE_NAME"           varchar2(31) not null ,
                          "WHERE_CLAUSE"         varchar2(4000)        ,
                          "ORDER_BY_CLAUSE"      varchar2(4000)        ,
                          "PROCESS_ORDER"        number(22,0) not null ,
                          "TABLE_GRP_SORT_ORDER" number(22,0) not null ,
                          "ACTIVE_STATUS"        number(1,0) default 1 ,
                          "TIME_STAMP"           date                  ,
                          "USER_ID"              varchar2(35)
             ) ;

Only thing I can see to be improved is "."MIGRATION_TABLES"

Correct?



Edited 1 time(s). Last edit at 07/16/2010 01:13PM by guidomarcel.

I'm using the most current version online for my example.

You are correct. In Oracle, that lowercase table name could cause problems if qualified in lowercase upon creation. At the very least, the ALTER, CREATE and UPDATE statements all have this issue.

Hi Jcrane,
I found the issue and will fix it with the next release. Thanks for the hint.
Regards
Guido