Oracle - PL/SQL - ASCIISTR Function

In Oracle/PLSQL, the asciistr function converts a string in any character set to an ASCII string using the database character set.

The syntax for the asciistr function is:

asciistr( string )

string is a string in any character set that you want converted to an ASCII string in the database character set.

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

asciistr('A B C Ä Ê')	would return 'A B C \00C4 \00CA'
asciistr('A B C Õ Ø')	would return 'A B C \00D5 \00D8'
asciistr('A B C Ä Ê Í Õ Ø')	would return 'A B C \00C4 \00CA \00CD \00D5 \00D8'

Oracle - PL/SQL - ASCII Function

In Oracle/PLSQL, the ascii function returns the NUMBER code that represents the specified character.

The syntax for the ascii function is:

ascii( single_character )

single_character is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

ascii('t')	would return 116.
ascii('T')	would return 84.
ascii('T2')	would also return 84.

ORACLE - ASCII Table - A Quick reference

Dec Hex Oct Char Description 0 0 000 null 1 1 001 start of heading 2 2 002 start of text 3 3 003 end of text 4 4 004 end of transmission 5 5 005 enquiry 6 6 006 acknowledge 7 7 007 bell 8 8 010 backspace 9 9 011 horizontal tab 10 A 012 new line 11 B 013 vertical tab 12 C 014 new page 13 D 015 carriage return 14 E 016 shift out 15 F 017 shift in 16 10 020 data link escape 17 11 021 device control 1 18 12 022 device control 2 19 13 023 device control 3 20 14 024 device control 4 21 15 025 negative acknowledge 22 16 026 synchronous idle 23 17 027 end of trans. block 24 18 030 cancel 25 19 031 end of medium 26 1A 032 substitute 27 1B 033 escape 28 1C 034 file separator 29 1D 035 group separator 30 1E 036 record separator 31 1F 037 unit separator 32 20 040 space 33 21 041 ! 34 22 042 " 35 23 043 # 36 24 044 $ 37 25 045 % 38 26 046 & 39 27 047 ' 40 28 050 ( 41 29 051 ) 42 2A 052 * 43 2B 053 + 44 2C 054 , 45 2D 055 - 46 2E 056 . 47 2F 057 / 48 30 060 0 49 31 061 1 50 32 062 2 51 33 063 3 52 34 064 4 53 35 065 5 54 36 066 6 55 37 067 7 56 38 070 8 57 39 071 9 58 3A 072 : 59 3B 073 ; 60 3C 074 < 61 3D 075 = 62 3E 076 > 63 3F 077 ?

