Creating Database Objects

This tutorial describes how to create tables, stored procedures and other objects in Oracle.

Contents

• Requirements
• General information
• Using SQL*Plus
• Additional information

Requirements

In order to create database objects you have to connect to server. This process is described in details in Connecting to Oracle.

General information

Database objects are created using Data Definition Language (DDL), which is a part of SQL. The DDL statements can be executed on server by account that has necessary privileges.

There are two ways to manipulate a database. You can build DDL statements manually and run them within Oracle SQL*Plus or component like OraQuery. Another way is to use IDE - visual shells that provide graphical user interface to manage database. We will discuss both ways.

Using SQL*Plus

1. Launch the SQL*Plus and authorize yourself.
2. Type the following code and press Enter. This will create first of the tables we'll use for tutorial purposes. :

CREATE TABLE dept (
  deptno INT PRIMARY KEY,
  dname VARCHAR2(14),
  loc VARCHAR2(13)
)

3. Run the following query. This is another table we'll use.

CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr INT,
  hiredate DATE,
  sal FLOAT,
  comm FLOAT,
  deptno INT REFERENCES dept
)

4. These two tables are enough to demonstrate basic functionality. Now you can type exit to exit the SQL*Plus.

Additional information

Actually there are lots of ways to create tables on server. Any tool or component that is capable of running a SQL query, can be used to manage database objects. For example, OracleCommand suits fine for creating objects one by one, while OracleScript is designed for executing series of DDL/DML statements. For information on DDL statements syntax refer to Oracle documentation.