SmallBank Compatibility Issue: 'BIGINT' Datatype Not Supported in Oracle SQL

[AshishVirdi]

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)
);

[apavlo]

@AshishVirdi Please send a PR.

[bpkroth]

@AshishVirdi can you please check to see if this is still relevant after #379 got merged? Thanks!