Closed
Description
Issue Description:
During the creation of the SmallBank benchmark database in Oracle Database 19c/23c, an error occurs due to the lack of native support for the 'BIGINT' datatype in Oracle SQL. The execution of the 'benchmarks/smallbank/ddl-generic.sql' script results in an 'ORA-00902: invalid datatype' error.
[INFO ] 2023-07-13 04:08:35,831 [main] com.oltpbenchmark.DBWorkload main (181) - ======================================================================
[INFO ] 2023-07-13 04:08:35,900 [main] com.oltpbenchmark.DBWorkload main (406) - Creating new SMALLBANK database...
[ERROR] 2023-07-13 04:08:41,968 [main] com.oltpbenchmark.DBWorkload main (411) - Unexpected error when creating benchmark database tables.
java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:122)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1199)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2504)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2459)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:327)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:125)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:82)
at com.oltpbenchmark.util.ScriptRunner.runScript(ScriptRunner.java:71)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:234)
at com.oltpbenchmark.api.BenchmarkModule.createDatabase(BenchmarkModule.java:213)
at com.oltpbenchmark.DBWorkload.runCreator(DBWorkload.java:628)
at com.oltpbenchmark.DBWorkload.main(DBWorkload.java:407)
Caused by: Error : 902, Position : 40, Sql = CREATE TABLE ACCOUNTS ( custid BIGINT NOT NULL, name VARCHAR(64) NOT NULL, CONSTRAINT pk_accounts PRIMARY KEY (custid) ), OriginalSql = CREATE TABLE ACCOUNTS ( custid BIGINT NOT NULL, name VARCHAR(64) NOT NULL, CONSTRAINT pk_accounts PRIMARY KEY (custid) ), Error Msg = ORA-00902: invalid datatype
Description:
To address this compatibility issue, a dedicated Oracle-specific SQL file, 'benchmarks/smallbank/ddl-oracle.sql', is introduced. In this file, the 'BIGINT' datatype is replaced with 'NUMBER(19, 0)' to ensure compatibility with Oracle SQL. This change allows the SmallBank benchmark to be loaded and executed successfully without requiring any further modifications for Oracle SQL.
-- (benchmarks/smallbank/ddl-oracle.sql)
DROP TABLE IF EXISTS CHECKING;
DROP TABLE IF EXISTS SAVINGS;
DROP TABLE IF EXISTS ACCOUNTS;
CREATE TABLE ACCOUNTS (
custid NUMBER(19, 0) NOT NULL,
name VARCHAR(64) NOT NULL,
CONSTRAINT pk_accounts PRIMARY KEY (custid)
);
CREATE INDEX IDX_ACCOUNTS_NAME ON ACCOUNTS (name);
CREATE TABLE SAVINGS (
custid NUMBER(19, 0) NOT NULL,
bal FLOAT NOT NULL,
CONSTRAINT pk_savings PRIMARY KEY (custid),
FOREIGN KEY (custid) REFERENCES ACCOUNTS (custid)
);
CREATE TABLE CHECKING (
custid NUMBER(19, 0) NOT NULL,
bal FLOAT NOT NULL,
CONSTRAINT pk_checking PRIMARY KEY (custid),
FOREIGN KEY (custid) REFERENCES ACCOUNTS (custid)
);