Oracle #

// build.gradle.kts
dependencies {
    // Oracle JDBC Driver (ojdbc11 for JDK 11+, ojdbc8 for JDK 8)
    implementation("com.oracle.database.jdbc:ojdbc11:23.3.0.23.09")

    // Oracle Connection Pool (UCP — Universal Connection Pool, optional)
    implementation("com.oracle.database.jdbc:ucp11:23.3.0.23.09")

    // HikariCP — more commonly used
    implementation("com.zaxxer:HikariCP:5.1.0")

    // Exposed ORM
    implementation("org.jetbrains.exposed:exposed-core:0.49.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.49.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.49.0")

    // Flyway for Oracle
    implementation("org.flywaydb:flyway-core:10.10.0")
    implementation("org.flywaydb:flyway-database-oracle:10.10.0")
}

// Format 1: SID (Service Identifier) — the old format
val sidUrl = "jdbc:oracle:thin:@localhost:1521:ORCL"
// jdbc:oracle:thin:@HOST:PORT:SID

// Format 2: Service Name (more modern, recommended)
val serviceUrl = "jdbc:oracle:thin:@//localhost:1521/orclpdb1"
// jdbc:oracle:thin:@//HOST:PORT/SERVICE_NAME

// Format 3: TNS with a full descriptor
val tnsUrl = """
    jdbc:oracle:thin:@(DESCRIPTION=
        (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
        (CONNECT_DATA=(SERVICE_NAME=orclpdb1)))
""".trimIndent().replace("\n", "").replace(" ", "")

// Oracle Cloud Database (ATP/ADW) — uses a wallet
val cloudUrl = "jdbc:oracle:thin:@mydb_high?TNS_ADMIN=/path/to/wallet"

// For development with Oracle XE (Express Edition)
val xeUrl = "jdbc:oracle:thin:@//localhost:1521/xe"

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object DatabaseOracle {
    private val dataSource: HikariDataSource by lazy {
        val config = HikariConfig().apply {
            jdbcUrl = "jdbc:oracle:thin:@//localhost:1521/orclpdb1"
            driverClassName = "oracle.jdbc.OracleDriver"
            username = System.getenv("DB_USER") ?: "myapp"
            password = System.getenv("DB_PASSWORD") ?: "password"

            // Pool configuration
            minimumIdle = 2
            maximumPoolSize = 10
            idleTimeout = 300_000
            connectionTimeout = 30_000
            maxLifetime = 1_800_000
            poolName = "Oracle-Pool"

            // Oracle-specific
            connectionTestQuery = "SELECT 1 FROM DUAL"  // Oracle needs FROM DUAL

            // Additional properties for Oracle
            addDataSourceProperty("oracle.jdbc.implicitStatementCacheSize", "20")
            addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "10000")
            addDataSourceProperty("oracle.jdbc.ReadTimeout", "30000")
        }
        HikariDataSource(config)
    }

    fun <T> use(block: (java.sql.Connection) -> T): T {
        return dataSource.connection.use(block)
    }

    fun close() {
        if (!dataSource.isClosed) dataSource.close()
    }
}

fun main() {
    DatabaseOracle.use { connection ->
        connection.createStatement().use { stmt ->
            stmt.executeQuery("SELECT * FROM v\$version WHERE rownum = 1").use { rs ->
                if (rs.next()) println("Oracle: ${rs.getString(1)}")
            }
        }
    }
}

-- 1. AUTO INCREMENT → SEQUENCE
-- MySQL:   id INT AUTO_INCREMENT
-- MSSQL:   id INT IDENTITY(1,1)
-- Oracle:  Use SEQUENCE + TRIGGER or GENERATED ALWAYS AS IDENTITY (12c+)

-- Oracle 12c+ (recommended):
CREATE TABLE produk (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama VARCHAR2(255) NOT NULL
);

-- Older Oracle (pre-12c):
CREATE SEQUENCE seq_produk START WITH 1 INCREMENT BY 1;
-- Then use seq_produk.NEXTVAL in the INSERT

