SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[9.5.444 Pro] - Serious issue with $COLUMNS$ in Oracle

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[9.5.444 Pro] - Serious issue with $COLUMNS$ in Oracle
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [9.5.444 Pro] - Serious issue with $COLUMNS$ in Oracle Reply with quote
$COLUMNS(vertical, types, keys)$ reports /* Cannot find key columns in xyz */ for tables that definitely have primary keys on them. Sometimes it only misses some of the key columns. e.g., for a table, that has a composite primary key of a varchar2(5) + a date columns (yes, I know, but it's in a legacy database) the macro retrieves its date column, but the varchar2 one is lost somewhere in the process.
Tue Dec 19, 2017 6:21 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Thank you very much for reporting this bug. I have logged it in our bug tracking system.
Tue Dec 19, 2017 10:50 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Would you please provide the DDL for the table that you see the issue with?

We're unable so far to reproduce this issue.
Wed Dec 20, 2017 3:28 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Certainly. Here's the code that was used to retrieve the ddl:
Code:

select sys.dbms_metadata.get_ddl('TABLE', 'VONAL', 'DEMO1') from dual;


And this is the CREATE TABLE for table vonal as retrieved by the above code:
Code:

CREATE TABLE "DEMO1"."VONAL"
   (   "VONAL_ID" VARCHAR2(5) NOT NULL ENABLE,
   "DATUM_TOL" DATE NOT NULL ENABLE,
   "VAROS_ID" NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,
   "VONAL_NEV" VARCHAR2(140) DEFAULT '' NOT NULL ENABLE,
   "AKTIV_E" NUMBER(1,0) DEFAULT 1 NOT NULL ENABLE,
   "DATUM_IG" DATE,
    CONSTRAINT "VONAL_ID" PRIMARY KEY ("VONAL_ID", "DATUM_TOL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO"  ENABLE,
    CONSTRAINT "UK_VONAL__VONAL_ID_DATUM_IG" UNIQUE ("VONAL_ID", "DATUM_IG")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DEMO"   NO INMEMORY     


I'm sure there are tons of irrelevant stuff in this code but the part
Code:

CONSTRAINT "VONAL_ID" PRIMARY KEY ("VONAL_ID", "DATUM_TOL")

is clear.

Both $COLUMNS(vertical,types,keys)$ and $COLUMNS(vertical,type_classes,keys)$ return
Code:

datum_tol DATE

for this table.
Wed Dec 20, 2017 4:54 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Thank you. It appears that one of PK columns is also member of unique constraint, and that's related to the issue.

Please take a look at the Options, see "Columns (Oracle) + Keys" query. There is a subquery that checks whether a column is referenced in a constraint. That feature is limited to one constraint type only. Whichever constraint comes first is used as a flag for the column / constraint reference. This handling is not specific to Oracle, it does the same for all database interfaces. The internal cache has room just for one constraint type flag.

If you feel like primary keys are always more important than other constraints, you can modify that query and add "ORDER BY c.constraint_type" clause to the subquery
Wed Dec 20, 2017 6:46 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Well, it was not as simple as adding the ORDER BY to the subquery, I've had my battle with "missing right parenthesis" :) But now it seems to work, and as a side-effect, I've learnt a lot about how Oracle handles subqueries. Thank you very much for the hint.
Fri Dec 22, 2017 10:14 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7839

Post Reply with quote
Glad you have found a working solution. As an alternative to ORDER BY in the subquery, it could have been changed to SELECT min(constraint_type) after removing WHERE rownum =1, that way 'P' type would alway come first.
Fri Dec 22, 2017 11:46 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.