Skip to main content

ORACLE 9i

 ORACLE 9I DATABASE TYPES 

 An upgraded Oracle 9i consists of three different types : 

i) Relational : The traditional ORACLE relational database 

(RDBMS). 

ii) Object-relational : The traditional ORACLE relational database, 

extended to include object-oriented concepts and structures such 

as abstract datatype, nested tables and varying arrays. 


iii) Object-oriented : An object-oriented database whose design is 

based only on Object-Oriented Analysis and Design principles. 

 Oracle provides full support to all the three types. Whatever 

method we choose, we must be familiar with the functions and 

features of the core ORACLE relational database. Even if OO 

capabilities are used, the functions and datatypes available in Oracle 

and its programming languages i.e. SQL and PL/SQL should be 

known.

USES OF OBECTS 

 Objects reduce complexity of representing complex data and its 

relations. Objects also help to simplify the way to interact the data. 

Benefits of using OO features are : 

• Object reuse : We can reuse previously written code modules by 

writing OO code. If we create OO database objects, chances of 

reuse of these database objects will be more. 

• Standards adherence : If database objects are built by using 

standards, then the chances they will be reused increase 

exponentially. We have to create de Facto Standard for 

applications or tables if we use the same set of database objects 

for multiple applications or tables. 

 For e.g., if for addresses of students we create a standard 

datatype, then all the addresses in the database will use the same 

internal format. 

 The main things we consider while using objects are the time 

taken to learn how to use OO features and the added complexity of 

the system. The little time required to develop and use condensed 

datatype is a good measure for the time required for learning Oracle’s 

OO features. 

 Object is made up of combination of data and the methods which 

we use to interact with data. 

For example : 

 If clerk want to make list of addresses of students then there is a 

standard for the structure of an address. First there is student’s name, 

then street name, city name, state name and then code number. 

When new admissions are taken, then the student is added in the list 

using same procedure. 

 Add Student( ) For adding a student to the list. 

 Update Student( ) For updating student’s information. 

 Remove Student( ) For deleting a student from the list in case of 

cancelling admission. 

 Method not only manipulate data but can give any information or 

report on data. See given example. 

 If any company want to give new skills training to its workers. 

Then information about age of a worker is valuable to see that how 

many workers can learn new skills considering their ages. 

 Here, if workers birthdates are stored then method for calculating 

age can be used and we have a report on worker’s current age. 

TYPES OF OBJECTS 

 Oracle have different types of objects. Here some major types are 

described. 

Abstract Datatype : 

 Abstract datatype consists of one or more subtypes. Rather than 

being constrained to the standard oracle datatype of NUMBER, DATA 

and Varchar2, the abstract datatype can describe data more 

accurately. 

Example : 

CREATE TYPE PERSON_TV AS OBJECT (NAME VARCHAR2 

(20), ADDRESS ADDRESS_TY); 

Output : 

Type Created 

 While using the abstract datatype the benefits for objects like 

reuse and standard adherence are realised. A standard for the 

representation of abstract data elements, for e.g. address, companies 

etc. is created when an abstract datatype is created. When the same 

abstract datatype is used in multiple places, the same logical data is 

represented in the same manner in each place. 

 When the same abstract datatype is used in multiple places, the 

same logical data is represented in the same manner in each place. 

 Reuse of the abstract datatype shows the enforcement of 

standard representation for the data to which it is bound. 

 We can use abstract datatype to create an object table. In an 

object table, the columns of the table map to the columns of an 

abstract datatype. 

Nested Tables : 

 A nested tables means ‘table within a table’. A nested table is a ‘a 

collection of rows, represented as a column within the main table’. For 

each record within the main table, the nested table may contain 

multiple rows. In one sense, it’s a way of storing a one-to-many 

relationship within one table. 

 Consider a table containing information about departments, in 

which each department may have many projects in progress at one 

time. in a strictly relational model, two separate tables would be 

created : 

i) DEPARTMETN 

ii) PROJECT 

 Nested tables allow us to store the information about projects 

within the DEPARTMENT table. The project table records can be 

accessed directly via the DEPARTMENT table, without the need to 

perform a join.

The ability to select data without traversing joins makes data 

access easier. Even if methods for accessing nested data are not 

defined, Department and Project data have clearly been associated. 

 In a strictly relational model, the association between the 

DEPARTMENT and PROJECT tables would be accomplished by a 

foreign key.

Varying Arrays : 

 A varying array is a ‘set of objects, each with the same datatype’. 

The size of the array is limited when it is created. 

 Varying arrays are also known as VARRAYS. They allows storing 

repeating attributes in tables. 

For example : suppose there is a PROJECT table, and projects 

having workers assigned to them. 

 A project may have many workers, and a worker may work on 

multiple projects. In a strictly relational implementation, a PROJECT 

table, a WORKER table, and an intersection table 

