Oracle CREATE TABLE Command in PL/SQL with 10 Examples

To create a table in Oracle, you use the CREATE TABLE command, which allows you to define columns, apply constraints such as primary and foreign keys, enforce NOT NULL rules, and set data types, including dates. This guide explains all these functionalities with practical examples. It covers how to create tables from scratch, copy them from existing ones, and apply different constraints. We will also explore how dbForge Studio for Oracle can simplify and enhance table management. 

Table of contents

• Oracle CREATE TABLE statement syntax
  • CREATE TABLE example
• CREATE TABLE with PRIMARY KEY constraint
• CREATE TABLE with FOREIGN KEY
• CREATE TABLE with NOT NULL column
• CREATE TABLE with a date column
• Oracle alternative to SQL CREATE TABLE IF NOT EXISTS
• Create a new table from another table using CREATE TABLE AS SELECT
• CREATE TABLE from SELECT in PL/SQL
• How to create and insert data into a temporary table
• CREATE TABLE in a wink using dbForge for Oracle
• Conclusion

Before creating tables in Oracle, make sure you have the necessary system privileges. Typically, you need the CREATE TABLE privilege in your own schema, or the CREATE ANY TABLE privilege to create tables in another user’s schema. Without these privileges, attempts to run the command will result in permission errors. 

Additionally, the examples in this guide are based on Oracle Database 19c, but the same methods apply across other supported Oracle versions, including 10g, 11g, and 12c. Where relevant, we will point out version-specific features, so you know exactly which options are available in your environment.

Oracle CREATE TABLE statement syntax

Here is the general syntax to create a table in an Oracle database: 

CREATE TABLE schema_name.table_name ( 
    column_1 data_type column_constraint, 
    column_2 data_type column_constraint, 
    ... 
    table_constraint 
); 

Let’s break down each part of the syntax: 

• schema_name.table_name – specifies the schema and the name of the new table.
• column_1, column_2 – placeholders for the column names.
• data_type – defines the type of data the column can hold (e.g., NUMBER, VARCHAR2, DATE).
• column_constraint – optional constraint applied to a column, such as NOT NULL, PRIMARY KEY, or CHECK.
• table_constraint – constraints that apply to the entire table, such as PRIMARY KEY, FOREIGN KEY, or CHECK.

Later in this guide, you will see this syntax in action. 

CREATE TABLE example

The following example shows how to create a three-column table named employees: 

CREATE TABLE employees
( employee_id number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  city varchar2(50)
); 

• Column 1 is named employee_id and has a number datatype (maximum 10 digits in length). It cannot contain null values.
• Column 2 is named employee_name and has a varchar2 datatype (50 maximum characters in length) and cannot contain null values as well.
• Column 3 is named city and has a varchar2 datatype. Unlike the previous two, this column can contain null values.

CREATE TABLE with PRIMARY KEY constraint

To define a primary key for a table in your Oracle database, you can use our previously created Employees table, edit the Oracle SQL CREATE TABLE statement, and define the employee_id as the primary key: 

CREATE TABLE employees
( employee_id number(10) NOT NULL,
  employee_name varchar2(50) NOT NULL,
  city varchar2(50),
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
); 

The PRIMARY KEY clause allows specifying a column as your primary key. You can use the primary key syntax to identify each unique row in the table. In contrast to other databases, Oracle enables a table to include only one primary key, and each field of the primary key must contain values other than NULL in order for the table to be considered valid. In the example above, we define the employee_id column as the Primary Key Column.

CREATE TABLE with FOREIGN KEY

Let’s take a look at the syntax for the Oracle CREATE TABLE statement FOREIGN key. The syntax can be defined at the column and table level: 

CREATE TABLE table_name
( 
col1 datatype [ NULL | NOT NULL ],
col2 datatype [ NULL | NOT NULL ],
...
col_n datatype [ NULL | NOT NULL ]
constraint <name> FOREIGN KEY (col1,col2) REFERENCES table(col1,col2)
)  tablespace <tablespace name>

CREATE TABLE table_name
(
col1 datatype [ NULL | NOT NULL ] constraint <name> primary key
,
col2 datatype [ NULL | NOT NULL ],
...
col_n datatype [ NULL | NOT NULL ]
)  tablespace <tablespace name>;
CREATE TABLE dept
( dept_id number(10) NOT NULL,
dept_name varchar2(50) NOT NULL,
CONSTRAINT dept_pk PRIMARY KEY (dept_id)
);

