Oracle #

<!-- Maven — Oracle JDBC is on Maven Central since ojdbc8 -->
<!-- Choose the version based on your Database and Java -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>  <!-- Java 11+ and Oracle 12.2+ -->
    <version>23.3.0.23.09</version>
    <!-- Alternatives:
         ojdbc8  → Java 8+ and Oracle 12.2+
         ojdbc11 → Java 11+ and Oracle 12.2+
         ojdbc17 → Java 17+ and Oracle 21c+ (newest features)
    -->
</dependency>

<!-- Optional: additional Oracle libraries -->
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ucp</artifactId>  <!-- Oracle Universal Connection Pool -->
    <version>23.3.0.23.09</version>
</dependency>

// Gradle
implementation 'com.oracle.database.jdbc:ojdbc11:23.3.0.23.09'

-- Run as a DBA (SYSDBA)
-- Create the user/schema
CREATE USER store IDENTIFIED BY "StrongSecret123!"
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE, CREATE SESSION TO store;
GRANT CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO store;

-- Connect as the store user
CONNECT store/"StrongSecret123!"@localhost:1521/ORCLPDB1

-- Oracle has no AUTO_INCREMENT — use a SEQUENCE or GENERATED AS IDENTITY (12c+)
-- Option 1: GENERATED AS IDENTITY (Oracle 12c+, easiest)
CREATE TABLE products (
    id          NUMBER(19)    GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR2(255) NOT NULL,
    price       NUMBER(15,2)  NOT NULL,
    stock       NUMBER(10)    DEFAULT 0 NOT NULL,
    category    VARCHAR2(100),
    active      NUMBER(1)     DEFAULT 1 NOT NULL,  -- 1=true, 0=false (no BOOLEAN)
    created_at  TIMESTAMP     DEFAULT SYSTIMESTAMP,
    updated_at  TIMESTAMP     DEFAULT SYSTIMESTAMP
);

-- Option 2: SEQUENCE + TRIGGER (Oracle 11g and below)
CREATE SEQUENCE seq_products_id
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE TABLE legacy_products (
    id          NUMBER(19)    PRIMARY KEY,
    name        VARCHAR2(255) NOT NULL,
    price       NUMBER(15,2)  NOT NULL,
    stock       NUMBER(10)    DEFAULT 0 NOT NULL,
    category    VARCHAR2(100),
    active      NUMBER(1)     DEFAULT 1 NOT NULL,
    created_at  DATE          DEFAULT SYSDATE,
    updated_at  DATE          DEFAULT SYSDATE
);

-- Trigger for auto-increment and timestamp updates
CREATE OR REPLACE TRIGGER trg_products_bi
BEFORE INSERT ON legacy_products
FOR EACH ROW
BEGIN
    IF :NEW.id IS NULL THEN
        :NEW.id := seq_products_id.NEXTVAL;
    END IF;
    :NEW.created_at := SYSDATE;
    :NEW.updated_at := SYSDATE;
END;
/

CREATE OR REPLACE TRIGGER trg_products_bu
BEFORE UPDATE ON legacy_products
FOR EACH ROW
BEGIN
    :NEW.updated_at := SYSDATE;
END;
/

INSERT INTO products (name, price, stock, category) VALUES ('ProBook Laptop', 12000000, 5, 'Electronics');
INSERT INTO products (name, price, stock, category) VALUES ('Wireless Mouse', 150000, 20, 'Accessories');
INSERT INTO products (name, price, stock, category) VALUES ('Mechanical Keyboard', 450000, 15, 'Accessories');
COMMIT;

// Thin — SID (database instance name, old format)
String sidUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";

// Thin — Service Name (modern format, more flexible)
String serviceUrl = "jdbc:oracle:thin:@//localhost:1521/ORCLPDB1";

// Thin — TNS descriptor (for complex configurations: RAC, failover)
String tnsUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
              + "(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))";

// Thin — Oracle Cloud (Autonomous Database) with a Wallet
String cloudUrl = "jdbc:oracle:thin:@namedb_high?TNS_ADMIN=/path/to/wallet";

