"CREATE TABLE" - Creating New Tables

This section describes how to create a table through the JDBC interface.

In order to do JDBC tests with Oracle database, I created a new table through JDBC interface using this program:

/* OracleCreateTable.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
import javax.sql.*;
public class OracleCreateTable {
  public static void main(String [] args) {
    Connection con = null;
    try {

// Setting up the DataSource object
      oracle.jdbc.pool.OracleDataSource ds
        = new oracle.jdbc.pool.OracleDataSource();
      ds.setDriverType("thin");
      ds.setServerName("localhost");
      ds.setPortNumber(1521);
      ds.setDatabaseName("XE");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");

// Getting a connection object
      con = ds.getConnection();

// Creating a database table
      Statement sta = con.createStatement();
      int count = sta.executeUpdate(
        "CREATE TABLE Profile ("
        + " ID INTEGER PRIMARY KEY,"
        + " FirstName VARCHAR(20) NOT NULL,"
        + " LastName VARCHAR(20),"
        + " Point REAL DEFAULT 0.0,"
        + " BirthDate DATE DEFAULT '31-Dec-1988',"
        + " ModTime TIMESTAMP DEFAULT '31-Dec-2006 11:59:59.999')");
      System.out.println("Table created.");
      sta.close();

      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

I ran this sample program, I got the "ORA-01031: insufficient privileges" exception:

herong> java -cp .;ojdbc11.jar OracleCreateTable

java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
   at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
   at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
   at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
   at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
   at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
   at oracle.jdbc.driver.T4CStatement.executeForRows(...)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(...)
   at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(...)
   at oracle.jdbc.driver.OracleStatement.executeUpdate(...)
   at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(...
   at OracleCreateTable.main(OracleCreateTable.java:26)

I had to log in with SQL*Plus using Windows user group authentication and grant "CREATE TABLE" privilege to user "Herong":

herong> sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> GRANT CREATE TABLE TO Herong;
Grant succeeded.

I ran this sample program again, I got the "ORA-01950: no privileges on tablespace" exception:

herong> java -cp .;ojdbc11.jar OracleCreateTable

java.sql.SQLSyntaxErrorException: ORA-01950: no privileges on
   tablespace 'SYSTEM'
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
   ...

Okay, I had to move user "Herong" to use "USERS" tablespace and gave some space quota.

herong> sqlplus /nolog

SQL> connect / as sysdba
Connected.

SQL> ALTER USER Herong DEFAULT TABLESPACE USERS;
User altered.

SQL> ALTER USER Herong QUOTA 10M ON USERS;
User altered.

If you run this program again, table "Profile" will be created. But you run it one more time, you will get the "ORA-00955: name is already used by an existing object" exception:

herong> java -cp .;ojdbc11.jar OracleCreateTable

Table created.

herong> java -cp .;ojdbc11.jar OracleCreateTable

java.sql.SQLSyntaxErrorException:
ORA-00955: name is already used by an existing object
   at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
   ...

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Oracle Express Edition Installation on Windows

Oracle JDBC Drivers

 Oracle JDBC Drivers Overview

 JDBC Thin Client-Side Driver Installation

 Loading JDBC Driver Class - ojdbc16.jar

 JDBC Driver Connection URL

 Creating Connections with DataSource Class

 DataSource Error - makeURL() Failed

 Getting Driver and Server Information

"CREATE TABLE" - Creating New Tables

 "INSERT INTO" - Inserting New Data Rows

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Using Connection Pool with JDBC

 Archived Tutorials

 References

 Full Version in PDF/EPUB