PROJECT_WORKER would be created which store the relationships 

between them. 

 Varying arrays can be used to store the worker names in the 

PROJECT table. If projects are limited to fifteen workers or fewer, a 

varying array with a limit of fifteen entries can be created. The 

datatype for the varying arrays will be whatever datatype is 

appropriate for the worker name values. 

 Then varying array can be populated, so that for each project the 

names of all of the project’s workers can be selected without querying 

the WORKER table. 

Note : When a table is created with a varying array, the array is a 

nested table with a limited set of rows. 

Large Objects : 

 A large object or LOB is capable of storing large volumes of data. 

The different LOB datatypes available are BLOB, CLOB, NCLOB, and 

BFILE. 

 The BLOB datatype is used for binary data and can extend to 

4GB in length. 

 The CLOB datatype stores character data and can store data 

up to 4GB in length. 

 The NCLOB datatype is used to store CLOB data for multibyte 

character sets.

 The data for BLOB, CLOB and NCLOB datatype is stored 

inside the database. So, there can be a single row in the 

database that is over 4GB in length. 

One of the LOB datatype, BFILE, is a pointer to an external file. 

The files referenced by BFILEs exist at operating system level. The 

database only maintains a pointer to the file. The size of the external 

file is limited only by the operating system. The data is stored outside 

the database, so ORACLE does not maintain concurrency or integrity 

of the data. 

 We can use multiple LOBs per table. For example, consider a 

table with a CLOB column and two BLOB columns. This is an 

improvement over the LONG datatype, as there can be one LONG per 

table, ORACLE provides a number of functions and procedures, which 

can be used to manipulate and select LOB data. 

References : 

 Varying arrays and Nested tables are embedded objects. They 

are physically embedded within another object. They of object called 

as referenced objects, are physically separate from the objects that 

refer to them. References (also known as REFs) are essentially 

pointers to row objects. A row object is different from a column 

object. An example of a column object would be a varying array. It is 

an object that is treated as a column in a table. On the other hand, a 

row object always represents a row. 

 References are typically among the last OO features

implemented while migrating a relational database to an objectrelational or pure OO one. 

Object Views : 

 Object views allow adding OO concepts on top of existing 

relational table. For example, an abstract datatype can be created 

based on an existing table definition. Thus, object views give the 

benefits of relational table storage and OO structures. Object views 

allow the development of OO features within a relational database, a 

kind of bridge between the relational and OO worlds. 

 FEATURES OF OBJECTS 

 An object has a name, a standard representation and a standard 

collection of operations that affect it. The operations that affect an 

object are called ‘methods’. So, an abstract datatype has a name, a 

standard representation and defined methods for accessing data

Comments

Post a Comment

Popular posts from this blog

what is computer network and their types ?

   what is network ? -  A network is a group of two or more computer systems, which are connected together. It also consist of a collection of computers, printers, scanner and other devices that are liked together. Networking has single purpose to share information as fast as possible.   The basic types of network- 1) LAN - LAN stands for Local Area Network. It is used to network computers within a limited area like office, school by using the network media. 2) CAN - Campus Area Network is an interconnection of local area networks within limited geographical area. campus network can be additional to the set of wireless connections. connects several buildings to the same network. Example, corporate office campus, university. 3) MAN - MAN means Metropolitan Area Network, which optimized for a larger geographic area than a LAN, ranging from several blocks of buildings to entire city. 4) WAN - WAN is Wide Area Network that is network connection of wide area such as the w...
Post a Comment
Read more

Levels of Software Testing

   Levels of Software Testing There are various testing levels one of which is unit testing in which the smallest testable part of an application is testing for correctness. In integration testing we check the system when we linking  the various modules.  In system testing we check the system as a whole from customers’ viewpoint. Acceptance testing tries to check whether the system is acceptable by most of the users. Alpha testing is carried out at developer’s site and beta is at customer’s site. A Testers workbench is a virtual environment used to verify the correctness or soundness of a design or model. 11 step testing process is a experience based practical approach for solution to a test assignment.     UNIT TESTING -        Unit testing is a software development process in which the smallest testable parts of an application, called units, are individually and independently scrutinized for proper operation. Unit testing is often...
Post a Comment
Read more

PL / SQL IN DBMS

                                          PL / SQL   Pl/SQL    state Loops in PL/SQL   Built in Function     Cursor Management     Exception INTRODUCTION   PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages,  resulting in a structural language that is more powerful than  SQL.PL/SQL is not case sensitive. ‘C’ style comments (/* ……… */)  may be used in PL/SQL programs whenever required.   All PL/SQL programs are made up of blocks, each block performs  a logical action in the program. A PL/SQL block consists of three parts  1. Declaration section   2. Executable section   3. Exception handling section  Only the executable section is required. The other sections are  optional.  A PL/SQL block h...
Post a Comment
Read more