11.1 CREATE TABLE & Oracle Built-In Datatypes

11.1 CREATE TABLE & Oracle Built-In Datatypes

In Oracle SQL, Data Definition Language (DDL) commands build, alter, and remove database structures. Unlike Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) which operate within recoverable transactions, DDL statements—such as CREATE TABLE—execute an implicit COMMIT immediately before and after running. Consequently, DDL changes are permanent and cannot be undone using ROLLBACK.

To construct reliable schemas and pass the Oracle Database SQL (1Z0-071) exam, you must master the CREATE TABLE syntax, schema object naming conventions, built-in datatype storage mechanics, column defaults, identity columns, and table replication using CREATE TABLE ... AS SELECT (CTAS).

---

Oracle Schema Object Naming Rules

When declaring tables, columns, constraints, views, or indexes, names must comply with Oracle's standard identifier rules:

+-------------------------------------------------------------------------+
|                    ORACLE IDENTIFIER NAMING RULES                       |
+-------------------------------------------------------------------------+
| 1. Must begin with an alphabetic character (A-Z, a-z).                  |
| 2. Can contain letters, digits (0-9), _, $, and #.                      |
| 3. Length: 1 to 30 bytes (up to 128 bytes in Oracle 12.2+).             |
| 4. Case-insensitive by default (stored in UPPERCASE in data dictionary).|
| 5. Must not be an Oracle reserved word (e.g., SELECT, FROM, TABLE).     |
| 6. Must be unique within the same schema and namespace.                 |
+-------------------------------------------------------------------------+

Standard (Unquoted) vs. Quoted Identifiers

Exam Tip: Avoid using quoted identifiers in production. If you create a table using CREATE TABLE "emp_records" (...), querying SELECT * FROM emp_records; will raise ORA-00942: table or view does not exist because Oracle looks for EMP_RECORDS in uppercase.

---

Basic CREATE TABLE Syntax

The fundamental syntax for creating a standard relational table is:

CREATE TABLE [schema.]table_name (
    column_1  datatype  [DEFAULT expr]  [inline_constraint],
    column_2  datatype  [DEFAULT expr]  [inline_constraint],
    ...
    [out_of_line_constraint],
    ...
);

---

Describing Tables: DESCRIBE / DESC

Before writing SQL against an unfamiliar table you need its column list, datatypes, and nullability. In SQL*Plus, SQL Developer, and SQLcl the DESCRIBE command (abbreviated DESC) prints exactly that structure:

DESCRIBE employees
-- Abbreviated form, optionally schema-qualified:
DESC hr.employees

Name                  Null?    Type
--------------------- -------- ----------------------
EMPLOYEE_ID           NOT NULL NUMBER(6)
FIRST_NAME                     VARCHAR2(20)
LAST_NAME             NOT NULL VARCHAR2(25)
EMAIL                 NOT NULL VARCHAR2(25)
HIRE_DATE             NOT NULL DATE
JOB_ID                NOT NULL VARCHAR2(10)
SALARY                         NUMBER(8,2)
COMMISSION_PCT                 NUMBER(2,2)
DEPARTMENT_ID                  NUMBER(4)

DESCRIBE Rules Tested on 1Z0-071

1. It is a client tool command, not a SQL statement. SQL*Plus, SQL Developer, and SQLcl interpret DESCRIBE locally instead of sending it to the SQL engine, so it cannot be nested inside a SELECT, used in PL/SQL, or issued by an application through JDBC as SQL.
2. Columns appear in COLUMN_ID order — the same physical order that an INSERT without a column list requires.
3. The Null? column reports only NOT NULL or blank. It shows nullability alone: no constraint names, no default values, no primary/foreign key information, no CHECK conditions.
4. It also works on views and synonyms, displaying the projected column structure of the underlying object.
5. The SQL equivalent — the one you can actually embed in queries, scripts, and application code — is USER_TAB_COLUMNS / ALL_TAB_COLUMNS (Chapter 15):

   SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
   FROM   user_tab_columns
   WHERE  table_name = 'EMPLOYEES'
   ORDER  BY column_id;
   

---

Oracle Built-In Datatypes

Choosing the correct datatype guarantees domain integrity, optimizes physical disk storage, and ensures predictable query sorting and filtering behavior.

                               ORACLE DATATYPES
                                      |
      +---------------+---------------+---------------+---------------+
      |               |               |               |               |
  CHARACTER        NUMERIC        DATETIME           LOB            BINARY
  - VARCHAR2       - NUMBER       - DATE             - CLOB         - RAW
  - CHAR                          - TIMESTAMP        - BLOB         - LONG RAW
  - LONG (legacy)                 - INTERVAL         - BFILE

Character Datatypes: VARCHAR2 vs. CHAR

Blank-Padding Comparison Mechanics

When comparing CHAR and VARCHAR2 values, Oracle uses distinct comparison semantics:

CREATE TABLE code_test (
    char_code    CHAR(5),
    vchar_code  VARCHAR2(5)
);

INSERT INTO code_test VALUES ('ABC', 'ABC');
-- char_code stores 'ABC  ' (length 5)
-- vchar_code stores 'ABC'   (length 3)

-- Query 1: Literal comparison with CHAR
SELECT * FROM code_test WHERE char_code = 'ABC';
-- RETURNS ROW (Oracle blank-pads the literal 'ABC' to 'ABC  ')

-- Query 2: Cross-column comparison
SELECT * FROM code_test WHERE char_code = vchar_code;
-- RETURNS NO ROWS! Non-padded comparison: 'ABC  ' != 'ABC'

---

The NUMBER Datatype: Precision and Scale Mechanics

The NUMBER datatype stores fixed and floating-point numbers with up to 38 decimal digits of precision.