// OCI — requires Oracle Instant Client on the machine
String ociUrl = "jdbc:oracle:oci:@//localhost:1521/ORCLPDB1";

String username = "store";
String password = "StrongSecret123!";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password)) {
    System.out.println("Connected to Oracle: "
        + conn.getMetaData().getDatabaseProductVersion());
} catch (SQLException e) {
    System.err.println("Connection failed: " + e.getMessage());
}

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password)) {

    // Oracle 12c+: standard SQL syntax (recommended)
    String modernSql = """
        SELECT id, name, price, stock
        FROM products
        WHERE category = ? AND active = 1
        ORDER BY price DESC
        FETCH FIRST ? ROWS ONLY
        """;

    // Oracle 11g and below: ROWNUM (pagination requires a subquery)
    String legacySql = """
        SELECT id, name, price, stock FROM (
            SELECT id, name, price, stock, ROWNUM rn
            FROM (
                SELECT id, name, price, stock
                FROM products
                WHERE category = ? AND active = 1
                ORDER BY price DESC
            )
            WHERE ROWNUM <= ?    -- upper bound
        )
        WHERE rn > ?             -- lower bound (for pagination)
        """;

    // Oracle 12c+ pagination with OFFSET FETCH
    String paginationSql = """
        SELECT id, name, price, stock
        FROM products
        WHERE active = 1
        ORDER BY id
        OFFSET ? ROWS FETCH NEXT ? ROWS ONLY
        """;

    try (PreparedStatement ps = conn.prepareStatement(paginationSql)) {
        ps.setInt(1, 10); // skip 10 rows
        ps.setInt(2, 5);  // take 5 rows

        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.printf("%-5d %-25s Rp%,.2f%n",
                    rs.getLong("id"),
                    rs.getString("name"),
                    rs.getDouble("price"));
            }
        }
    }
}

// Way 1: Sequence.NEXTVAL directly in the INSERT
String sql1 = "INSERT INTO legacy_products (id, name, price, stock, category) "
            + "VALUES (seq_products_id.NEXTVAL, ?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(sql1)) {

    ps.setString(1, "1TB SSD");
    ps.setBigDecimal(2, new java.math.BigDecimal("750000"));
    ps.setInt(3, 30);
    ps.setString(4, "Storage");
    ps.executeUpdate();
}

// Way 2: RETURNING INTO — get the newly created ID (Oracle-specific)
String sql2 = "INSERT INTO products (name, price, stock, category) "
            + "VALUES (?, ?, ?, ?) "
            + "RETURNING id INTO ?";  // RETURNING INTO: Oracle-specific

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password)) {

    // Requires casting to OraclePreparedStatement for RETURNING INTO
    oracle.jdbc.OraclePreparedStatement ps =
        (oracle.jdbc.OraclePreparedStatement) conn.prepareStatement(sql2);

    ps.setString(1, "32GB RAM");
    ps.setBigDecimal(2, new java.math.BigDecimal("1200000"));
    ps.setInt(3, 12);
    ps.setString(4, "Components");

    // Register the output column
    ps.registerReturnParameter(5, java.sql.Types.BIGINT);
    ps.executeUpdate();

    // Read the returned value
    try (ResultSet rs = ps.getReturnResultSet()) {
        if (rs.next()) {
            System.out.println("New ID: " + rs.getLong(1));
        }
    }
    ps.close();
}

// Way 3: Statement.RETURN_GENERATED_KEYS (standard JDBC way)
String sql3 = "INSERT INTO products (name, price, stock, category) VALUES (?, ?, ?, ?)";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(sql3,
         new String[]{"id"})) {  // name the columns you want returned

    ps.setString(1, "HD Webcam");
    ps.setBigDecimal(2, new java.math.BigDecimal("350000"));
    ps.setInt(3, 25);
    ps.setString(4, "Accessories");
    ps.executeUpdate();

    try (ResultSet keys = ps.getGeneratedKeys()) {
        if (keys.next()) {
            System.out.println("New ID: " + keys.getLong(1));
        }
    }
}

