"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

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Java DB (Derby) - DML Statements

 Java DB (Derby) - ResultSet Objects of Queries

 Java DB (Derby) - PreparedStatement

 MySQL Installation on Windows

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

 MySQL - JBDC CallableStatement

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

 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

 Microsoft SQL Server Express Edition

 Microsoft JDBC Driver for SQL Server

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Summary of JDBC Drivers and Database Servers

 Using Connection Pool with JDBC

 Archived Tutorials

 References

 Full Version in PDF/EPUB