Oracle Datatypes

Datatype summary for Oracle 7, 8 & 9i

Datatype	Description	Max Size: Oracle 7	Max Size: Oracle 8	Max Size: Oracle 9	Max Size: PL/SQL	PL/SQL Subtypes/ Synonyms
VARCHAR2(size)	Variable length character string having maximum length size bytes. You must specify size	2000 bytes minimum is 1	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
NVARCHAR2(size)	Variable length national character set string having maximum length size bytes. You must specify size	N/A	4000 bytes minimum is 1	4000 bytes minimum is 1	32767 bytes minimum is 1	STRING VARCHAR
VARCHAR	Now deprecated – VARCHAR is a synonym for VARCHAR2 but this usage may change in future versions.	-	-	-
CHAR(size)	Fixed length character data of length size bytes. This should be used for fixed length data. Such as codes A100, B102…	255 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.	CHARACTER
NCHAR(size)	Fixed length national character set data of length size bytes. This should be used for fixed length data. Such as codes A100, B102…	N/A	2000 bytes Default and minimum size is 1 byte.	2000 bytes Default and minimum size is 1 byte.	32767 bytes Default and minimum size is 1 byte.
NUMBER(p,s)	Number having precision p and scale s.	The precision p can range from 1 to 38.The scale s can range from -84 to 127.	The precision p can range from 1 to 38.The scale s can range from -84 to 127.	The precision p can range from 1 to 38.The scale s can range from -84 to 127.	Magnitude 1E-130 .. 10E125maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits The scale s can range from -84 to 127. For floating point don’t specify p,s REAL has a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits	fixed-point numbers: DEC DECIMAL NUMERICintegers: INTEGER INT SMALLINT floating-point: DOUBLE PRECISION FLOAT REAL
PLS_INTEGER	signed integers PLS_INTEGER values require less storage and provide better performance than NUMBER values. So use PLS_INTEGER where you can!	PL/SQL only	PL/SQL only	PL/SQL only	magnitude range is -2147483647 .. 2147483647
BINARY_INTEGER	signed integers (older slower version of PLS_INTEGER)				magnitude range is -2147483647 .. 2147483647
LONG	Character data of variable length (A bigger version the VARCHAR2 datatype)	2 Gigabytes	2 Gigabytes	2 Gigabytes – but now deprecated	32760 bytes Note this is smalller than the maximum width of a LONG column
DATE	Valid date range	from January 1, 4712 BC to December 31, 4712 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD.	from January 1, 4712 BC to December 31, 9999 AD. (in Oracle7 = 4712 AD)
TIMESTAMP (fractional_seconds_precision)	the number of digits in the fractional part of the SECOND datetime field.	-	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
TIMESTAMP (fractional_seconds_precision) WITH {LOCAL} TIMEZONE	As above with time zone displacement value	-	-	Accepted values of fractional_seconds_precision are 0 to 9. (default = 6)
INTERVAL YEAR (year_precision) TO MONTH	Time in years and months, where year_precision is the number of digits in the YEAR datetime field.	-	-	Accepted values are 0 to 9. (default = 2)
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Time in days, hours, minutes, and seconds.day_precision is the maximum number of digits in ‘DAY’ fractional_seconds_precision is the max number of fractional digits in the SECOND field.	-	-	day_precision may be 0 to 9. (default = 2)fractional_seconds_precision may be 0 to 9. (default = 6)
RAW(size)	Raw binary data of length size bytes. You must specify size for a RAW value.	Maximum size is 255 bytes.	Maximum size is 2000 bytes	Maximum size is 2000 bytes	32767 bytes
LONG RAW	Raw binary data of variable length. (not intrepreted by PL/SQL)	2 Gigabytes.	2 Gigabytes.	2 Gigabytes – but now deprecated	32760 bytes Note this is smalller than the maximum width of a LONG RAW column
ROWID	Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)				Hexadecimal string representing the unique address of a row in its table. (primarily for values returned by the ROWID pseudocolumn.)
UROWID	Hex string representing the logical address of a row of an index-organized table	N/A	The maximum size and default is 4000 bytes	The maximum size and default is 4000 bytes	universal rowid – Hex string representing the logical address of a row of an index-organized table, either physical, logical, or foreign (non-Oracle)	See CHARTOROWID and the package: DBMS_ROWID
MLSLABEL	Binary format of an operating system label.This datatype is used with Trusted Oracle7.
CLOB	Character Large Object	4Gigabytes	4Gigabytes	4Gigabytes	4Gigabytes
NCLOB	National Character Large Object		4Gigabytes	4Gigabytes	4Gigabytes
BLOB	Binary Large Object		4Gigabytes	4Gigabytes	4Gigabytes
BFILE	pointer to binary file on disk		4Gigabytes	4Gigabytes	The size of a BFILE is system dependent but cannot exceed four gigabytes (2**32 – 1 bytes).
XMLType	XML data	-	-	4Gigabytes	Populate with XML from a CLOB or VARCHAR2.or query from another XMLType column.