CREATE TABLE emp 
( emp_no number(10) NOT NULL,
emp_name varchar2(50) NOT NULL,
dept_id number(10),
salary number(6),
CONSTRAINT emp_pk PRIMARY KEY (emp_no),
CONSTRAINT dept_fk
FOREIGN KEY (dept_id)
REFERENCES dept(dept_id) ); 

In this syntax: 

• Column 1 is labeled emp_no, and it is formatted as a number, which means that it cannot include any null values.
• Column 2 is titled emp_name, which is built as varchar2(50) and cannot include any null values.
• Column 3 is named dept_id and has a number datatype.
• Column 4 is called salary and is also formatted as a number column.
• Table level primary key constraint emp_pk is defined on the key (emp_no).
• Table level foreign key constraints dept_fk  which references dept table dept_id.

CREATE TABLE with NOT NULL column

To ensure a column always contains a value in Oracle, use the NOT NULL constraint. This constraint makes sure that the column cannot remain empty (NULL) when inserting or updating data. It’s one of the most common integrity rules in database design because it guarantees that critical fields always have valid entries. 

Here is an example of creating a table with a NOT NULL column: 

CREATE TABLE employees_bdays ( 
    emp_name VARCHAR2(30), 
    bday DATE, 
    emp_id VARCHAR2(15) NOT NULL 
); 

In this case, the emp_id column must contain a value for every row.

Using NOT NULL when creating a table in Oracle database is especially useful for identifiers, codes, or other essential attributes where missing values would cause data inconsistency or errors in reporting.

CREATE TABLE with a date column

To define a date column in Oracle, use the DATE data type. When inserting data, apply the TO_DATE() function to ensure proper formatting and avoid errors. This function converts a character string into a date, based on the format model you specify. Correct formatting is crucial because Oracle needs to interpret the string consistently. Regional or local formats (e.g., MM/DD/YYYY vs. DD/MM/YYYY) can otherwise cause misinterpretation or failed inserts. 

Example syntax:

CREATE TABLE employees_bdays (
bday DATE
);

Inserting values with TO_DATE():

TO_DATE('01 December 2021','DD Month YYYY') 
TO_DATE('01/12/2021','DD/MM/YYYY') 

Oracle alternative to SQL CREATE TABLE IF NOT EXISTS

Oracle does not support CREATE TABLE IF NOT EXISTS. Instead, you can explicitly check if the table exists before creating it or handle the error Oracle might throw gracefully with the Oracle’s procedural extension to SQL known as Procedural Language/Structured Query Language (PL/SQL). Without any of these approaches, if you try to create a table with an existing name, you may get an error ORA-00955: name is already in use by an existing object.  

Here is an example of how you can avoid this using an anonymous PL/SQL block: 

BEGIN 
   EXECUTE IMMEDIATE 'CREATE TABLE employees ( 
      emp_id NUMBER PRIMARY KEY, 
      emp_name VARCHAR2(50) 
   )'; 
EXCEPTION 
   WHEN OTHERS THEN 
      IF SQLCODE = -955 THEN 
         DBMS_OUTPUT.PUT_LINE('Table already exists.'); 
      ELSE 
         RAISE; 
      END IF; 
END; 
/ 

This approach ensures your script continues running without failing if the table already exists, while still alerting you to the condition. 

Create a new table from another table using CREATE TABLE AS SELECT

If you want to copy table data and insert it into another one, here is the syntax to achieve this 

CREATE TABLE table_name AS (
SELECT select_query 
);

This approach is also referred to as CREATE TABLE AS SELECT (CTAS). 

With this format, you can either enter the table_name for your new table or use the SELECT query to copy it. Also, you can enter SELECT * FROM old_table if you need to copy all the data to the new table. If you need to restrict the values to be copied across, the WHERE clause will be helpful for you.

CREATE TABLE from SELECT in PL/SQL

Oracle allows you to create a new table based on the results of a query using the CREATE TABLE … AS SELECT (CTAS) syntax. In pure SQL, this command is often used to duplicate structures or copy data quickly. In PL/SQL, the same command can be executed with EXECUTE IMMEDIATE, which makes it useful inside anonymous blocks, stored procedures, or scheduled jobs. 