$\text{Syntax: } \text{NUMBER}(p, s)$

• Precision ($p$): Total number of significant decimal digits (range: 1 to 38).
• Scale ($s$): Number of digits to the right (positive) or left (negative) of the decimal point (range: -84 to 127).
• Allowed Integer Digits: $p - s$ digits are permitted to the left of the decimal point.

                 Precision (p = 7): Total Significant Digits
                  <----------------------------------------->
                                  1 2 3 4 5 . 6 7
                  <-----------------------> <------->
                   Integer Digits (p - s=5)  Scale (s=2)

Precision and Scale Rules Matrix

Exam Trap: Exceeding scale causes Oracle to round the fraction silently. Exceeding the allowable integer digits ($p - s$) causes Oracle to reject the statement with ORA-01438: value larger than specified precision allowed for this column.

---

Datetime and LOB Datatypes

Datetime Datatypes

Large Object (LOB) and Binary Datatypes

• CLOB (Character Large Object): Stores single-byte or multi-byte character text data up to $(4\text{GB} - 1) \times \text{DB_BLOCK_SIZE}$ (typically 8TB to 128TB).
• BLOB (Binary Large Object): Stores unstructured binary data (images, audio, PDFs, executables) up to the same multi-terabyte limits.
• RAW(size): Variable-length raw binary data up to 2,000 bytes (or 32,767 extended bytes).
• BFILE: Stores a read-only locator pointer to an operating system file outside the database.

---

DEFAULT Column Values

The DEFAULT clause specifies a value to be automatically assigned when an INSERT statement omits the column or specifies the DEFAULT keyword:

CREATE TABLE orders (
    order_id     NUMBER(10)     PRIMARY KEY,
    order_date   DATE           DEFAULT SYSDATE NOT NULL,
    status       VARCHAR2(20)   DEFAULT 'PENDING',
    priority     NUMBER(1)      DEFAULT 1
);

Rules and Prohibitions for DEFAULT Clauses

1. Valid expressions: Literals ('ACTIVE', 100), deterministic expressions (10 * 5), and built-in SQL functions (SYSDATE, CURRENT_TIMESTAMP, USER).
2. Prohibited in DEFAULT:
   • References to other table columns (e.g., DEFAULT col_a + 10 is invalid).
   • Pseudocolumns such as ROWNUM, LEVEL, PRIOR, or XMLDATA.
   • Subqueries (e.g., DEFAULT (SELECT default_val FROM config) is invalid).
3. Behavior with NULL: If an INSERT explicitly passes NULL into a column with a standard DEFAULT clause, the column is populated with NULL, not the default value.

---

Identity Columns (Oracle Database 12c+)

An Identity Column automates surrogate key generation using an implicit database sequence bound directly to the table column:

$\text{Syntax: } \text{col\_name NUMBER GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY [(options)]}$

CREATE TABLE departments_auto (
    dept_id    NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 10) PRIMARY KEY,
    dept_name  VARCHAR2(30) NOT NULL
);

The Three Identity Options

Identity Column Restrictions

• Only one identity column is permitted per table.
• Must be a numeric datatype (NUMBER, INTEGER); user-defined datatypes are not permitted.
• Cannot specify a separate DEFAULT clause on an identity column.
• NOT NULL and NOT DEFERRABLE are applied implicitly; an inline constraint that conflicts with either raises an error.

---

CREATE TABLE ... AS SELECT (CTAS)

CREATE TABLE ... AS SELECT allows you to create a new table and populate it with rows from an existing query in a single DDL operation:

-- Copy table structure and data for department 50
CREATE TABLE emp_dept50 AS
SELECT employee_id, first_name, last_name, salary * 12 AS annual_salary
FROM hr.employees
WHERE department_id = 50;

CTAS Rules & Constraint Inheritance Matrix

+-------------------------------------------------------------------------+
|                     CTAS CONSTRAINT INHERITANCE RULES                   |
+-------------------------------------------------------------------------+
| Constraint / Object Type  | Inherited in New Table? | Action Required   |
| :------------------------ | :---------------------- | :---------------- |
| NOT NULL Constraints      | YES (explicit only)     | None              |
| DEFAULT Values            | NO (Ignored)            | Redefine via ALTER|
| PRIMARY KEY Constraints   | NO (Ignored)            | Add via ALTER     |
| FOREIGN KEY Constraints   | NO (Ignored)            | Add via ALTER     |
| UNIQUE Constraints        | NO (Ignored)            | Add via ALTER     |
| CHECK Constraints         | NO (Ignored)            | Add via ALTER     |
| Table Indexes             | NO (Ignored)            | Recreate manually |
+-------------------------------------------------------------------------+

Critical CTAS Guidelines

1. Column Aliases Required for Expressions: Any calculated expression, literal, or function in the SELECT list must have an explicit column alias, or the statement will fail with ORA-00998: must name this expression with a column alias.
2. Structure-Only Copy: To copy the table schema without copying any data rows, use an impossible condition in the WHERE clause:

   CREATE TABLE emp_template AS
   SELECT * FROM hr.employees WHERE 1 = 2;
   

3. Only Explicit NOT NULL Survives: Oracle carries over a NOT NULL constraint only when it was explicitly created on the source column and the subquery selects that column directly (not wrapped in an expression). A NOT NULL that Oracle generated implicitly — for example, the one behind a PRIMARY KEY — is not carried over.
4. Column DEFAULT Values Are Lost: CREATE TABLE ... AS SELECT does not copy DEFAULT clauses. If ORDER_DATE was DEFAULT SYSDATE in the source table, the CTAS copy has no default at all and must be re-declared with ALTER TABLE ... MODIFY (order_date DEFAULT SYSDATE).