Notes and Examples

VARCHAR2:
Storing character data as Varchar2 will save space:

Store ‘SMITH’ not ‘SMITH ‘

CHAR:
Over time, when varchar2 columns are updated they will sometimes create chained rows – because CHAR columns are fixed width they are not affected by this – so less DBA effort is required to maintain performance.

PL/SQL
When retrieving data for a NUMBER column, consider (if you can) using the PL/SQL datatype: PLS_INTEGER for better performance.

LONG
You should start using BLOB instead of LONG

INTEGER
This ANSI datatype will be accepted by Oracle – it is actually a synonym for NUMBER(38)

The FLOAT datatype
This ANSI datatype will be accepted by Oracle – Very similar to NUMBER it stores zero, positive, and negative floating-point numbers

The NUMBER datatype
Stores zero, positive, and negative numbers, fixed or floating-point numbers

Fixed-point NUMBER
NUMBER(p,s)
precision p = length of the number in digits
scale s = places after the decimal point, or (for negative scale values) significant places before the decimal point.

Integer NUMBER
NUMBER(p)
This is a fixed-point number with precision p and scale 0. Equivalent to NUMBER(p,0)

Floating-Point NUMBER
NUMBER
floating-point number with decimal precision 38

Confusingly the Units of measure for PRECISION vary according to the datatype.
For NUMBER data types: precision p = Number of Digits
For FLOAT data types: precision p = Binary Precision (multiply by 0.30103 to convert)

{So FLOAT = FLOAT (126) = 126 x 0.30103 = approx 37.9 digits of precision.}

Example

The value 7,456,123.89 will display as follows NUMBER(9) 7456124 NUMBER(9,1) 7456123.9 NUMBER(*,1) 7456123.9 NUMBER(9,2) 7456123.89 NUMBER(6) [not accepted exceeds precision] NUMBER(7,-2) 7456100 NUMBER 7456123.89 FLOAT 7456123.89 FLOAT(12) 7456000.0

Oracle stores all numeric data in variable length format.

Storage space is therefore dependent on the length of all the individual values stored in the table. Precision and scale settings do not affect storage requirements. DATA_SCALE may appear to be truncating data, but Oracle still stores the exact values as input. DATA_PRECISION can be used to constrain input values.

It is possible to save storage space by having an application truncate a fractional value before inserting into a table, but you have to be very sure the logic and maths still make sense. (This is unlikely to be the case)

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE
FROM cols WHERE table_name = ‘Your_Table’;

A common space-saving trick is storing boolean values as an Oracle CHAR, rather than NUMBER:

CREATE TABLE my_demo (accountcode NUMBER, postableYN CHAR check (postableYN in (0,1)) );

– Standard logical values: 1=True and 0=False
insert into my_demo values(525, ‘1′);
insert into my_demo values(526, ‘0′);

SELECT accountcode, decode(postableYN,1,’True’,0,’False’) FROM my_demo;
or
SELECT accountcode, decode(postableYN,1,’Oui’,0,’Non’) FROM my_demo;

Comparison with other RDBMS’s

	int10	int6	int1	char(n)	blob	XML