// ANTI-PATTERN: inserting an empty string, expecting "" but getting NULL
String sql = "INSERT INTO products (name, category) VALUES (?, ?)";
ps.setString(1, "Laptop");
ps.setString(2, "");  // Oracle converts this to NULL!

// Check the result:
String category = rs.getString("category");
System.out.println(category == null); // true — even though you inserted ""

// CORRECT: use NULL explicitly if there's genuinely no value
ps.setNull(2, Types.VARCHAR); // explicit — the intent is clearer

// Or use a placeholder value if it must not be null
ps.setString(2, category.isEmpty() ? "GENERAL" : category);

-- Create a package to group related procedures (Oracle best practice)
CREATE OR REPLACE PACKAGE pkg_products AS
    -- Declare a REF CURSOR type for result sets
    TYPE t_cursor IS REF CURSOR;

    PROCEDURE get_by_category(
        p_category  IN  VARCHAR2,
        p_cursor    OUT t_cursor
    );

    PROCEDURE create_product(
        p_name      IN  VARCHAR2,
        p_price     IN  NUMBER,
        p_stock     IN  NUMBER,
        p_category  IN  VARCHAR2,
        p_new_id    OUT NUMBER,
        p_message   OUT VARCHAR2
    );

    PROCEDURE update_stock(
        p_id        IN  NUMBER,
        p_amount    IN  NUMBER,   -- positive = add, negative = subtract
        p_success   OUT NUMBER    -- 1=success, 0=failure
    );
END pkg_products;
/

CREATE OR REPLACE PACKAGE BODY pkg_products AS

    PROCEDURE get_by_category(
        p_category  IN  VARCHAR2,
        p_cursor    OUT t_cursor
    ) AS
    BEGIN
        OPEN p_cursor FOR
            SELECT id, name, price, stock
            FROM products
            WHERE category = p_category
              AND active = 1
            ORDER BY price DESC;
    END get_by_category;

    PROCEDURE create_product(
        p_name      IN  VARCHAR2,
        p_price     IN  NUMBER,
        p_stock     IN  NUMBER,
        p_category  IN  VARCHAR2,
        p_new_id    OUT NUMBER,
        p_message   OUT VARCHAR2
    ) AS
        v_count NUMBER;
    BEGIN
        -- Check for duplicate names
        SELECT COUNT(*) INTO v_count
        FROM products
        WHERE UPPER(name) = UPPER(p_name) AND active = 1;

        IF v_count > 0 THEN
            p_new_id := -1;
            p_message   := 'A product named "' || p_name || '" already exists';
            RETURN;
        END IF;

        INSERT INTO products (name, price, stock, category)
        VALUES (p_name, p_price, p_stock, p_category)
        RETURNING id INTO p_new_id;

        p_message := 'Product created successfully with ID ' || TO_CHAR(p_new_id);
        COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            p_new_id := -1;
            p_message   := 'Error: ' || SQLERRM;
    END create_product;

    PROCEDURE update_stock(
        p_id        IN  NUMBER,
        p_amount    IN  NUMBER,
        p_success   OUT NUMBER
    ) AS
        v_current_stock NUMBER;
    BEGIN
        SELECT stock INTO v_current_stock
        FROM products
        WHERE id = p_id AND active = 1
        FOR UPDATE;  -- lock the row for update

        IF v_current_stock + p_amount < 0 THEN
            p_success := 0;  -- insufficient stock
            RETURN;
        END IF;

        UPDATE products
        SET stock = stock + p_amount
        WHERE id = p_id;

        p_success := 1;
        COMMIT;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_success := 0;
        WHEN OTHERS THEN
            ROLLBACK;
            p_success := 0;
    END update_stock;

END pkg_products;
/

import java.sql.*;