-- 2. DUAL — a one-row table for expressions without a table
SELECT SYSDATE FROM DUAL;          -- current time
SELECT UPPER('kotlin') FROM DUAL;  -- string function
SELECT 1 + 1 FROM DUAL;            -- arithmetic expression

-- 3. Pagination
-- MySQL:  LIMIT 10 OFFSET 20
-- MSSQL:  OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
-- Oracle 12c+: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY (same as MSSQL)
-- Older Oracle: Use a subquery with ROWNUM

-- Older Oracle (pre-12c):
SELECT * FROM (
    SELECT t.*, ROWNUM AS rn FROM (
        SELECT * FROM produk WHERE aktif = 1 ORDER BY nama
    ) t WHERE ROWNUM <= 30  -- offset + limit
) WHERE rn > 20;            -- offset

-- 4. Empty string = NULL (Oracle's biggest gotcha!)
-- In Oracle, '' and NULL are the same thing for VARCHAR2
INSERT INTO pengguna (nama, bio) VALUES ('Budi', '');
-- bio is stored as NULL, not an empty string!

-- 5. No native BOOLEAN
-- MySQL:  BOOLEAN (alias TINYINT(1))
-- MSSQL:  BIT
-- Oracle: NUMBER(1) with the convention 0=false, 1=true
-- Or use CHAR(1) with 'Y'/'N'

-- 6. Text data types
-- MySQL:  VARCHAR, TEXT
-- MSSQL:  NVARCHAR, NVARCHAR(MAX)
-- Oracle: VARCHAR2 (max 32767 chars), CLOB (for large text)

-- 7. Different functions
-- MySQL:  NOW(), IFNULL(), LIMIT
-- MSSQL:  GETDATE(), ISNULL(), TOP
-- Oracle: SYSDATE, NVL(), ROWNUM/FETCH FIRST

-- Create a sequence for IDs (pre-12c)
CREATE SEQUENCE seq_produk
    START WITH 1
    INCREMENT BY 1
    NOCACHE          -- or CACHE 20 for better performance
    NOCYCLE;

-- Create the table
CREATE TABLE produk (
    id              NUMBER          DEFAULT seq_produk.NEXTVAL PRIMARY KEY,
    -- or Oracle 12c+: id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama            VARCHAR2(255)   NOT NULL,
    deskripsi       CLOB,                          -- for long text
    harga           NUMBER(15,2)    NOT NULL,
    stok            NUMBER          DEFAULT 0 NOT NULL,
    kategori        VARCHAR2(100),
    aktif           NUMBER(1)       DEFAULT 1 NOT NULL,  -- 1=true, 0=false
    dibuat_pada     TIMESTAMP       DEFAULT SYSTIMESTAMP,
    diperbarui_pada TIMESTAMP       DEFAULT SYSTIMESTAMP,
    CONSTRAINT ck_aktif CHECK (aktif IN (0, 1)),
    CONSTRAINT ck_harga CHECK (harga >= 0),
    CONSTRAINT ck_stok CHECK (stok >= 0)
);

-- Index
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_produk_aktif ON produk(aktif);

-- Trigger for automatic timestamp updates (Oracle has no ON UPDATE)
CREATE OR REPLACE TRIGGER trg_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW
BEGIN
    :NEW.diperbarui_pada := SYSTIMESTAMP;
END;
/

import java.math.BigDecimal
import java.sql.Clob
import java.sql.ResultSet
import java.sql.Types

data class Product(
    val id: Long = 0,
    val name: String,
    val description: String? = null,
    val price: BigDecimal,
    val stock: Int = 0,
    val category: String? = null,
    val active: Boolean = true
)

class OracleProductRepository {

    private fun ResultSet.toProduct() = Product(
        id        = getLong("id"),
        name      = getString("nama"),
        // CLOB must be read in a special way
        description = getClob("deskripsi")?.let { clob ->
            clob.getSubString(1, clob.length().toInt()).also { clob.free() }
        },
        price     = getBigDecimal("harga"),
        stock     = getInt("stok"),
        category  = getString("kategori"),
        active    = getInt("aktif") == 1  // NUMBER(1) → Boolean
    )

    // INSERT — Oracle uses RETURNING INTO to get the ID
    fun save(product: Product): Product {
        val sql = """
            INSERT INTO produk (nama, deskripsi, harga, stok, kategori, aktif)
            VALUES (?, ?, ?, ?, ?, ?)
        """.trimIndent()

        return DatabaseOracle.use { connection ->
            // Oracle: use RETURNING ... INTO to get the ID
            val sqlWithReturning = """
                INSERT INTO produk (nama, deskripsi, harga, stok, kategori, aktif)
                VALUES (?, ?, ?, ?, ?, ?)
                RETURNING id INTO ?
            """.trimIndent()

            connection.prepareCall(sqlWithReturning).use { stmt ->
                stmt.setString(1, product.name)
                if (product.description != null) stmt.setString(2, product.description)
                else stmt.setNull(2, Types.CLOB)
                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)
                if (product.category != null) stmt.setString(5, product.category)
                else stmt.setNull(5, Types.VARCHAR)
                stmt.setInt(6, if (product.active) 1 else 0)

                stmt.registerOutParameter(7, Types.NUMERIC)
                stmt.execute()

                val newId = stmt.getLong(7)
                product.copy(id = newId)
            }
        }
    }

    // Alternative INSERT with SEQUENCE.NEXTVAL (pre-12c)
    fun saveWithSequence(product: Product): Product {
        val sql = """
            INSERT INTO produk (id, nama, harga, stok, kategori, aktif)
            VALUES (seq_produk.NEXTVAL, ?, ?, ?, ?, ?)
        """.trimIndent()

        return DatabaseOracle.use { connection ->
            // Get the ID to be used first
            val newId = connection.createStatement().use { stmt ->
                stmt.executeQuery("SELECT seq_produk.NEXTVAL FROM DUAL").use { rs ->
                    rs.next(); rs.getLong(1)
                }
            }

            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setBigDecimal(2, product.price)
                stmt.setInt(3, product.stock)
                if (product.category != null) stmt.setString(4, product.category)
                else stmt.setNull(4, Types.VARCHAR)
                stmt.setInt(5, if (product.active) 1 else 0)
                stmt.executeUpdate()
            }

            product.copy(id = newId)
        }
    }

    // SELECT with Oracle 12c+ pagination
    fun findAll(
        category: String? = null,
        page: Int = 1,
        size: Int = 20
    ): List<Product> {
        val offset = (page - 1) * size
        val params = mutableListOf<Any?>()
        val conditions = mutableListOf("aktif = 1")

        if (category != null) {
            conditions.add("kategori = ?")
            params.add(category)
        }

        // Oracle 12c+: OFFSET...FETCH (same as MSSQL)
        val sql = """
            SELECT id, nama, deskripsi, harga, stok, kategori, aktif
            FROM produk
            WHERE ${conditions.joinToString(" AND ")}
            ORDER BY nama
            OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
        """.trimIndent()

        return DatabaseOracle.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                var idx = 1
                params.forEach { p ->
                    when (p) {
                        is String -> stmt.setString(idx++, p)
                        else -> stmt.setObject(idx++, p)
                    }
                }
                stmt.setInt(idx++, offset)
                stmt.setInt(idx, size)

                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduct()) }
                }
            }
        }
    }

    // SELECT with ROWNUM pagination (older Oracle, pre-12c)
    fun findAllLegacy(category: String? = null, offset: Int = 0, limit: Int = 20): List<Product> {
        val innerCondition = if (category != null) "AND kategori = ?" else ""
        val sql = """
            SELECT * FROM (
                SELECT t.*, ROWNUM AS rn FROM (
                    SELECT id, nama, deskripsi, harga, stok, kategori, aktif
                    FROM produk
                    WHERE aktif = 1 $innerCondition
                    ORDER BY nama
                ) t WHERE ROWNUM <= ?
            ) WHERE rn > ?
        """.trimIndent()

        return DatabaseOracle.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                var idx = 1
                if (category != null) stmt.setString(idx++, category)
                stmt.setInt(idx++, offset + limit)  // upper bound
                stmt.setInt(idx, offset)            // lower bound

                stmt.executeQuery().use { rs ->
                    buildList { while (rs.next()) add(rs.toProduct()) }
                }
            }
        }
    }
}