Oracle 9	NUMBER(10)	NUMBER(6)	NUMBER(1)	VARCHAR2(n)	BLOB	XMLType
MS SQL Server 2005	NUMERIC(10)	NUMERIC(6)	TINYINT	VARCHAR(n)	IMAGE	XML
Sybase system 10	NUMERIC(10)	NUMERIC(6)	NUMERIC(1)	VARCHAR(n)	IMAGE
MS Access (Jet)	Long Int or Double	Single	Byte	TEXT(n)	LONGBINARY
TERADATA	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARBYTE(20480)
DB2	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	VARCHAR(255)
RDB	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	LONG VARCHAR
INFORMIX	INTEGER	DECIMAL(6)	DECIMAL(1)	VARCHAR(n)	BYTE
RedBrick	integer	int	int	char(n)	char(1024)
INGRES	INTEGER	INTEGER	INTEGER	VARCHAR(n)	VARCHAR(1500)

Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement – these limits vary considerably between products and versions.

http://www.ss64.com/orasyntax/datatypes.html

unique index vs unique constraint

A constraint is to define a business rule for a column of a table.

Constraints to be referential integrity rules that govern the allowable contents of a column and
in the case of a primary key (PK) and unique key (UK) constraint, in conjunction with foreign keys
(FK), define the formal relationship between columns and rows in one table to another.

The difference between a unique index and a unique key/primary key constraint starts with the fact
that the constraint is a rule while the index is a database object that is used to provide improved
performance in the retrieval of rows from a table. It is a physical object that takes space and is
created with the DDL command: create index or as part of a create table with primary key or unique
key constraints or an alter table command that adds these constraints.

Constraint types

Not Null – Column value must be present.
Unique Key – Column(s) value(s) must be unique in table or null.
Primary Key – Unique key + Not Null which equates to every column in the key must have a value and
this value is unique so the primary key uniquely identifies each and every row in the table.
Foreign Key – Restricts values in a table column to being a value found in the primary key or unique
key Constraint on the referenced table (parent/child relationship).
Check – Tests the column value against an expression (rule).

Technically it would be possible for Oracle, to support primary key and unique key constraints
without using an index at all. In the case of a unique key or primary key constraint Oracle could
perform a full table scan to check for the presence of a key value before performing the insert
but the performance cost of doing this for anything other than a very small table would be excessive
probably rendering Oracle useless.

Prior to Oracle 8 if you defined a primary key or a unique key constraint the Oracle RDBMS would
create a unique index to support enforcement of the constraint. If an index already existed on the
constrained columns Oracle would use it rather than define another index on the same columns.

Starting with Oracle version 8 Oracle has the ability to enforce primary key and unique key constraints
using non-unique indexes. The use of non-unique indexes supports deferring enforcement of the constraint
until transaction commit time if the constraint is defined at creation time as deferrable. Also starting
with version 8 Oracle has the ability to place constraints on tables where the existing data does not
meet the requirements imposed by the constraint through use of a novalidate option.

The practical difference between using a unique index to support data integrity and a unique key or
primary key on the same columns since Oracle will build an index to support the constraint if you do not
is that you can define foreign key constraints when the primary key or unique key constraint exist.
Also in the case of a primary key constraint Oracle will convert the columns in the constraint to be
not null constrained when it is added to meet the primary key requirement to uniquely identify each and
every row in the table.

There is no such restriction on a unique index. The primary key and unique key constraints along with
foreign key constraints that reference them also provide a form of documentation on the relationships
between objects.

The Oracle RDBMS Data Dictionary views All/DBA/USER_CONSTRAINTS and ALL/DBA/USER_CONS_COLUMNS may be used
to locate constraints on a table and the columns being constrained.

If you drop or disable a primary key or unique key constraint that is supported by a unique index the index
is dropped with the constraint. If a non-unique index is used to support the constraint the index is not
dropped with the constraint. This second condition is effective only with version 8 and higher.

Note: Unique key constraints allow the constrained column to be NULL. Nulls values are considered to be
valid and do not violate the constraint.

http://www.jlcomp.demon.co.uk/faq/uk_idx_con.html
http://askanantha.blogspot.com/2007/09/difference-between-unique-index-and.html

Delete duplicate row

DELETE FROM my_table
WHERE rowid NOT IN ( SELECT MAX(ROWID) FROM my_table
                     GROUP BY colA,colB,colC );

In the GROUP BY clause, enumerate all of your columns in your table, or the columns you think should be the primary key columns. The subquery will get the max rowid of these groupings. The DELETE will remove all rows that do not have these rowid values.

Reset a sequence

alter sequence SEQUENCE_NAME increment by -10;
select SEQUENCE_NAME.NEXTVAL from dual;
alter sequence SEQUENCE_NAME increment by 1;

Synonym

create synonym synonym-name for object;
create public synonym synonym-name for object;

A synonym is an alias for one of the following objects:

• table
• object table
• view
• object view
• sequence
• stored procedure
• stored function
• package
• materialized view
• java class
• used defined object object type
• another synonym

The object does not need to exist at the time of its creation.

Synonyms cannot be used in a drop table, drop view or truncate table/cluster statements. If this is tried, it results in a ORA-00942: table or view does not exist

Public synonyms

A public synonym (create public synonym) is owned by public rather than the one who has created it. Therefore, a public synonym is valid for each schema.

Listing synonyms

Existing synonyms can be found via all_synonyms (or dba_synonyms, or user_synonyms).

Generally good DBA’s try to avoid public synonyms. They do make management of the database a bit easier, but they also have security and performance issues associated with them. Hence, try not to use public synonyms unless you have to.

You can have a public and private synonym of the same name. In fact, you can have a public and private synonym called EMP in the SCOTT schema and have a table called EMP in the same schema. In cases where you have multiple synonyms and/or a table present, it can get confusing which object you are using (this is another reason we hate public synonyms). There is an order of precedence with regards to the use of synonyms and local objects. This is:

1. Local objects will always be accessed first.

2. If a local object does not exist, the object with a private synonym will be accessed.

3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Note that a synonym can be created for an object that does not exist, and an object with an associated synonym can be dropped without removing the synonym. This can cause all sorts of interesting problems for DBA’s, so be careful.

Removing Synonyms

– Drop public synony

SQL> DROP PUBLIC SYNONYM emp;

– Drop private synonym

SQL> DROP SYNONYM emp;

From http://www.dba-oracle.com/concepts/synonyms.htm

 

Oracle alter table

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

 

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
 RENAME TO vendors;

This will rename the suppliers table to vendors.

 

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 ADD column_name column-definition;

For example:

ALTER TABLE supplier
 ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

 

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	…
	column_n	column_definition );

For example:

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

 

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

For example:

ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

 

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	…
	column_n	column_type );

For example:

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

 

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 DROP COLUMN column_name;

For example:

ALTER TABLE supplier
 DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

 

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

http://www.techonthenet.com/sql/tables/alter_table.php

JDBC connects to different DBs

1、Oracle8/8i/9i（thin）
Class.forName(“oracle.jdbc.driver.OracleDriver”).newInstance();
String url=”jdbc:oracle:thin:@localhost:1521:orcl”;
//orcl is ORACLE_SID
String user=”test”;
String password=”test”;
Connection conn= DriverManager.getConnection(url,user,password);

 

2、DB2
Class.forName(“com.ibm.db2.jdbc.app.DB2Driver “).newInstance();
String url=”jdbc:db2://localhost:5000/sample”;
//sample is DB name
String user=”admin”;
String password=”";
Connection conn= DriverManager.getConnection(url,user,password);

 

3、Sql Server7.0/2000
Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”).newInstance();
String url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydb”;
//myDB is DB name
String user=”sa”;
String password=”";
Connection conn= DriverManager.getConnection(url,user,password);

 

4、Sybase
Class.forName(“com.sybase.jdbc.SybDriver”).newInstance();
String url =” jdbc:sybase:Tds:localhost:5007/myDB”;
//myDB is DB name
Properties sysProps = System.getProperties();
SysProps.put(“user”,”userid”);
SysProps.put(“password”,”user_password”);
Connection conn= DriverManager.getConnection(url, SysProps);

 

5、Informix
Class.forName(“com.informix.jdbc.IfxDriver”).newInstance();
String url =
“jdbc:informix-sqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver;
user=testuser;password=testpassword”;
//myDB is DB name
Connection conn= DriverManager.getConnection(url);

 

6、MySQL
Class.forName(“org.gjt.mm.mysql.Driver”).newInstance();
String url =”jdbc:mysql://localhost/myDB?user=soft&password=soft1234&useUnicod
e=true&characterEncoding=8859_1″
//myDB is DB name
Connection conn= DriverManager.getConnection(url);

7、PostgreSQL
Class.forName(“org.postgresql.Driver”).newInstance();
String url =”jdbc:postgresql://localhost/myDB”
//myDB is DB name
String user=”myuser”;
String password=”mypassword”;
Connection conn= DriverManager.getConnection(url,user,password);

From http://www.ixdba.net/article/0a/499.html

Using LIKE Instead of SUBSTR

Do you LIKE to SUBSTR?

I need to implement the following requirement:

“If the first three characters of the pet type is CAT, then buy catnip. If the first three characters of the pet type is DOG, then buy milkbones.”

What’s the best way to satisfy this requirement? Well, I know how to identify the first three characters of a string…use SUBSTR. So my first inclination would be to code as follows:

v_first_three := SUBSTR (pet_type, 1, 3);

IF v_first_three = 'CAT'

THEN

   buy_catnip;

ELSIF v_first_three = 'DOG'

THEN

   buy_milkbones;

END IF;

And that certainly does the trick. But is it the only way? Unlikely…in very few situations do you just have a single way to implement a requirement. In this case, I could also use the LIKE operator, as follows:

IF pet_type LIKE 'CAT%'

THEN

   buy_catnip;

ELSIF pet_type LIKE 'DOG%'

THEN

   buy_milkbones;

END IF;

It turns out that using the LIKE operator is more efficient than SUBSTR in this scenario (not by a whole lot, but consistently faster). My conclusion: when checking the contents of a beginning of a string, use LIKE instead of SUBSTR.

From http://www.quest-pipelines.com/pipelines/plsql/tips00.htm#august

Wildcard Characters and the LIKE Operator

Let’s explore a nuance of the LIKE operator…

expression LIKE 'pattern' [ESCAPE 'escape-character']

I bet just about every one of you is aware of the two wildcard characters used in Oracle’s SQL for string comparisons: _ Match to any single character in this position

% Match to any number of characters in this position

(As you can see, “*” is not a wildcard character in SQL.)

These wildcard characters come in handy when using the LIKE operator. If, for example, I want to display the names of everyone who likes any variety of chocolate ice cream, I could write this query:

SELECT name

  FROM general_preference

 WHERE flavor LIKE 'CHOC%'

   AND topic = 'ICE CREAM';

And if I need to identify all companies placing orders in the 3rd quarter of the year, that information (“3″) stored (in a very questionable design decision) in the 16th character of the order number, I could write this query:

SELECT company_id

  FROM order

 WHERE order_id LIKE RPAD ('_', '_', 15) || '3%';

Great stuff! OK, but what if our string contains wildcard characters? It is certainly common, for example, to place underscores in the names of database objects (see “general_preference” above). How do I answer the question “Show me all database objects that contain an underscore?” I’d like to write the query as follows:

SELECT object_name

  FROM user_objects

 WHERE object_name LIKE '%_%';

Yet this query would always return the names of all my objects. To write this query, I need to tell SQL to stop using the underscore character as a wildcard, which I can do with the ESCAPE clause:

SELECT object_name

  FROM user_objects

 WHERE object_name LIKE '%^_%' ESCAPE '^';

From http://www.quest-pipelines.com/pipelines/plsql/tips00.htm#july

Login as SYS to test

select * from tab;
3667

select * from tab where tname like ‘%^_%’ escape ‘^’;
2961

select * from tab where tname not like ‘%^_%’ escape ‘^’;
706

Rename tables, columns and constraints in Oracle

In more recent versions of Oracle you can rename tables, columns and constraints.

Here are examples of the syntax:

ALTER TABLE table_name RENAME COLUMN column_name TO
new_column_name; (from Oracle9i Release 2)

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY(column_name);  

ALTER TABLE table_name RENAME CONSTRAINT
constraint_name TO new_constraint_name;

ALTER INDEX index_name RENAME TO new_index_name;

It's also possible to drop a column:
ALTER TABLE table_name DROP COLUMN column_name;

From http://www.oradev.com/rename.jsp