To_date
to_date('2003/07/09',
would return a date value of July 9, 2003.
'yyyy/mm/dd')
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.
Decode
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.
To_Char
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'
would return 'JUL 09TH,
to_char(sysdate, 'MON DDth, YYYY');
2003'
would return 'JUL 9TH,
to_char(sysdate, 'FMMON DDth, YYYY');
2003'
to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'
Oracle/PLSQL: Data Types
The following is a list of datatypes available in Oracle and PLSQL. We've tried to
differentiate between datatypes available in Oracle 8i versus Oracle 9i.
Data Type Oracle 8i Oracle 9i Explanation
Syntax (if applicable)
Where p is the precision and s is
the scale.
The maximum precision is The maximum precision is
dec(p, s)
38 digits. 38 digits. For example, dec(3,1) is a number
that has 2 digits before the decimal
and 1 digit after the decimal.
Where p is the precision and s is
the scale.
The maximum precision is The maximum precision is
decimal(p, s) For example, decimal(3,1) is a
38 digits. 38 digits.
number that has 2 digits before the
decimal and 1 digit after the
decimal.
double
precision
float
int
integer
Where p is the precision and s is
the scale.
The maximum precision is The maximum precision is
numeric(p, s) For example, numeric(7,2) is a
38 digits. 38 digits.
number that has 5 digits before the
decimal and 2 digits after the
decimal.
Where p is the precision and s is
the scale.
The maximum precision is The maximum precision is
number(p, s) For example, number(7,2) is a
38 digits. 38 digits.
number that has 5 digits before the
decimal and 2 digits after the
decimal.
real
smallint
Up to 32767 bytes in Up to 32767 bytes in
PLSQL. PLSQL. Where size is the number of
char (size) characters to store. Fixed-length
Up to 2000 bytes in Oracle Up to 2000 bytes in Oracle strings. Space padded.
8i. 9i.
Up to 32767 bytes in Up to 32767 bytes in
PLSQL. PLSQL. Where size is the number of
varchar2
characters to store. Variable-length
(size)
Up to 4000 bytes in Oracle Up to 4000 bytes in Oracle strings.
8i. 9i.
Variable-length strings. (backward
long Up to 2 gigabytes. Up to 2 gigabytes.
compatible)
Up to 32767 bytes in Up to 32767 bytes in
PLSQL. PLSQL.
raw Variable-length binary strings
Up to 2000 bytes in Oracle Up to 2000 bytes in Oracle
8i. 9i.
Variable-length binary strings.
long raw Up to 2 gigabytes. Up to 2 gigabytes.
(backward compatible)
A date between Jan 1,
A date between Jan 1, 4712
date 4712 BC and Dec 31,
BC and Dec 31, 9999 AD.
9999 AD.
Includes year, month, day, hour,
timestamp fractional seconds
minute, and seconds.
(fractional precision must be a
Not supported in Oracle 8i.
seconds number between 0 and 9.
For example:
precision) (default is 6)
timestamp(6)
timestamp Includes year, month, day, hour,
(fractional fractional seconds minute, and seconds; with a time
seconds precision must be a zone displacement value.
Not supported in Oracle 8i.
precision) number between 0 and 9.
with time (default is 6) For example:
zone timestamp(5) with time zone
Includes year, month, day, hour,
timestamp
minute, and seconds; with a time
(fractional fractional seconds
zone expressed as the session
seconds precision must be a
Not supported in Oracle 8i. time zone.
precision) number between 0 and 9.
with local (default is 6)
For example:
time zone
timestamp(4) with local time zone
Time period stored in years and
interval year
year precision must be a months.
(year
Not supported in Oracle 8i. number between 0 and 9.
precision)
(default is 2) For example:
to month
interval year(4) to month
interval day Not supported in Oracle 8i. day precision must be a Time period stored in days, hours,
(day number between 0 and 9. minutes, and seconds.
precision) (default is 2)
to second For example:
(fractional fractional seconds interval day(2) to second(6)
seconds precision must be a
precision) number between 0 and 9.
(default is 6)
The format of the rowid is: The format of the rowid is:
BBBBBBB.RRRR.FFFFF BBBBBBB.RRRR.FFFFF
Fixed-length binary data. Every
Where BBBBBBB is the Where BBBBBBB is the
rowid record in the database has a
block in the database file; block in the database file;
physical address or rowid.
RRRR is the row in the RRRR is the row in the
block; block;
FFFFF is the database file. FFFFF is the database file.
Universal rowid.
urowid [size] Up to 2000 bytes. Up to 2000 bytes.
Where size is optional.
Valid in PLSQL, but this Valid in PLSQL, but this
boolean datatype does not exist in datatype does not exist in
Oracle 8i. Oracle 9i.
Up to 32767 bytes in Up to 32767 bytes in
PLSQL. PLSQL. Where size is the number of
nchar (size) characters to store. Fixed-length
Up to 2000 bytes in Oracle Up to 2000 bytes in Oracle NLS string
8i 9i.
Up to 32767 bytes in Up to 32767 bytes in
PLSQL. PLSQL. Where size is the number of
nvarchar2
characters to store. Variable-length
(size)
Up to 4000 bytes in Oracle Up to 4000 bytes in Oracle NLS string
8i. 9i.
File locators that point to a read-
bfile Up to 4 gigabytes. Up to 4 gigabytes. only binary object outside of the
database
LOB locators that point to a large
blob U p to 4 gigabytes. Up to 4 gigabytes.
binary object within the database
LOB locators that point to a large
clob Up to 4 gigabytes. Up to 4 gigabytes. character object within the
database
LOB locators that point to a large
nclob Up to 4 gigabytes. Up to 4 gigabytes. NLS character object within the
database
Oracle/PLSQL: Sequences (Autonumber)
In Oracle, you can create an autonumber field by using sequences. A sequence is an
object in Oracle that is used to generate a number sequence. This can be useful
when you need to create a unique number to act as a primary key.
The syntax for a sequence is:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
For example:
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
This would create a sequence object called supplier_seq. The first sequence number
that it would use is 1 and each subsequent number would increment by 1 (ie:
2,3,4,...}. It will cache up to 20 values for performance.
If you omit the MAXVALUE option, your sequence will automatically default to:
MAXVALUE 999999999999999999999999999
So you can simplify your CREATE SEQUENCE command as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Now that you've created a sequence object to simulate an autonumber field, we'll
cover how to retrieve a value from this sequence object. To retrieve the next value in
the sequence order, you need to use nextval.
For example:
supplier_seq.nextval
This would retrieve the next value from supplier_seq. The nextval statement needs to
be used in an SQL statement. For example:
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');
This insert statement would insert a new record into the suppliers table. The
supplier_id field would be assigned the next number from the supplier_seq sequence.
The supplier_name field would be set to Kraft Foods.
Frequently Asked Questions
One common question about sequences is:
Question: While creating a sequence, what does cache and nocache options
mean? For example, you could create a sequence with a cache of 20 as follows:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;
Or you could create the same sequence with the nocache option:
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
Answer: With respect to a sequence, the cache option specifies how many
sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is that if a system failure occurs,
all cached sequence values that have not be used, will be "lost". This results in a
"gap" in the assigned sequence values. When the system comes back up, Oracle will
cache new numbers from where it left off in the sequence, ignoring the so called
"lost" sequence values.
Nocache means that none of the sequence values are stored in memory. This option
may sacrifice some performance, however, you should not encounter a gap in the
assigned sequence values.
Question: How do we set the LASTVALUE value in an Oracle Sequence?
Answer: You can change the LASTVALUE for an Oracle sequence, by executing an
ALTER SEQUENCE command.
For example, if the last value used by the Oracle sequence was 100 and you would
like to reset the sequence to serve 225 as the next value. You would execute the
following commands.
alter sequence seq_name
increment by 124;
select seq_name.nextval from dual;
alter sequence seq_name
increment by 1;
Oracle/PLSQL: NVL Function
In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is
encountered.
The syntax for the NVL function is:
NVL( string1, replace_with )
string1 is the string to test for a null value.
replace_with is the value returned if string1 is null.
Example #1:
select NVL(supplier_city, 'n/a')
from suppliers;
The SQL statement above would return 'n/a' if the supplier_city field contained a null
value. Otherwise, it would return the supplier_city value.
Example #2:
select supplier_id,
NVL(supplier_desc, supplier_name)
from suppliers;
This SQL statement would return the supplier_name field if the supplier_desc
contained a null value. Otherwise, it would return the supplier_desc.
Example #3:
select NVL(commission, 0)
from sales;
This SQL statement would return 0 if the commission field contained a null value.
Otherwise, it would return the commission field.
Oracle/PLSQL: Substr Function
In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.
The syntax for the substr function is:
substr( string, start_position, [ length ] )
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is
omitted, substr will return the entire string.
Note:
If start_position is 0, then substr treats start_position as 1 (ie: the first position in the
string).
If start_position is a positive number, then substr starts from the beginning of the
string.
If start_position is a negative number, then substr starts from the end of the string
and counts backwards.
If length is a negative number, then substr will return a NULL value.
For example:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'
Oracle/PLSQL: Oracle System Tables
Below is an alphabetical listing of the Oracle system tables that are commonly
used.
System Table Description
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers
to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an
enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps
privilege (auditing option) type numbers to type names
Oracle/PLSQL: Instr Function
In Oracle/PLSQL, the instr function returns the location of a substring in a string.
The syntax for the instr Oracle function is:
instr( string1, string2, [ start_position ],
[ nth_appearance ] )
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is
optional. If omitted, it defaults to 1. The first position in the string is 1. If the
start_position is negative, the function counts back start_position number of
characters from the end of string1 and then searches towards the beginning of
string1.
nth_appearance is the nth appearance of string2. This is optional. If omitted, it
defaults to 1.
Note: If string2 is not found in string1, then the instr Oracle function will return 0.
For example:
instr('Tech on the net', 'e') would return 2; the first occurrence of 'e'
instr('Tech on the net', 'e', 1, 1) would return 2; the first occurrence of 'e'
would return 11; the second occurrence of
instr('Tech on the net', 'e', 1, 2)
'e'
instr('Tech on the net', 'e', 1, 3) would return 14; the third occurrence of 'e'
instr('Tech on the net', 'e', -3, 2) would return 2.
Oracle/PLSQL: Least Function
In Oracle/PLSQL, the least function returns the smallest value in a list of
expressions.
The syntax for the least function is:
least( expr1, expr2, ... expr_n )
expr1, expr2, . expr_n are expressions that are evaluated by the greatest function.
If the datatypes of the expressions are different, all expressions will be converted to
whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered
smaller than another if it has a lower character set value.
For example:
least(2, 5, 12, 3) would return 2
least('2', '5', '12', '3') would return '12'
least('apples', 'oranges', 'bananas') would return 'apples'
least('apples', 'applis', 'applas') would return 'applas'
