general enhancement in 10g

8/3/2019 General Enhancement in 10g

1/34

Oracle10g New

Features


2/34

Oracle Database 10g Data Types


3/34

3

Fundamentals of PL/SQLy Full-featured programming languagey Interpreted languagey Execute using Oracle 10g utilities

y SQL*Plusy

Forms Buildery Combines SQL queries with procedural commandsy Reserved words


4/34

4

PL/SQL Variables and Data

Typesy Variable names must follow the Oracle naming

standard (Example: current_s_id, not $current_s_id)

y Strongly typed languagey Explicitly declare each variable including data type

before using variable

y Variable declaration syntax:

variable_name data_type_declaration;

y Default value always NULL


5/34

5

Scalar Variablesy Reference single value such as number, date, string

yData types correspond to Oracle 10g database data typesy VARCHAR

y CHAR

y NCHAR

y DATEy NUMBER

y PL/SQL has other data types that do not correspond todatabase data types


6/34

Native Floating-Point Data

Typesy Two new numeric data types BINARY_FLOAT,BINARY_DOUBLE

y Based on the IEEE 754 Standard for binary floatingpoint arithmetic

y More efficient than the NUMBER type. Arithmeticoperations are implemented in hardware on mostplatforms. These types may also take up less space in

memory/disk.y Seamless support in SQL and PL/SQL


7/34

BINARY_FLOATy BINARY_FLOAT is a 32-bit, single-precision floating-

point number datatype.

y Each BINARY_FLOAT value requires 5 bytes, including alength byte.

BINARY_DOUBLE

y BINARY_DOUBLE is a 64-bit, double-precisionfloating-point number datatype.

y Each BINARY_DOUBLE value requires 9 bytes,

including a length byte.


8/34

Native Floating-Point Data

Typesy New type conversion functionsy Seamless support in SQL, PL/SQL, Java, XML, and

OCI/OCCI

CREATE TABLE t (f BINARY_FLOAT, dBINARY_DOUBLE);

INSERT INTO t VALUES (2.0f, 2.0d);

INSERT INTO t VALUES (1.5f/0.5f, 1.5d/0.5d);


9/34

NCHAR DatatypeyThe NCHAR datatype is a Unicode-only datatype.

y Define the column length in characters.

y Define the national character set when creatingdatabase.

yWidth specifications of character datatype NCHARrefer to the number of characters.

y The maximum column size allowed is 2000 bytes.

y If we insert a value that is shorter than the columnlength, then Oracle blank-pads the value to columnlength.

ywe cannot insert a CHAR value into an NCHARcolumn, nor can we insert an NCHAR value into aCHAR column.


10/34

10

Composite Variablesy Data object made up of multiple individual data

elements

y Data structure contains multiple scalar variables

y Composite variable data types include:

y RECORD (multiple scalar values similar to a table record)

y TABLE (tabular structure with multiple columnsand rows)

y VARRAY(variable-sized array. Tabular structurethat can expand or contract based on data values)

A

R

R

A

Y


11/34

Varraysy An array is an ordered set of data elements.

y All elements of a given array are of the same datatype.

y Each element has an index, which is a numbercorresponding to the position of the element in thearray.

y The number of elements in an array is the size of thearray.

y Oracle arrays are of variable size, which is why they arecalled varrays.


12/34

Varrays

y we must specify a maximum size when we declare thevarray.

y When we declare a varray, it does not allocate space. It

defines a type, which we can use as:

y The datatype of a column of a relational table

y An object type attribute

y APL/SQL variable, parameter, or function return type


13/34

Nested Tablesy A

nested table type models an unordered set ofelements.

y The elements may be built-in types or user-definedtypes. We

y can view a nested table as a single-column table or,if the nested table is an object type, as amulticolumn table, with a column for each attributeof the object type.

y Anested table definition does not allocate space.


14/34

Nested Tables

y It defines a type, which we can use to declare:

y The datatype of a column of a relational table

y An object type attribute

y APL/SQL variable, parameter, or function return type

y When a nested table appears as the type of a column in arelational table or as an attribute of the underlying objecttype of an object table, Oracle stores all of the nested tabledata in a single table, which it associates with the enclosingrelational or object table.

BACK


15/34

ANSI Collections Support in Nested

Tablesy Equality and Non-EqualityPredicates

y IN and NOT IN operators

y MULTISET, SUBMULTISET, POWERMULTISET,

POWERMULTISET_BY_CARDINALITY MULTISET EXCEPToperators

y CARDINALIT Y, Operator

y SET, ISASET operators

y MEMBER OF operator

y IS EMPTY operator


16/34

MultiSet Operators

y Except

y INTERSECT

y UNION


17/34

Multiset Operators: EXCEPT

y The multiset operator EXCEPT allow to return all the valuesfrom one nested table that aren't found in a second nestedtable in a query. For example the

y

Following query expression returns a nested table of theoriginal value, except for 1.

y select num multiset except num_tab_typ(1) num fromnum_tab;

NUM

--------------------------

NUM_TAB_TYP(2, 3, 4, 5)

NUM_TAB_TYP(3, 5)

NUM_TAB_TYP(2, 4, 6)


18/34

Multiset Operators: INTERSECT

y The multiset operator INTERSECT allows you to return all thevalues which are common between two nested tables. Using anested table with just the value 1 will return an empty set if 1doesn't exist, and a nested table with the value 1 if it does exist.

y select num multiset intersect num_tab_typ(1) num from

num_tabNUM

--------------------------

NUM_TAB_TYP(1)

NUM_TAB_TYP(1)

NUM_TAB_TYP()


19/34

Multiset Operators: UNION

yThe multiset operator UNION allows you to return all thevalues from both nested tables merged into a single nestedtable.

