ROWID and UROWID

Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical rowid identify a row in an ordinary table. A logical rowid identify a row in an index-organized table. The ROWID datatype can store only physical rowids. Thgough, the UROWID (the universal rowid) datatype can store logical,physical, or foreign (non-Oracle) rowids.

Physical Rowids

The Physical rowids provide a fast access to the particular rows. As long as the row exists, its physical rowid does not change. The Efficient & stable, physical rowids are very useful for selecting a set of rows, operating on the entire set, and then updating a subset. For e.g. , you can compare a UROWID variable with the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the newest row fetched from a cursor.

Logical Rowids

The Logical rowids provide the fastest access to the particular rows. The Oracle uses them to construct the secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across the data blocks when new rows are inserted. Though, if the physical location of a row changes, its logical rowid remain valid.

VARCHAR2

You use the VARCHAR2 datatype to store the variable-length character data. How the data is represented internally is totally depend on the database character set. The VARCHAR2 datatype takes a required parameter that specifies the maximum length of up to 32767 bytes. The syntax is as shown below:

VARCHAR2(maximum_length)

You cannot use a constant or variable to specify the highest length; you should use an integer literal in the range 1 to 32767.

The VARCHAR2 datatype includes a trade-off between efficiency and memory use. For a VARCHAR2(>= 2000) variable, the PL/SQL dynamically allocates only enough memory to hold the actual value. Though, for a VARCHAR2 (< 2000) variable, the PL/SQL pre allocates enough memory to hold a maximum-size value.

VARCHAR2 Subtypes

The VARCHAR2 subtypes shown below have the same range of values as their base type. For illustration, the VARCHAR is just another name for VARCHAR2.

STRING

VARCHAR

NLS Character Types

Though the widely used 7- or 8-bit ASCII and EBCDIC character sets are sufficient to represent the Roman alphabet, some Asian languages, like Japanese, contain many characters. These languages need 16 bits (two bytes) to represent each character. How does Oracle deal with such unlike languages?

The Oracle provides National Language Support (NLS) that process single-byte & multi-byte character data and convert between the character sets. It also lets your applications run in various language environments.

NCHAR

You use the NCHAR datatype to store the fixed-length NLS character data. How the data is represented internally, totally depends on the national character set, which might use a fixed-width encoding like US7ASCII or a variable-width encoding like JA16SJIS. The NCHAR datatype takes an optional parameter that specifies the highest length of up to 32767 bytes. The syntax is as shown below:

NCHAR[(maximum_length)]

You cannot use a variable or constant to specify the maximum length; you must use an integer literal in the range 1 to 32767.

NVARCHAR2

You use the NVARCHAR2 datatype to store the variable-length NLS character data. How the data is represented internally totally depends on the national character set, that might use a fixed-width encoding like WE8EBCDIC37C or a variable-width encoding like JA16DBCS. The NVARCHAR2 datatype takes a required parameter which specifies a maximum length of up to 32767 bytes. The syntax is as shown below:

NVARCHAR2(maximum_length)

You cannot use a variable or constant to specify the maximum length; you must use an integer literal in the range 1 to 32767.

Posted Date: 10/2/2012 2:34:39 AM | Location : United States

Ask an Expert

Related Discussions:- Character types, Assignment Help, Ask Question on Character types, Get Answer, Expert's Help, Character types Discussions

Write discussion on Character types
Your posts are moderated

Write your message here..

Related Questions

Restriction and and - sql, Restriction and AND - SQL Restriction is av... Restriction and AND - SQL Restriction is available via the WHERE operator, and so it is in SQL. However, by Example showing how a certain simple restriction can be expressed u

Operators on tables and rows, Operators on Tables and Rows Row Extrac... Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Definition of cross join - sql, Definition of CROSS JOIN - SQL Let s ... Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Left and right joins, Left and Right Joins LEFT OUTER JOIN can be used... Left and Right Joins LEFT OUTER JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3...

Object types and collections - performance of application, Use Object Types... Use Object Types and Collections The Collection types and object types increase your efficiency by allowing for the realistic data modeling. The Complex real-world entities an

Character types in pl/sql, Character Types The Character types allow yo... Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

Subprograms, Subprograms The PL/SQL has two types of subprograms known ... Subprograms The PL/SQL has two types of subprograms known as the procedures and functions that can take parameters and be invoked. As the following example represents, a subp

Pass the nulls to a dynamic sql, Pass the nulls to a dynamic SQL: Pas... Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Updating by replacement, Updating by replacement Syntax: UPDAT... Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

Need fullcalendar modifications, Project Description: I am looking to ch... Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra