Oracle Data Definition Language

To create, change and destroy tables in the database we use the Data Definition Language (DDL).

These are the three main commands:

CREATE TABLE
ALTER TABLE
DROP TABLE

Create Table

To create the COUNTRY table used in the previous examples, we execute this command:

CREATE TABLE country (
  ctry_id         NUMBER,
  ctry_name       VARCHAR2(50),
  ctry_area       NUMBER,
  ctry_pop        NUMBER,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)
);

Each column must include a data type. The most common data types are:

VARCHAR2(n) for variable length character strings up to 4000 bytes.
NUMBER for positive and negative, integer and decimal numbers.
DATE for date and time with second granularity.
TIMESTAMP for date and time with time zone and fractions of a second granularity (to 9 decimal places).
CLOB and BLOB to store character large objects and binary large objects.

Depending on the data type used, Oracle will only allow appropriate values to be entered into the column. For example, DATE columns can only hold valid dates, NUMBER columns can only contain valid numbers and VARCHAR2 columns can contain any characters but only up to the size specified in brackets.

Describe Table

To see the definition of a table use the DESC[RIBE] command.

 
DESC country;

Which produces this output:

Name            Null     Type
--------------- -------- ------------------
CTRY_ID                  NUMBER
CTRY_NAME                VARCHAR2(50)
CTRY_AREA                NUMBER
CTRY_POP                 NUMBER
POP_UPD_ON               DATE
CURRENCY                 VARCHAR2(50)

6 rows selected

Alter Table

We can change the structure of a table using the ALTER TABLE statement. Here are a few examples:

-- Remove a column
ALTER TABLE country DROP COLUMN currency;

-- Add a single column
ALTER TABLE country ADD crncy VARCHAR(50);

-- Add multiple columns
ALTER TABLE country ADD (
  calling_code NUMBER,
  gdp NUMBER
);

-- Rename a column
ALTER TABLE country RENAME COLUMN crncy TO currency;

-- Rename a table
ALTER TABLE country RENAME TO ctry;

Drop Table

Finally, we need a way to remove unwanted tables from the database. For this we use the DROP TABLE command.

 
DROP TABLE country;

DDL and Transactions

One important point to remember is that Oracle will do an implicit COMMIT before and after every DDL statement. In other words DDL statements do NOT execute in a transactional context.

For example, let's go back to our COUNTRY table.

SELECT *
FROM country;

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
14	Germany	137882	82046000	30-nov-08	Euro
48	Ghana	92100	23837000	null	Cedi
53	Australia	2969907	21884000	4-sep-09	Australian Dollar
73	Greece	50949	11257285	1-Jan-09	Euro
122	Georgia	26900	4382100	1-Jan-09	Lari
123	New Zealand	104428	4320300	4-sep-09	New Zealand Dollar
147	Gambia	4361	1705000	null	Dalasi
149	Gabon	103347	1475000	null	CFA franc

All the data in the table is accidentally deleted.

DELETE FROM country;

CREATE TABLE t1 (c1 NUMBER);

ROLLBACK;

SELECT *
FROM country;

-- 0 rows selected

We rolback in an attempt to salvage the data, but a DDL statement (CREATE TABLE) has been issued in the mean time and an implicit COMMIT statement has already been executed.

quick start