// Call pkg_products.get_by_category, which returns a REF CURSOR
String call = "{call pkg_products.get_by_category(?, ?)}";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    cs.setString(1, "Electronics");

    // Register the OUT parameter of type REF CURSOR
    cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

    cs.execute();

    // Read the REF CURSOR as a ResultSet
    try (ResultSet rs = (ResultSet) cs.getObject(2)) {
        System.out.println("Electronics products:");
        while (rs.next()) {
            System.out.printf("  [%d] %-25s Rp%,.2f (stock: %d)%n",
                rs.getLong("id"),
                rs.getString("name"),
                rs.getDouble("price"),
                rs.getInt("stock"));
        }
    }
}

// Call pkg_products.create_product
String call = "{call pkg_products.create_product(?, ?, ?, ?, ?, ?)}";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    // IN parameters
    cs.setString(1, "4K Monitor");
    cs.setBigDecimal(2, new java.math.BigDecimal("4500000"));
    cs.setInt(3, 3);
    cs.setString(4, "Electronics");

    // OUT parameters
    cs.registerOutParameter(5, Types.NUMERIC);   // p_new_id
    cs.registerOutParameter(6, Types.VARCHAR);    // p_message

    cs.execute();

    long newId = cs.getLong(5);
    String message = cs.getString(6);

    System.out.println("Message: " + message);
    if (newId > 0) {
        System.out.println("New product ID: " + newId);
    } else {
        System.err.println("Failed to create product");
    }
}

-- Oracle functions return a value directly (unlike procedures)
CREATE OR REPLACE FUNCTION fn_calculate_stock_value(p_category IN VARCHAR2)
    RETURN NUMBER AS
    v_total NUMBER;
BEGIN
    SELECT SUM(price * stock) INTO v_total
    FROM products
    WHERE category = p_category AND active = 1;

    RETURN NVL(v_total, 0);  -- NVL = Oracle's COALESCE
END fn_calculate_stock_value;
/

// Oracle functions are called with ? = {call function(...)}
String call = "{? = call fn_calculate_stock_value(?)}";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     CallableStatement cs = conn.prepareCall(call)) {

    // Register the return value first
    cs.registerOutParameter(1, Types.NUMERIC);
    cs.setString(2, "Electronics");

    cs.execute();

    double stockValue = cs.getDouble(1);
    System.out.printf("Electronics stock value: Rp%,.2f%n", stockValue);
}

// Add a CLOB column to the table
// ALTER TABLE products ADD description CLOB;

// Write a CLOB
String insertSql = "UPDATE products SET description = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(insertSql)) {

    String longText = "A very long description... ".repeat(1000); // > 4000 characters

    // Way 1: setString — for short text (< 4000 characters)
    // ps.setString(1, longText);

    // Way 2: setClob / setCharacterStream — for very long text
    java.io.Reader reader = new java.io.StringReader(longText);
    ps.setCharacterStream(1, reader, longText.length());
    ps.setLong(2, 1L);
    ps.executeUpdate();
}

// Read a CLOB
String selectSql = "SELECT description FROM products WHERE id = ?";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(selectSql)) {

    ps.setLong(1, 1L);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            java.sql.Clob clob = rs.getClob("description");
            if (clob != null) {
                // Read as a String
                String contents = clob.getSubString(1, (int) clob.length());
                System.out.println("Description length: " + contents.length());

                // Or read as a stream (for very large text)
                try (java.io.Reader reader = clob.getCharacterStream()) {
                    char[] buffer = new char[4096];
                    int read;
                    StringBuilder sb = new StringBuilder();
                    while ((read = reader.read(buffer)) != -1) {
                        sb.append(buffer, 0, read);
                    }
                }
                clob.free(); // release the CLOB resource
            }
        }
    }
}

// ALTER TABLE products ADD image BLOB;

// Write a BLOB (image/binary file)
String insertSql = "UPDATE products SET image = ? WHERE id = ?";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(insertSql)) {

    byte[] binaryData = java.nio.file.Files.readAllBytes(
        java.nio.file.Path.of("images/laptop.jpg"));

    // setBytes for small files
    ps.setBytes(1, binaryData);
    // setBinaryStream for large files
    // ps.setBinaryStream(1, new java.io.ByteArrayInputStream(binaryData), binaryData.length);

    ps.setLong(2, 1L);
    ps.executeUpdate();
}

