Hi Team,
I have noticed that when you query reference object views which having cascade definitions in that case if we try to navigate through reference by clicking on it its going up to first level only. Earlier in old toad versions less than version 10 it was working fine. Looks like some thing broke after that. Appreciate if you can fix this behavior as we are using highly object oriented features this will be helpful.
Regards,
Mahesh K
Can you give us a script to create some objects in the database so we can reproduce it?
DROP VIEW vw_address;
DROP VIEW vw_city;
DROP VIEW vw_country;
DROP VIEW vw_region;
DROP VIEW vw_street;
DROP TYPE bs_address;
DROP TYPE bs_street;
DROP TYPE bs_city;
DROP TYPE bs_region;
DROP TYPE bs_country;
DROP TABLE tn_country CASCADE CONSTRAINTS;
DROP TABLE tn_region CASCADE CONSTRAINTS;
DROP TABLE tn_city CASCADE CONSTRAINTS;
DROP TABLE tn_street CASCADE CONSTRAINTS;
DROP TABLE tn_address CASCADE CONSTRAINTS;
CREATE TABLE tn_country(pkey NUMBER(16) NOT NULL, uniqueid VARCHAR2(5 BYTE) NOT NULL, name VARCHAR2(40 BYTE))
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
CREATE TABLE tn_region(pkey NUMBER(16) NOT NULL, uniqueid VARCHAR2(10 BYTE) NOT NULL, name VARCHAR2(40 BYTE), fkey_country NUMBER(16))
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
CREATE TABLE tn_city(pkey NUMBER(16) NOT NULL, name VARCHAR2(40 BYTE) NOT NULL, fkey_region NUMBER(16))
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
CREATE TABLE tn_street(pkey NUMBER(16) NOT NULL, name VARCHAR2(40 BYTE) NOT NULL, fkey_city NUMBER(16))
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
CREATE TABLE tn_address(pkey NUMBER(16) NOT NULL, fkey_street NUMBER(16), zipcode VARCHAR2(10 BYTE), house VARCHAR2(10 BYTE),
addition VARCHAR2(40 BYTE), geoquality VARCHAR2(6 BYTE), longitude FLOAT(126), latitude FLOAT(126))
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
ALTER TABLE tn_country
ADD(CONSTRAINT pkey_country PRIMARY KEY(pkey) ENABLE VALIDATE);
ALTER TABLE tn_region
ADD(CONSTRAINT pkey_region PRIMARY KEY(pkey) ENABLE VALIDATE);
ALTER TABLE tn_city
ADD(CONSTRAINT pkey_city PRIMARY KEY(pkey) ENABLE VALIDATE);
ALTER TABLE tn_street
ADD(CONSTRAINT pkey_street PRIMARY KEY(pkey) ENABLE VALIDATE);
ALTER TABLE tn_address
ADD(CONSTRAINT pkey_address PRIMARY KEY(pkey) ENABLE VALIDATE);
ALTER TABLE tn_region
ADD(CONSTRAINT fkey_region_country FOREIGN KEY(fkey_country) REFERENCES tn_country (pkey) ENABLE VALIDATE);
ALTER TABLE tn_city
ADD(CONSTRAINT fkey_city_country FOREIGN KEY(fkey_region) REFERENCES tn_region (pkey) ENABLE VALIDATE);
ALTER TABLE tn_street
ADD(CONSTRAINT fkey_street_city FOREIGN KEY(fkey_city) REFERENCES tn_city (pkey) ENABLE VALIDATE);
ALTER TABLE tn_address
ADD(CONSTRAINT fkey_addrebs_street FOREIGN KEY(fkey_street) REFERENCES tn_street (pkey) ENABLE VALIDATE);
CREATE OR REPLACE TYPE bs_country AS OBJECT(pkey NUMBER(16), uniqueid VARCHAR2(5), name VARCHAR2(40));
/
CREATE OR REPLACE TYPE bs_region AS OBJECT(pkey NUMBER(16), uniqueid VARCHAR2(5), name VARCHAR2(40), country REF bs_country);
/
CREATE OR REPLACE TYPE bs_city AS OBJECT(pkey NUMBER(16), name VARCHAR2(40), region REF bs_region);
/
CREATE OR REPLACE TYPE bs_street AS OBJECT(pkey NUMBER(16), name VARCHAR2(40), city REF bs_city);
/
CREATE OR REPLACE TYPE bs_address AS OBJECT(pkey NUMBER, street REF bs_street, zipcode VARCHAR2(10), house VARCHAR2(10),
addition VARCHAR2(40), geoquality VARCHAR2(6), longitude FLOAT, latitude FLOAT);
/
CREATE OR REPLACE FORCE VIEW vw_address OF bs_address
WITH OBJECT IDENTIFIER (pkey)
AS
SELECT t.pkey, make_ref(vw_street, t.fkey_street), t.zipcode, t.house,
t.addition, t.geoquality, t.longitude, t.latitude
FROM tn_address t;
CREATE OR REPLACE FORCE VIEW vw_city OF bs_city
WITH OBJECT IDENTIFIER (pkey)
AS
SELECT t.pkey, t.name, make_ref(vw_region, t.fkey_region)
FROM tn_city t;
CREATE OR REPLACE FORCE VIEW vw_country OF bs_country
WITH OBJECT IDENTIFIER (pkey)
AS
SELECT pkey, uniqueid, name FROM tn_country;
CREATE OR REPLACE FORCE VIEW vw_region OF bs_region
WITH OBJECT IDENTIFIER (pkey)
AS
SELECT t.pkey, t.uniqueid, t.name, make_ref(vw_country, t.fkey_country)
FROM tn_region t;
CREATE OR REPLACE FORCE VIEW vw_street OF bs_street
WITH OBJECT IDENTIFIER (pkey)
AS
SELECT t.pkey, t.name, make_ref(vw_city, t.fkey_city)
FROM tn_street t;
INSERT INTO tn_country
VALUES ('1', 'US', 'United State');
INSERT INTO tn_region
VALUES ('1', 'HARRIS', 'Harris County', 1);
INSERT INTO tn_city
VALUES ('1', 'HOUSTON', '1');
INSERT INTO tn_street
VALUES ('1', 'OLD ROAD', '1');
INSERT INTO tn_address
VALUES ('1', '1', '77063', '86956',
NULL, NULL, NULL, NULL);
COMMIT;
SELECT * FROM vw_address;
Oh, I see....so you double-click in the STREET cell, then it opens a window and you want to double-click in CITY after that but nothing happens when you do.
Thanks, I'll investigate
Thank you for the script. It is fixed in next beta.
Could you please provide me beta version?
You can get it here, but you must also have Toad version 13.1 installed or the beta will not run.
I am getting error This version of Toad for Oracle has expired. You must download new version before proceeding. I do have updated version installed on my machine still I am getting this error can you help me out here to check this.
after you download the beta zip file, run the .msi file first to install the beta, then the .msi file to update it to most recent.
Thanks let me check.
oops, I said that wrong...first the .msi to install, then the .MSP to patch.
Thats ok I got it right. I have one more bug to report I will send details soon preparing sample script.
I got information from your team that you have fixed type comparison from one system to another that will generate alter statement for sync script. But with this beta version I do see that issue still exists when I compare two type I am getting entire create or replace type script which I can not run I was expecting I should get alter type script similar to Table compare. Let me know update on this.
Yes, that was me who made that change. Sometimes it will still do create or replace, it depends on how the source schema was altered. Here's an example:
If the type in the SOURCE schema is like this:
CREATE TYPE Cust_Address_Type
AS OBJECT
( street_address VARCHAR2(40) )
/
and then later, the source is modified with
ALTER TYPE Cust_Address_Type
ADD ATTRIBUTE city VARCHAR2(30);
Now, the source for Cust_address_types (in DBA_SOURCE) shows the "Alter" embedded in the source, like this:
CREATE TYPE Cust_Address_Type
AS OBJECT
( street_address VARCHAR2(40) )
ALTER TYPE Cust_Address_Type
ADD ATTRIBUTE city VARCHAR2(30)
/
So, if the TARGET schema has
CREATE TYPE Cust_Address_Type
AS OBJECT
( street_address VARCHAR2(40) )
/
...then Toad will use the same ALTER that was used on the SOURCE and apply it to the target.
But if source and target are just different - like this:
(source)
CREATE TYPE Cust_Address_Type
AS OBJECT
( street_address VARCHAR2(40),
city VARCHAR2(40),
state VARCHAR2(40) )
/
(target)
CREATE TYPE Cust_Address_Type
AS OBJECT
( street_address VARCHAR2(40) )
/
...then Toad will just do a 'CREATE OR REPLACE' on the target, as that is what was done in the source. The main reason for this is that this is the only way that the target can be made to be identical to the source.
There can be other things in there like comments and "restrict reference" clauses that can't be used in ALTER to make the target look just like the source.
By the way, in your support case, you mentioned that "create or replace" can break the database structure/model of types and dependencies. In my tests, the worst thing that happened is Oracle gave an ORA-02303 and didn't make any changes. Can you provide some example of how things break?
I tested this on your beta version but it is going upto CITY when I click on REGION going blank where as we have data for REGION. Check one more time.
Yes, I see what you mean with Region being null. That seems like a different bug, I'll log it and let you know when it is fixed.
I don't think the problem is in the popup, it seems to be in the data somehow.
The REGION column if VW_CITY shows as (Reference). In all of the other views, it shows as (REFERENCE). Lowercase designates NULL, uppercase designates non-null. Looking at the script you gave, I don't see why this is happening though.
There is a mistake in your script. You have Varchar2(5) for uniqueid here and it should be Varchar2(10). Too bad Oracle didn't give you an error message!
Make this change and then there is data for REGION.
CREATE OR REPLACE TYPE bs_region AS OBJECT(pkey NUMBER(16), uniqueid VARCHAR2(10), name VARCHAR2(40), country REF bs_country);
/
That's correct, but still seems to be strange to me why then below query returning output
SELECT a.street.city.region.uniqueid FROM vw_address a;
This one not returning anything
SELECT a.street.city.region FROM vw_address a;
I don't know, but that's on Oracle's side. Toad just displays the data that Oracle provides.