Dec Hex Oct Char 64 40 100 @ 65 41 101 A 66 42 102 B 67 43 103 C 68 44 104 D 69 45 105 E 70 46 106 F 71 47 107 G 72 48 110 H 73 49 111 I 74 4A 112 J 75 4B 113 K 76 4C 114 L 77 4D 115 M 78 4E 116 N 79 4F 117 O 80 50 120 P 81 51 121 Q 82 52 122 R 83 53 123 S 84 54 124 T 85 55 125 U 86 56 126 V 87 57 127 W 88 58 130 X 89 59 131 Y 90 5A 132 Z 91 5B 133 [ 92 5C 134 \ 93 5D 135 ] 94 5E 136 ^ 95 5F 137 _ 96 60 140 ` 97 61 141 a 98 62 142 b 99 63 143 c 100 64 144 d 101 65 145 e 102 66 146 f 103 67 147 g 104 68 150 h 105 69 151 i 106 6A 152 j 107 6B 153 k 108 6C 154 l 109 6D 155 m 110 6E 156 n 111 6F 157 o 112 70 160 p 113 71 161 q 114 72 162 r 115 73 163 s 116 74 164 t 117 75 165 u 118 76 166 v 119 77 167 w 120 78 170 x 121 79 171 y 122 7A 172 z 123 7B 173 { 124 7C 174 125 7D 175 } 126 7E 176 ~ 127 7F 177 DEL

Oracle - PL/SQL - TO_CHAR Function

In Oracle/PLSQL, the to_char function converts a number or date to a string.

The syntax for the to_char function is:

to_char( value, [ format_mask ], [ nls_language ] )

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Examples - Numbers

The following are number examples for the to_char function.

to_char(1210.73, '9999.9')	would return '1210.7'
to_char(1210.73, '9,999.99')	would return '1,210.73'
to_char(1210.73, '$9,999.00')	would return '$1,210.73'
to_char(21, '000099')	would return '000021'

Examples - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');	would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');	would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');	would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');	would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');	would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');	would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions

---

Question: Why doesn't this sort the days of the week in order?

select ename, hiredate, to_char((hiredate),'fmDay') "Day"
from emp
order by "Day";

Answer:

In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmDD format mask as follows:

select ename, hiredate, to_char((hiredate),'fmDD') "Day"
from emp
order by "Day";

Oracle - PL/SQL - DECODE Function

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.

The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id,	10000,	'IBM',
	10001,	'Microsoft',
	10002,	'Hewlett Packard',
		'Gateway') result
FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
result := 'IBM';

ELSIF supplier_id = 10001 THEN
result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';

ELSE
result := 'Gateway';

END IF;

The decode function will compare each supplier_id value, one by one.

Sample Scenario :

Question: How to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1.

Answer: To accomplish this, use the decode function as follows:

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

The formula below would equal 0, if date1 is greater than date2:

(date1 - date2) - abs(date1 - date2)

Question: Is it possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,
decode(trunc ((supplier_id - 1) / 10),	0,	'category 1',
	1,	'category 2',
	2,	'category 3',
		'unknown') result
FROM suppliers;

In this example, based on the formula:

trunc ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

and so on...

Question: I need to write a decode statement that will return the following:

If yrs_of_service < 1 then return 0.04
If yrs_of_service >= 1 and < 5 then return 0.04
If yrs_of_service > 5 then return 0.06

How can I do this?

Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

SELECT emp_name,
decode(trunc (( yrs_of_service + 3) / 4),	0,	0.04,
	1,	0.04,
		0.06) as perc_value
FROM employees;

Oracle - PL/SQL - TO_DATE Function

In Oracle/PLSQL, the to_date function converts a string to a date.

The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:

to_date('2003/07/09', 'yyyy/mm/dd')	would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY')	would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd')	would return a date value of Mar 15, 2002.

PL/SQL- COURSOR WITHIN A CURSOR

Question: In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer: Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);

/* First cursor */
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';

/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;

begin

-- Open first cursor
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;

open get_columns;
loop
fetch get_columns into v_column_name;

end loop;
close get_columns;

end loop;
close get_tables;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - 'SQLCODE' -ERROR- 'SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.

Oracle - Report 6i - Basics

1) About reports

A report consists of objects that collectively define the report:
1)data model objects (queries, groups, columns, links, user parameters)
2)layout objects (repeating frames, frames, fields, boilerplate, anchors)
3)parameter form objects (parameters, fields, boilerplate)

2) Report Styles
2.1)About tabular reports
A tabular report is the most basic type of report. Each column corresponds to acolumn selected from the database.
2.2)About group above reports
A group above report contains multiple groups in its data model.
It is a "master/detail" report, where there may be a lot of information in the master group.
For every master group, the related values of the detail group(s) are fetched from thedatabase and are displayed below the master information.
2.3)About group left reports
A group left report also contains multiple groups in its data model, dividing the rowsof a table based on a common value in one of the columns.
Use this type of report to restrict a column from repeating the same value several times while values of relatedcolumns change.
The data model for group above and group left reports is the same,but the layouts differ; group above reports display the master information at the topwhile group left reports display break columns to the side.
2.4)About form-like reports
A form-like report displays one record per page, displaying field values to the right offield labels.
2.5)About form letter reports
A form letter report contains database values embedded in boilerplate text (any textthat you enter or import into a Report Editor.)
2.6)About mailing label reports
A mailing label report prints mailing labels in multiple columns on each page. Usingthe Report Wizard, you can specify the format for your mailing labels.
2.7)About matrix reports
A matrix (cross-product) report is a cross-tabulation of four groups of data:
One group of data is displayed across the page. One group of data is displayed down the page. One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations. One group of data is displayed as the"filler" of the cells.
Thus, to create a matrix report, you need at least four groups in the data model: onegroup must be a cross-product group, two of the groups must be within thecross-product group to furnish the "labels", and at least one group must provide theinformation to fill the cells. The groups can belong to a single query or to multiplequeries.

3)About triggers
Triggers check for an event.

When the event occurs they run the PL/SQL code associated with the trigger.

Report triggers are activated in response to report events such as the report openingand closing rather that the data that is contained in the report. They are activated in apredefined order for all reports.

Format triggers are executed before an object is formatted. A format trigger can be usedto dynamically change the formatting attributes of the object.

Validation triggers are PL/SQL functions that are executed when parameter values arespecified on the command line and when you accept the Runtime Parameter Form.

Database triggers are procedures that are stored in the database and implicitly executedwhen a triggering statement such as INSERT, UPDATE, or DELETE is issued against theassociated table.

3.1)About report triggers
Report triggers execute PL/SQL functions at specific times during the execution andformatting of your report.

Using the conditional processing capabilities of PL/SQL forthese triggers, you can do things such as customize the formatting of your report,perform initialization tasks, and access the database.

To create or modify a report trigger, you use the Report Triggers node in the Object Navigator.
Report triggers must explicitly return TRUE or FALSE.

Oracle Reports has five global report triggers.

The trigger names indicate at what point the trigger fires:

3.1.1)Before Report trigger: Fires before the report is executed but after queries areparsed.

3.1.2)After Report trigger: Fires after you exit the Paper Design view, or after reportoutput is sent to a specified destination, such as a file, a printer, or an e-mail ID.

This trigger can be used to clean up any initial processing that was done, such asdeleting tables.

Note, however, that this trigger always fires, whether or not yourreport completed successfully.

3.1.3)Between Pages trigger: Fires before each page of the report is formatted, except thevery first page.
This trigger can be used for customized page formatting.

In the Paper Design view, this trigger only fires the first time that you go to a page.
If you subsequently return to the page, the trigger does not fire again.

3.1.4)Before Parameter Form trigger: Fires before the Runtime Parameter Form isdisplayed. From this trigger, you can access and change the values of parameters,PL/SQL global variables, and report-level columns.

If the Runtime Parameter Form is suppressed, this trigger still fires.
Consequently, you can use this trigger for validation of command line parameters.

3.1.5)After Parameter Form trigger: Fires after the Runtime Parameter Form is displayed.From this trigger, you can access parameters and check their values.

This trigger can also be used to change parameter values or, if an error occurs, return to theRuntime Parameter Form.

Columns from the data model are not accessible from this trigger.

If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires.Consequently, you can use this trigger for validation of command line parameters or other data.

4)Order of report trigger execution
The order of events when a report is executed is as follows:

1. Before Parameter Form trigger is fired.

2. Runtime Parameter Form appears (if not suppressed).

3. After Parameter Form trigger is fired (unless the user cancels from the Runtime
Parameter Form).

4. Report is "compiled".

5. Queries are parsed.

6. Before Report trigger is fired.

7. SET TRANSACTION READONLY is executed (if specified with the READONLY command line keyword or setting).

8. The report is executed and the Between Pages trigger fires for each page except the first one. COMMITs can occur during this time due to: SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.

9. COMMIT is executed (if READONLY is specified) to end the transaction.

10. After Report trigger is fired.

11. COMMIT/ROLLBACK/NOACTION is executed based on what was specifiedwith the ONSUCCESS command line keyword or setting.

Ramya Vivek

Oracle - Complete list of PL/SQL Data Types

Character Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

Numeric Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

Date/Time Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

Large Object (LOB) Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

Rowid Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid. Where size is optional.