-- Oracle stored procedure (PL/SQL)
CREATE OR REPLACE PROCEDURE sp_tambah_stok (
    p_produk_id IN  NUMBER,
    p_jumlah    IN  NUMBER,
    p_stok_baru OUT NUMBER,
    p_status    OUT VARCHAR2
) AS
    v_stok_lama NUMBER;
BEGIN
    SELECT stok INTO v_stok_lama
    FROM produk
    WHERE id = p_produk_id
    FOR UPDATE;  -- lock the row for update

    UPDATE produk
    SET stok = stok + p_jumlah
    WHERE id = p_produk_id;

    SELECT stok INTO p_stok_baru FROM produk WHERE id = p_produk_id;
    p_status := 'BERHASIL';

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        p_stok_baru := -1;
        p_status := 'PRODUK_TIDAK_DITEMUKAN';
    WHEN OTHERS THEN
        ROLLBACK;
        p_stok_baru := -1;
        p_status := 'ERROR: ' || SQLERRM;
END sp_tambah_stok;
/

data class AddStockResult(val newStock: Int, val status: String)

fun addStockViaStoredProc(productId: Long, quantity: Int): AddStockResult {
    return DatabaseOracle.use { connection ->
        // Oracle syntax: { call procedure_name(?, ?, ?, ?) }
        connection.prepareCall("{ call sp_tambah_stok(?, ?, ?, ?) }").use { stmt ->
            // IN parameters
            stmt.setLong(1, productId)
            stmt.setInt(2, quantity)

            // OUT parameters — register their types
            stmt.registerOutParameter(3, Types.NUMERIC)  // p_stok_baru
            stmt.registerOutParameter(4, Types.VARCHAR)  // p_status

            stmt.execute()

            AddStockResult(
                newStock = stmt.getInt(3),
                status   = stmt.getString(4)
            )
        }
    }
}

// Usage
fun main() {
    val result = addStockViaStoredProc(productId = 1, quantity = 50)
    when {
        result.status == "BERHASIL" -> println("New stock: ${result.newStock}")
        result.status == "PRODUK_TIDAK_DITEMUKAN" -> println("Product not found")
        else -> println("Error: ${result.status}")
    }
}

CREATE OR REPLACE FUNCTION fn_harga_akhir (
    p_harga IN NUMBER,
    p_diskon IN NUMBER
) RETURN NUMBER AS
BEGIN
    RETURN p_harga * (1 - p_diskon / 100);
END fn_harga_akhir;
/

fun calculateFinalPrice(price: BigDecimal, discount: Int): BigDecimal {
    return DatabaseOracle.use { connection ->
        // Oracle function: { ? = call function_name(?, ?) }
        connection.prepareCall("{ ? = call fn_harga_akhir(?, ?) }").use { stmt ->
            stmt.registerOutParameter(1, Types.NUMERIC)  // return value
            stmt.setBigDecimal(2, price)
            stmt.setInt(3, discount)
            stmt.execute()
            stmt.getBigDecimal(1)
        }
    }
}

import oracle.jdbc.OracleTypes

// Write a CLOB
fun saveWithClob(id: Long, fullText: String) {
    DatabaseOracle.use { connection ->
        connection.prepareStatement(
            "UPDATE produk SET deskripsi = ? WHERE id = ?"
        ).use { stmt ->
            // For large text, use setCharacterStream
            stmt.setCharacterStream(
                1,
                java.io.StringReader(fullText),
                fullText.length.toLong()
            )
            stmt.setLong(2, id)
            stmt.executeUpdate()
        }
    }
}