// Read a BLOB
String selectSql = "SELECT image FROM products WHERE id = ?";

try (Connection conn = DriverManager.getConnection(serviceUrl, username, password);
     PreparedStatement ps = conn.prepareStatement(selectSql)) {

    ps.setLong(1, 1L);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) {
            java.sql.Blob blob = rs.getBlob("image");
            if (blob != null) {
                byte[] data = blob.getBytes(1, (int) blob.length());
                java.nio.file.Files.write(java.nio.file.Path.of("output/laptop.jpg"), data);
                blob.free();
            }
        }
    }
}

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

public class OraclePool {

    private static final HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();

        config.setJdbcUrl("jdbc:oracle:thin:@//localhost:1521/ORCLPDB1");
        config.setUsername("store");
        config.setPassword("StrongSecret123!");
        config.setDriverClassName("oracle.jdbc.OracleDriver");

        config.setMaximumPoolSize(10);
        config.setMinimumIdle(2);
        config.setConnectionTimeout(30_000);
        config.setIdleTimeout(600_000);
        config.setMaxLifetime(1_800_000);

        // Oracle: very lightweight connection validation query
        config.setConnectionTestQuery("SELECT 1 FROM DUAL");

        // Oracle-specific properties
        config.addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "10000");
        config.addDataSourceProperty("oracle.jdbc.ReadTimeout", "60000");
        config.addDataSourceProperty("defaultRowPrefetch", "50"); // fetch 50 rows per fetch

        config.setPoolName("Oracle-Pool");

        dataSource = new HikariDataSource(config);
    }

    public static java.sql.Connection getConnection() throws java.sql.SQLException {
        return dataSource.getConnection();
    }
}

<!-- Maven -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <scope>runtime</scope>
</dependency>

spring:
  datasource:
    url: jdbc:oracle:thin:@//localhost:1521/ORCLPDB1
    username: store
    password: StrongSecret123!
    driver-class-name: oracle.jdbc.OracleDriver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      connection-test-query: SELECT 1 FROM DUAL
      pool-name: Oracle-Pool
      data-source-properties:
        oracle.net.CONNECT_TIMEOUT: 10000
        defaultRowPrefetch: 50

  jpa:
    hibernate:
      ddl-auto: validate
    show-sql: false
    open-in-view: false
    database-platform: org.hibernate.dialect.OracleDialect
    properties:
      hibernate:
        dialect: org.hibernate.dialect.OracleDialect
        format_sql: true
        jdbc:
          batch_size: 50
          fetch_size: 50
        default_schema: STORE   # default schema (Oracle user name, uppercase)

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "PRODUCTS",  // Oracle: table names are uppercase
       schema = "STORE")  // Oracle: schema name = user name
public class Product {

    @Id
    // Oracle 12c+: GENERATED AS IDENTITY — use IDENTITY
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // Oracle < 12c: use a SEQUENCE
    // @Id
    // @GeneratedValue(strategy = GenerationType.SEQUENCE,
    //                 generator = "seq_products")
    // @SequenceGenerator(name = "seq_products",
    //                    sequenceName = "SEQ_PRODUCTS_ID",
    //                    allocationSize = 1)
    // private Long id;

    @Column(nullable = false, length = 255)
    private String name;

    @Column(nullable = false, precision = 15, scale = 2)
    private BigDecimal price;

    @Column(nullable = false)
    private Integer stock = 0;

    @Column(length = 100)
    private String category;

    // Oracle has no BOOLEAN — use NUMBER(1)
    @Column(nullable = false, columnDefinition = "NUMBER(1) DEFAULT 1")
    private Integer active = 1;  // 1=active, 0=inactive

    // Helper getter for boolean conversion
    public boolean isActive() { return active != null && active == 1; }
    public void setActiveBoolean(boolean active) { this.active = active ? 1 : 0; }

