/**
Code revised from
Database Metadata with JDBC
A sample from
JDBC Metadata, MySQL, and Oracle Recipes
by Mahmoud Parsian
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class CreateTableAllTypesInOracle {
public static Connection getConnection() throws Exception {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
String username = "scott";
String password = "tiger";
Class.forName(driver); // load Oracle driver
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void main(String[] args) {
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement("CREATE TYPE varray_type is VARRAY(5) OF VARCHAR(10)");
pstmt.executeUpdate();
// Create an OBJECT type
pstmt = conn.prepareStatement("CREATE TYPE oracle_object is OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
pstmt.executeUpdate();
StringBuffer allTypesTable = new StringBuffer("CREATE TABLE oracle_all_types(");
// Column Name Oracle Type Java Type
allTypesTable.append("column_short SMALLINT, "); // short
allTypesTable.append("column_int INTEGER, "); // int
allTypesTable.append("column_float REAL, "); // float; can also be NUMBER
allTypesTable.append("column_double DOUBLE PRECISION, "); // double; can also be FLOAT or NUMBER
allTypesTable.append("column_bigdecimal DECIMAL(13,0), "); // BigDecimal
allTypesTable.append("column_string VARCHAR2(254), "); // String; can also be CHAR(n)
allTypesTable.append("column_characterstream LONG, "); // CharacterStream or AsciiStream
allTypesTable.append("column_bytes RAW(2000), "); // byte[]; can also be LONG RAW(n)
allTypesTable.append("column_binarystream RAW(2000), "); // BinaryStream; can also be LONG RAW(n)
allTypesTable.append("column_timestamp DATE, "); // Timestamp
allTypesTable.append("column_clob CLOB, "); // Clob
allTypesTable.append("column_blob BLOB, "); // Blob; can also be BFILE
allTypesTable.append("column_bfile BFILE, "); // oracle.sql.BFILE
allTypesTable.append("column_array varray_type, "); // oracle.sql.ARRAY
allTypesTable.append("column_object oracle_object)"); // oracle.sql.OBJECT
pstmt.executeUpdate(allTypesTable.toString());
} catch (Exception e) {
// creation of table failed.
// handle the exception
e.printStackTrace();
}
}
}