// Read a CLOB
fun readDescription(id: Long): String? {
    return DatabaseOracle.use { connection ->
        connection.prepareStatement(
            "SELECT deskripsi FROM produk WHERE id = ?"
        ).use { stmt ->
            stmt.setLong(1, id)
            stmt.executeQuery().use { rs ->
                if (!rs.next()) return@use null
                val clob: Clob? = rs.getClob("deskripsi")
                clob?.let {
                    val content = it.getSubString(1, it.length().toInt())
                    it.free()
                    content
                }
            }
        }
    }
}

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

// Exposed supports the Oracle dialect automatically
fun initializeExposedOracle() {
    Database.connect(DatabaseOracle.dataSource)
}

// Table definition
object OracleProductTable : Table("PRODUK") {  // Oracle defaults to UPPERCASE
    val id        = long("ID").autoIncrement()
    val name      = varchar("NAMA", 255)
    val price     = decimal("HARGA", 15, 2)
    val stock     = integer("STOK").default(0)
    val category  = varchar("KATEGORI", 100).nullable()
    val active    = integer("AKTIF").default(1)  // NUMBER(1) → Int

    override val primaryKey = PrimaryKey(id)
}

// CRUD with Exposed
fun addProduct(name: String, price: BigDecimal) = transaction {
    OracleProductTable.insertAndGetId {
        it[OracleProductTable.name]  = name
        it[OracleProductTable.price] = price
    }.value
}

fun findActiveProducts() = transaction {
    OracleProductTable
        .select { OracleProductTable.active eq 1 }
        .orderBy(OracleProductTable.name)
        .map { row ->
            Product(
                id       = row[OracleProductTable.id],
                name     = row[OracleProductTable.name],
                price    = row[OracleProductTable.price],
                stock    = row[OracleProductTable.stock],
                category = row[OracleProductTable.category],
                active   = row[OracleProductTable.active] == 1
            )
        }
}

import org.flywaydb.core.Flyway

fun runOracleMigrations() {
    val flyway = Flyway.configure()
        .dataSource(DatabaseOracle.dataSource)
        .locations("classpath:db/migration/oracle")
        .defaultSchema("MYAPP")              // Oracle schema (uppercase)
        .baselineOnMigrate(true)
        .validateOnMigrate(true)
        .load()

    val result = flyway.migrate()
    println("Oracle Migration: ${result.migrationsExecuted} migrations executed")
}

-- V1__create_produk.sql
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = 'PRODUK';
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE '
            CREATE TABLE PRODUK (
                ID              NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                NAMA            VARCHAR2(255) NOT NULL,
                DESKRIPSI       CLOB,
                HARGA           NUMBER(15,2) NOT NULL,
                STOK            NUMBER DEFAULT 0 NOT NULL,
                KATEGORI        VARCHAR2(100),
                AKTIF           NUMBER(1) DEFAULT 1 NOT NULL,
                DIBUAT_PADA     TIMESTAMP DEFAULT SYSTIMESTAMP
            )';
        EXECUTE IMMEDIATE 'CREATE INDEX IDX_PRODUK_KATEGORI ON PRODUK(KATEGORI)';
    END IF;
END;
/

// 1. Always use bind variables (PreparedStatement) — Oracle optimizes these
// With bind variables, Oracle can reuse execution plans

// 2. Beware empty string = NULL
// This stores NULL, not ""
stmt.setString(1, "")  // → NULL in Oracle

// Solution: explicitly convert empty strings to null
fun String?.toOracleString() = if (this.isNullOrEmpty()) null else this

// 3. Oracle dates and times
// Oracle has TIMESTAMP WITH TIME ZONE for timezone-aware storage
val sqlDate = "SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL"

// 4. Oracle batch inserts are more optimal with array binding (Oracle-specific)
// Or use regular PreparedStatement batching, which is also efficient

// 5. For frequently run queries, consider Statement Cache
val config = HikariConfig().apply {
    addDataSourceProperty("oracle.jdbc.implicitStatementCacheSize", "20")
}