FUNDAMENTALS OF
DATABASE USING
ORACLE 10g Express
Edition
Lesson 2
Mr. Michael S. Forbile
haracter Datatypes
he following are the Character Datatypes in Oracle/PLSQL:
Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
Where size is
the number of
Maximum size of Maximum size of Maximum size of characters to
char(size)
2000 bytes. 2000 bytes. 2000 bytes. store. Fixed-
length strings.
Space padded.
Where size is
the number of
Maximum size of Maximum size of Maximum size of characters to
nvarchar2(size)
4000 bytes. 4000 bytes. 4000 bytes. store. Variable-
length NLS
string.
Where size is
Maximum size of Maximum size of Maximum size of
the number of
4000 bytes. 4000 bytes. 4000 bytes.
varchar2(size) characters to
Maximum size of Maximum size of Maximum size of
store. Variable-
32KB in PLSQL. 32KB in PLSQL. 32KB in PLSQL.
length string.
Numeric Datatypes
The following are the Numeric Datatypes in Oracle/PLSQL:
Data Type Syntax Oracle 9i Oracle 10g Oracle 11g Explanation
Where p is the
precision and s is
the scale.
Precision can range Precision can range Precision can range For example,
from 1 to 38. from 1 to 38. from 1 to 38. number(7,2) is a
number(p,s)
Scale can range Scale can range Scale can range number that has 5
from -84 to 127. from -84 to 127. from -84 to 127. digits before the
decimal and 2
digits after the
decimal.
Where p is the
precision and s is
the scale.
For example,
Precision can range Precision can range Precision can range numeric(7,2) is a
numeric(p,s)
from 1 to 38. from 1 to 38. from 1 to 38. number that has 5
digits before the
decimal and 2
digits after the
decimal.
How to Create
Table using
ORACLE?
-CREATE Command-
The Oracle CREATE TABLE
statement allows you to
create and define a
table.
Syntax
create table tableName (FieldName1
DataType(value) not null,
Fieldname2 DataType(value)) ,
constraint tableName_pk Primary Key
(FieldName1))
How to Insert/Add
Record using
ORACLE?
-INSERT Command-
The Oracle INSERT statement is used
to insert a single record or multiple
records into a table in Oracle.
syntax
INSERT INTO tablename (column1,
column2, ... column_n ) VALUES
(expression1, expression2, ... expression_n
);
How to View
Record(s) using
ORACLE?
-SELECT Command-
The Oracle SELECT statement is used to
retrieve records from one or more tables in
an Oracle database.
Syntax
select *from tablename;
