Try this (you will need to make minor changes to reflect your database
connection):
package ddlutils;
import java.sql.Connection;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
public class DemoDDLUtils {
/**
* Creates a new instance of DemoDDLUtils
*/
public DemoDDLUtils() {
}
public static void main(String[] args) throws Exception {
DemoDDLUtils reader = new DemoDDLUtils();
reader.run();
}
public void run() throws Exception {
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@localhost:1521:orcl");
ods.setUser("my_user");
ods.setPassword("my_password");
String sql = "CREATE TABLE MY_TABLE( " +
"prop_name VARCHAR2(200) NOT NULL PRIMARY KEY, " +
"prop_value VARCHAR2(200), prop_value_ext LONG RAW NULL)";
Connection conn = ods.getConnection();
Statement stmt = conn.createStatement();
stmt.execute(sql);
stmt.close();
conn.close();
String schema = "MY_SCHEMA";
Platform platform = PlatformFactory.createNewPlatformInstance(ods);
Database db = platform.readModelFromDatabase("test", null,
schema, null);
dumpDb(db);
platform.alterTables(db, false, false, false);
}
private void dumpDb(Database database) {
System.out.println("Name=" + database.getName());
System.out.println("Version=" + database.getVersion());
Table[] tables = database.getTables();
System.out.println("Tables--------------------");
for (int t=0; t<tables.length; t++) {
System.out.println("Table: " + tables[t].getName());
Column[] columns = tables[t].getColumns();
for (int c=0; c<columns.length; c++) {
System.out.println(" COLUMN: " +
columns[c].toVerboseString());
}
}
}
}
and this is the output I get.
Name=test
Version=null
Tables--------------------
Table: MY_TABLE
COLUMN: Column [name=PROP_NAME; javaName=null; type=VARCHAR;
typeCode=12; size=200; required=true; primaryKey=true;
autoIncrement=false; defaultValue=null; precisionRadix=200; scale=0]
COLUMN: Column [name=PROP_VALUE; javaName=null; type=VARCHAR;
typeCode=12; size=200; required=false; primaryKey=false;
autoIncrement=false; defaultValue=null; precisionRadix=200; scale=0]
COLUMN: Column [name=PROP_VALUE_EXT; javaName=null;
type=LONGVARBINARY; typeCode=-4; size=0; required=false;
primaryKey=false; autoIncrement=false; defaultValue=null;
precisionRadix=0; scale=0]
PK: Column [name=PROP_NAME; javaName=null; type=VARCHAR; typeCode=12;
size=200; required=true; primaryKey=true; autoIncrement=false;
defaultValue=null; precisionRadix=200; scale=0]
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterTable
INFO: Column PROP_VALUE_EXT in table MY_TABLE differs from current
specification
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DR$POLICY_TAB can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table CS_SRS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OGIS_SPATIAL_REFERENCE_SYSTEMS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_ANGLE_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_AREA_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_DATUMS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_DIST_UNITS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_ELLIPSOIDS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SDO_PROJECTIONS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table USER_CS_SRS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table USER_TRANSFORM_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AUDIT_ACTIONS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AW$CWMTOECM can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table AW$EXPRESS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DUAL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table ODCI_SECOBJ$ can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table ODCI_WARNINGS$ can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OLAPTABLEVELS can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table OLAPTABLEVELTUPLES can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table PSTUBTBL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table STMT_AUDIT_OPTION_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table SYSTEM_PRIVILEGE_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table TABLE_PRIVILEGE_MAP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table DEF$_TEMP$LOB can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table HELP can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$CHARSET can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$CRAWLER_CONFIG_DEFAULT can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$LANG can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$MIMETYPES can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WK$SYS_CONFIG can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table WM$WORKSPACES_TABLE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ACL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ALL_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ANY can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ANYATTR can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRGROUP_DEF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRGROUP_REF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ATTRIBUTE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$CHOICE_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$COMPLEX_TYPE can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$ELEMENT can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$GROUP_DEF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$GROUP_REF can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SCHEMA can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SEQUENCE_MODEL can be dropped
31-Jan-2006 13:54:58 org.apache.ddlutils.platform.SqlBuilder alterDatabase
INFO: Table XDB$SIMPLE_TYPE can be dropped
Exception in thread "main" org.apache.ddlutils.DynaSqlException: Error
while executing SQL -- Column PROP_VALUE_EXT in table MY_TABLE differs
from current specification
-- Table DR$POLICY_TAB can be dropped
-- Table CS_SRS can be dropped
-- Table OGIS_SPATIAL_REFERENCE_SYSTEMS can be dropped
-- Table SDO_ANGLE_UNITS can be dropped
-- Table SDO_AREA_UNITS can be dropped
-- Table SDO_DATUMS can be dropped
-- Table SDO_DIST_UNITS can be dropped
-- Table SDO_ELLIPSOIDS can be dropped
-- Table SDO_PROJECTIONS can be dropped
-- Table USER_CS_SRS can be dropped
-- Table USER_TRANSFORM_MAP can be dropped
-- Table AUDIT_ACTIONS can be dropped
-- Table AW$CWMTOECM can be dropped
-- Table AW$EXPRESS can be dropped
-- Table DUAL can be dropped
-- Table ODCI_SECOBJ$ can be dropped
-- Table ODCI_WARNINGS$ can be dropped
-- Table OLAPTABLEVELS can be dropped
-- Table OLAPTABLEVELTUPLES can be dropped
-- Table PSTUBTBL can be dropped
-- Table STMT_AUDIT_OPTION_MAP can be dropped
-- Table SYSTEM_PRIVILEGE_MAP can be dropped
-- Table TABLE_PRIVILEGE_MAP can be dropped
-- Table DEF$_TEMP$LOB can be dropped
-- Table HELP can be dropped
-- Table WK$CHARSET can be dropped
-- Table WK$CRAWLER_CONFIG_DEFAULT can be dropped
-- Table WK$LANG can be dropped
-- Table WK$MIMETYPES can be dropped
-- Table WK$SYS_CONFIG can be dropped
-- Table WM$WORKSPACES_TABLE can be dropped
-- Table XDB$ACL can be dropped
-- Table XDB$ALL_MODEL can be dropped
-- Table XDB$ANY can be dropped
-- Table XDB$ANYATTR can be dropped
-- Table XDB$ATTRGROUP_DEF can be dropped
-- Table XDB$ATTRGROUP_REF can be dropped
-- Table XDB$ATTRIBUTE can be dropped
-- Table XDB$CHOICE_MODEL can be dropped
-- Table XDB$COMPLEX_TYPE can be dropped
-- Table XDB$ELEMENT can be dropped
-- Table XDB$GROUP_DEF can be dropped
-- Table XDB$GROUP_REF can be dropped
-- Table XDB$SCHEMA can be dropped
-- Table XDB$SEQUENCE_MODEL can be dropped
-- Table XDB$SIMPLE_TYPE can be dropped
at
org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:218)
at
org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:484)
at
org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:402)
at ddlutils.DemoDDLUtils.run(DemoDDLUtils.java:76)
at ddlutils.DemoDDLUtils.main(DemoDDLUtils.java:47)
Caused by: java.sql.SQLException: ORA-00900: invalid SQL statement
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at
oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at
oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at
oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:796)
at
org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:202)
... 4 more
Java Result: 1
Thomas Dudziak wrote:
On 1/31/06, Tim Dudgeon <[EMAIL PROTECTED]> wrote:
If you read the model of an Oracle database and then try an
alterTables() with this unchanged model DDLUtils incorrectly thinks it
needs to make lots of changes to the database and can corrupt the
database. For instance if you do this:
Database db = platform.readModelFromDatabase("test", null, "MYSCHEMA",
null);
platform.alterTables(db, false, false, false);
then DDLUtils want to make lots of changes to the database, even though
you have made no changes at all.
I do NOT recommend you try this for yourself!
Please be more specific, e.g. post an original model and what DdlUtils
reads back.
Tom