Here is the basic syntax: 

CREATE TABLE new_table 
AS 
SELECT * FROM old_table; 

For example, you might create a backup table during a PL/SQL job to preserve a snapshot of data before running updates: 

BEGIN 
   EXECUTE IMMEDIATE 'CREATE TABLE employees_backup AS SELECT * FROM employees'; 
END; 
/ 

This approach copies both the structure and the data from the source table into the new one. You can also create temporary working tables using CTAS to process subsets of data without affecting the original. 

How to create and insert data into a temporary table

Temporary tables are useful for storing data that is only needed during a session or a single transaction. They are commonly used for session-specific logs, staging intermediate results, or handling large datasets during complex data processing without impacting permanent tables. To create a temporary table in your Oracle database, use the CREATE GLOBAL TEMPORARY TABLE statement.  

You can control how long the data persists by using the ON COMMIT clause: 

• ON COMMIT DELETE ROWS – clears the data after each transaction.
• ON COMMIT PRESERVE ROWS – keeps the data available for the entire session. 

Here is an example of creating a transaction-specific temporary table: 

CREATE GLOBAL TEMPORARY TABLE admin_work_area ( 
    startdate DATE, 
    enddate DATE, 
    operation CHAR(20) 
) ON COMMIT DELETE ROWS; 

You can insert and query data in this table just like a permanent one, but the rows will be cleared based on the option you choose. 

CREATE TABLE in a wink using dbForge for Oracle

To quickly create a table in Oracle using a GUI, follow these steps in dbForge Studio for Oracle. dbForge Studio for Oracle is an all-in-one IDE that is designed to help database professionals including DBAs, developers, and analysts save time by streamlining database development, administration, and management. It supports tasks such as data synchronization between servers, automated database processes, and schema design—all within an intuitive interface. Below is a step-by-step walkthrough of how to use the dbForge Studio for Oracle to create tables. 

Step-by-step walkthrough: create a table via GUI 

1. Open dbForge Studio for Oracle. On the first launch, the Database Connection Properties window will appear automatically. To open it manually, go to Database, select New Connection. 

2. Fill in the required fields and click Test Connection.

3. If configured correctly, you’ll see this confirmation message.

4. Connect to your Oracle server and select the user you want to create a new table for. 
Check this article to read more on how to create a new user in Oracle.
5. In the Database Explorer, right-click the required schema, select New Object, and choose Table. 

6. In the Table Editor, complete the following: 

a. In the Name text box, enter a table name. 
b. In the grid below the Name text box, define column names, data types, and constraints (e.g., NOT NULL). 
c. Navigate to the right-hand panel to review and adjust your column properties. 

7. Check the SQL query panel to see that all changes are reflected in real time. 
8. Once ready, click Apply Changes to create your table. 

Features that boost productivity 

Beyond simplifying table creation, dbForge Studio for Oracle offers features that speed up database work. These include: 

• Automatic SQL syntax generation while you design objects visually.
• Database diagrams and visual modelling to manage complex schemas.
• Built-in tools for data comparison, synchronization, and export.
• Productivity boosters like code completion, formatting, and debugging.

With these features, you can manage Oracle databases more efficiently while minimizing manual SQL coding.

Conclusion

Creating tables is one of the most fundamental tasks in Oracle Database, as it provides the structure for organizing and managing data effectively. In this guide, we explored 10 different approaches to creating tables, from basic definitions with constraints to more advanced techniques like using CREATE TABLE AS SELECT (CTAS), defining primary and foreign keys, and working with temporary tables. 

• Methods covered: We explored creating tables with constraints (NOT NULL, PK, FK), adding date columns, building tables from existing ones (CTAS), handling conditional creation, and using global temporary tables for session- or transaction-specific data.
• Why it matters: These examples show that Oracle table creation is versatile, giving you multiple ways to define structures depending on your use case—from permanent storage to lightweight, temporary staging.

Whether you prefer executing SQL manually or working through an IDE, tools like dbForge Studio for Oracle can streamline the process, automate repetitive tasks, and boost productivity with built-in features for Oracle PL/SQL performance tuning.

Useful links

• Create a Database in Oracle
• Rename the table in Oracle
• PL/SQL Formatter