Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
Generation of change scripts for object types is invalid
Description
Toad generates invalid change scripts for object types. Steps to reproduce the issue:
create 2 empty schemas for comparison: in this example, COMP1 (target) and COMP2 (Source)
run this in COMP1
CREATE OR REPLACE TYPE my_type AS OBJECT ( id NUMBER, name VARCHAR2(100) );
create table my_type_table of my_type; run this in the other COMP2
CREATE OR REPLACE TYPE my_type AS OBJECT ( id NUMBER, name VARCHAR2(100) );
create table my_type_table of my_type;
ALTER TYPE my_type ADD ATTRIBUTE (email VARCHAR2(100)) cascade; Note: Oracle tracks the changes to the TYPE in DBA_TYPE_VERSIONS. There you can see the versions of the type before and after the ALTER TYPE statement. Toad uses the version info here to compare and sync
Compare them using COMP2 as source and COMP1 as target.
The only object types you need to include are tables and types
The sync script looks like below.
ALTER TYPE COMP1.my_type ADD ATTRIBUTE (email VARCHAR2(100)) cascade;
CREATE OR REPLACE TYPE COMP1.my_type AS OBJECT ( id NUMBER, name VARCHAR2(100) ) ALTER TYPE my_type ADD ATTRIBUTE (email VARCHAR2(100)) cascade /
SHOW ERRORS;
ALTER TABLE COMP1.MY_TYPE_TABLE ADD (EMAIL VARCHAR2(100 BYTE));
Bug: The only valid statement here is the first one.
The “Create or replace type” is unnecessary because we already altered it.
“Show errors” is actually harmless.
The “Alter table” is invalid because you can’t add columns to type tables.
Sign In Required
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
Your Request will be reviewed by our technical reviewer team and, if approved, will be added as a Topic in our Knowledgebase.
Welcome to Quest Support
You can find online support help for Quest *product* on an affiliate support site. Click continue to be directed to the correct support content and assistance for *product*.
The Quest Software Portal no longer supports IE8, 9, & 10 and it is recommended to upgrade your browser to the latest version of Internet Explorer or Chrome.