"CREATE TABLE" - Creating New Tables

JDBC for Oracle - Herong's Tutorial Examples

∟"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)
   ...