    @Lob  // Oracle CLOB
    @Column(name = "DESCRIPTION", columnDefinition = "CLOB")
    private String description;

    @Column(name = "CREATED_AT", updatable = false)
    private LocalDateTime createdAt;

    @Column(name = "UPDATED_AT")
    private LocalDateTime updatedAt;

    @PrePersist
    protected void onCreate() {
        createdAt = LocalDateTime.now();
        updatedAt = LocalDateTime.now();
    }

    @PreUpdate
    protected void onUpdate() {
        updatedAt = LocalDateTime.now();
    }

    // Constructors, getters, setters
    public Product() {}
    public Product(String name, BigDecimal price, int stock, String category) {
        this.name = name; this.price = price; this.stock = stock; this.category = category;
    }
    // ... getters and setters
}

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    // Method names — same as other databases
    List<Product> findByActive(Integer active); // active=1 for active ones
    List<Product> findByCategoryAndActive(String category, Integer active);

    // JPQL — portable across all databases
    @Query("SELECT p FROM Product p WHERE p.active = 1 ORDER BY p.price DESC")
    List<Product> findAllActiveOrderByPrice();

    // Native Oracle query — use Oracle syntax
    @Query(value = """
        SELECT id, name, price, stock
        FROM store.products
        WHERE active = 1
          AND UPPER(name) LIKE UPPER('%' || :keyword || '%')
        ORDER BY price DESC
        FETCH FIRST :limit ROWS ONLY
        """, nativeQuery = true)
    List<Object[]> searchNative(@Param("keyword") String keyword, @Param("limit") int limit);

    // Call an Oracle stored procedure via @Procedure
    @Procedure(name = "pkg_products.update_stock")
    Integer updateStock(@Param("p_id") Long id, @Param("p_amount") Integer amount);

    // Native query with ROWNUM (Oracle 11g and earlier)
    @Query(value = """
        SELECT * FROM (
            SELECT p.*, ROWNUM rn
            FROM (SELECT id, name, price FROM products WHERE active = 1 ORDER BY id) p
            WHERE ROWNUM <= :max
        ) WHERE rn > :min
        """, nativeQuery = true)
    List<Object[]> findWithRownum(@Param("min") int min, @Param("max") int max);

    // Statistics
    @Query(value = """
        SELECT category, COUNT(*) count, SUM(stock) total_stock, AVG(price) avg_price
        FROM products
        WHERE active = 1
        GROUP BY category
        ORDER BY count DESC
        """, nativeQuery = true)
    List<Object[]> statisticsPerCategory();
}

// ANTI-PATTERN: assuming "" and NULL differ like in MySQL/SQL Server
String sql = "SELECT * FROM products WHERE category = ?";
ps.setString(1, "");
// In Oracle: setString(1, "") is the same as setNull(1, Types.VARCHAR)
// The query looks for products with category IS NULL, not category = ''

// CORRECT: use IS NULL explicitly
String nullSql = "SELECT * FROM products WHERE category IS NULL";
// Or: use NVL to handle nulls
String nvlSql = "SELECT * FROM products WHERE NVL(category, 'GENERAL') = ?";

// ANTI-PATTERN: literals in queries cause a hard parse every time (expensive in Oracle)
String sql = "SELECT * FROM products WHERE id = " + id;
// Oracle creates a new execution plan for every different id value

// CORRECT: bind variables enable soft parse (execution plan reuse)
String sql = "SELECT * FROM products WHERE id = ?";
ps.setLong(1, id);
// Oracle uses the same execution plan for all id values

// Oracle has a limit on open cursors per session
// Default: 300 cursors per session (configurable)

// ANTI-PATTERN: not closing the ResultSet or Statement
ResultSet rs = ps.executeQuery(); // opens a cursor
// ... without rs.close() → cursor leak → eventually "ORA-01000: maximum open cursors exceeded"

// CORRECT: always use try-with-resources
try (PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {
    // the cursor is closed automatically
}