y select num multiset union num_tab_typ(1) num fromnum_tab;

NUM--------------------------

NUM_TAB_TYP(1, 2, 3, 4, 5, 1)

NUM_TAB_TYP(1, 3, 5, 1)

NUM_TAB_TYP(2, 4, 6, 1)

select num multiset union distinctnum_tab_typ(1) num from num_tab;


20/34


21/34

Varray and Nested Tables Enhancements

Alter VARRAY and Nested Table Limit Size

Varray Columns in temporary tables

ALTERTYPE email_list_tab MODIFYELEMENTTYPE

VARCHAR2(40) CASCADE;

ALTERTYPE email_varray_typ MODIFYLIMIT 100

INVALIDATE;

Alter VARRAY and Nested Table Limit Size

Varray Columns in temporary tables

ALTERTYPE email_list_tab MODIFYELEMENTTYPE

VARCHAR2(40) CASCADE;

ALTERTYPE email_varray_typ MODIFYLIMIT 100

INVALIDATE;


22/34

22

Reference Variablesy Directly reference specific database column or row

y Assume data type of associated column or row

y %TYPE data declaration syntax:

variable_name tablename.fieldname%TYPE;

y

%ROWTYPE data declaration syntax:variable_name tablename%ROWTYPE;

LOB Data Type

Must be manipulated using programs in

DBMS_LOB package


23/34

LARGE OBJECT DATATYPE


24/34

Large O ject LOB

Datatypesy T

he built-in LOB datatypes BLOB, CLOB, and NCLOB(stored internally) can store large and unstructured datasuch as text, image, video, and spatial data.

y BLOB, CLOB, and NCLOB data can be up to (4 gigabytes-1) * (database block size) in size.

y When creating a table, we can optionally specifydifferent tablespace and storage characteristics for LOBcolumns or LOB object attributes from those specifiedfor the table.

y LOB columns contain LOB locators that can refer to in-line (in the database) or out-of-line (outside thedatabase) LOB values.


25/34

Large Object (LOB)

Datatypesy Selecting a LOB from a table actually returns the LOB

locator and not the entire LOB value.

y LOBs are similar to LONG and LONG RAW types, butdiffer in the following ways:y LOBs can be attributes of an object type (user-defineddatatype).y The LOB locator is stored in the table column, either with or

without the actual LOB value.y BLOB, NCLOB, and CLOB values can be stored in separate

tablespaces.

y When we access a LOB column, the locator is returned.


26/34

Large Object (LOB)

Datatypesy LOBs permit efficient, random, piece-wise access to andmanipulation of data.

y we can define more than one LOB column in a table.

y With the exception of NCLOB, we can define one ormore LOB attributes in an object.

y we can select LOB columns and LOB attributes.

y we can insert a new row or update an existing row thatcontains one or more LOB columns or an object withone or more LOB attributes.


27/34

Large Object (LOB)

Datatypesy In update operations, we can set the internal LOB value to NULL, empty,or replace the entire LOB with data.y we can update a LOB row-column intersection or a LOB attribute with

another LOB row-column intersection or LOB attribute.

y we can delete a row containing a LOB column or LOB attribute and therebyalso delete the LOB value.

y we can access and populate rows of an in-line LOB column (a LOB columnstored in the database) simply by issuing an INSERT or UPDATEstatement.

y However, to access and populate a LOB attribute that is part of an objecttype, we must first initialize the LOB attribute using the EMPTY_CLOB orEMPTY_BLOB function.

y we can then select the emptyLOB attribute and populate it using theDBMS_LOB package or some other appropriate interface.


28/34

BLOB Datatypey The BLOB datatype stores unstructured binary large objects.

y BLOB objects can be thought of as bitstreams with no character setsemantics.

y BLOB objects can store up to (4 gigabytes-1) * (database block size)of binary data.

y BLOB objects have full transactional support.

y Changes made through SQL, the DBMS_LOB package, or theOracle Call Interface (OCI) participate fully in the transaction.

y BLOB value manipulations can be committed and rolled back.


29/34

CLOB Datatype

y The CLOB datatype stores single-byte and multibytecharacter data.

y Both fixed-width and variable-width character sets aresupported, and both use the database character set.

y CLOB objects can store up to (4 gigabytes-1) *(database block size) of character data.


30/34

NCLOB Datatype

y The NCLOB datatype stores Unicode data.

y Both fixed-width and variable-width character setsare supported, and both use the national characterset.

y NCLOB objects can store up to (4 gigabytes-1) *(database block size) of character text data.

y

NCLOB objects have full transactional support.


31/34

BFILE Datatypey T

he BFILE datatype enables access to binary file LOBsthat are stored in file systems outside Oracle Database.

y ABFILE column or attribute stores a BFILE locator,which serves as a pointer to a binary file on the serverfile system.

y The locator maintains the directory name and thefilename.

y we can change the filename and path of a BFILE without

affecting the base table by using the BFILENAMEfunction.


32/34

BFILE Datatype

y Binary file LOBs do not participate in transactions andare not recoverable.

y Rather, the underlying operating system provides fileintegrity and durability.

y The maximum file size supported is 4 gigabytes.

y The database administrator must ensure that theexternal file exists and that Oracle processes haveoperating system read permissions on the file.

y The BFILE datatype enables read-only support of largebinary files. You cannot modify or replicate such a file.


33/34

LOB Enhancements

y Performance improvements

y Terabyte size LOBs

y NewDBMS_LOB.GET_STORAGE_LIMIT function

y OCI now supports LOBs greater than 4GB


34/34

Q U E S T I O N SQ U E S T I O N S

A N S W E R SA N S W E R S

general enhancement in 10g

Documents