[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Cascade and Cascade include table data

Hi Guys,

I read about cascade and Cascade include table data for altering Oracle types in Oracle Documentation.

But I am not able to understand.

Can someone explain with a small example.
0
sakthikumar
Asked:
sakthikumar
  • 5
  • 4
1 Solution
 
MikeOM_DBACommented:
And where did you read that?
0
 
sakthikumarAuthor Commented:
I read like cascase will change in all dependent objects, can't understand exactly. what will be changed.
suppose if we are increasing the width of  a type attribute and the type is used in a table.

what will be changed in the table, we are just referring the type in the table structure and when the type is changed
what have to be changed in the table.?
0
 
slightwv (䄆 Netminder) Commented:
Based on the 10g docs, there is an example:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjadv.htm#CJGFFICH

Basically if you create a type that is used in tables and alter the type, how do you want to handle the changes for the dependent objects?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
sakthikumarAuthor Commented:
how do you want to handle the changes for the dependent objects?

what changes for dependent objects, a type is changed and is referred in a table,
in that case,what change is required in dependent objects.
0
 
slightwv (䄆 Netminder) Commented:
Then it sounds like you want  cascade include.
0
 
sakthikumarAuthor Commented:
For eg:

CREATE OR REPLACE TYPE Project AS OBJECT (
project_no NUMBER(2),
title      VARCHAR2(35),
cost       NUMBER(7,2));
/


CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF Project;
/

CREATE TABLE department (
dept_id  NUMBER(2),
name     VARCHAR2(15),
budget   NUMBER(11,2),
projects ProjectList);

in this case, how can i see the metadata of the type "ProjectList" stored in department table. or in which view I can find the definition of this.

Just understand the example in your link.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjadv.htm#i1009137
0
 
sakthikumarAuthor Commented:
just want to understand the example in your link.
0
 
slightwv (䄆 Netminder) Commented:
I don't know the exact view where you can see the actual definition information.

You can get the ddl with:
select dbms_metadata.get_ddl('TYPE','PROJECTLIST') from dual;

The one view I could find that shows you some info is:
select * from DBA_TYPES where type_name='PROJECTLIST';

I looked a little but could not find the view that showed the actual information about 'what' it is.
0
 
sakthikumarAuthor Commented:
OK. In the link it was mentioned like, whenever we include cascade for alter type.

all the metadata associated with dependent objects will be changed.

in this case just like to see the meta data of department table which stores the columns of the type.
0
 
slightwv (䄆 Netminder) Commented:
Searching through doc link you posted, you'll find:
These changes mainly involve updating the tables' metadata (information about a table's structure, describing its columns and their types) and can be done quickly. However, the data in those tables must be updated to the format of the new type version as well. Updating this data can be time-consuming if there is a lot of it, so the ALTER TYPE command has options to let you choose whether to convert all dependent table data immediately or to leave it in the old format to be converted piecemeal as it is updated in the course of business.

The CASCADE option for ALTER TYPE propagates a type change to dependent types and tables. See "ALTER TYPE Statement for Type Evolution". CASCADE itself has options that let you choose whether to convert table data to the new type format as part of the propagation: the option INCLUDING TABLE DATA converts the data; the option NOT INCLUDING TABLE DATA does not convert it. By default, the CASCADE option converts the data. In any case, table data is always returned in the format of the latest type version. If the table data is stored in the format of an earlier type version, Oracle converts the data to the format of the latest version before returning it, even though the format in which the data is actually stored is not changed until the data is rewritten.

I'm even a little confused about how you can update an object but not the data in the object.  Doesn't make sense to me either.

This might be a better question for Oracle Support.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now