Oracle #

Oracle Database adalah sistem database enterprise yang banyak digunakan di perusahaan besar, perbankan, dan telekomunikasi. Go mendukung Oracle melalui driver github.com/godror/godror yang menggunakan Oracle Instant Client (ODPI-C) di baliknya. Oracle punya beberapa keunikan dibanding database lain: placeholder :1 atau :namaParam, tidak ada AUTO_INCREMENT (menggunakan SEQUENCE), RETURNING INTO untuk mendapatkan nilai setelah INSERT, dan ROWNUM/FETCH FIRST untuk pagination.

Prasyarat — Oracle Instant Client #

Driver godror membutuhkan Oracle Instant Client yang terinstall di sistem:

# Download Oracle Instant Client dari:
# https://www.oracle.com/database/technologies/instant-client/downloads.html

# Setelah install, set library path
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_9:$LD_LIBRARY_PATH
export PATH=/opt/oracle/instantclient_21_9:$PATH

Instalasi #

go get github.com/godror/godror

Koneksi ke Oracle #

import (
    "database/sql"
    "fmt"
    "log"
    _ "github.com/godror/godror"
)

func main() {
    // Format: user/password@host:port/service_name
    // Atau menggunakan Easy Connect string
    dsn := `user="appuser" password="AppPass!23" connectString="localhost:1521/FREEPDB1"`

    // Alternatif Easy Connect (lebih ringkas)
    // dsn := `user="appuser" password="AppPass!23" connectString="localhost/XEPDB1"`

    db, err := sql.Open("godror", dsn)
    if err != nil {
        log.Fatal("sql.Open:", err)
    }
    defer db.Close()

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(5 * time.Minute)

    if err := db.Ping(); err != nil {
        log.Fatal("Ping gagal:", err)
    }
    fmt.Println("Terhubung ke Oracle Database!")
}

Placeholder — `:1` dan Named Binding #

Oracle menggunakan :1, :2, … atau named parameter :namaParam:

// Positional placeholder
row := db.QueryRowContext(ctx,
    "SELECT name, price FROM products WHERE id = :1", id)

// Named placeholder (lebih readable untuk query panjang)
rows, err := db.QueryContext(ctx, `
    SELECT id, name, price FROM products
    WHERE category = :category AND price BETWEEN :minPrice AND :maxPrice
    ORDER BY name
`, sql.Named("category", "elektronik"),
   sql.Named("minPrice", 100_000),
   sql.Named("maxPrice", 5_000_000))

CREATE TABLE dengan SEQUENCE #

Oracle tidak punya AUTO_INCREMENT. Gunakan SEQUENCE + TRIGGER atau IDENTITY column (Oracle 12c+):

-- Oracle 12c+ — GENERATED ALWAYS AS IDENTITY (mirip AUTO_INCREMENT)
CREATE TABLE products (
    id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      VARCHAR2(200) NOT NULL,
    price     NUMBER(15,2) DEFAULT 0 NOT NULL,
    stock     NUMBER(10) DEFAULT 0 NOT NULL,
    category  VARCHAR2(100) NOT NULL,
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

-- Oracle < 12c — gunakan SEQUENCE + TRIGGER
CREATE SEQUENCE products_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER products_bi
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    :NEW.id := products_seq.NEXTVAL;
END;

INSERT dengan RETURNING INTO #

Oracle menggunakan RETURNING INTO untuk mendapatkan nilai setelah INSERT — mirip OUTPUT di SQL Server:

func createProduct(ctx context.Context, db *sql.DB, p *Product) (int64, error) {
    var newID int64

    // RETURNING INTO mengambil id yang baru di-insert
    _, err := db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES (:1, :2, :3, :4, SYSTIMESTAMP)
        RETURNING id INTO :5
    `, p.Name, p.Price, p.Stock, p.Category,
        sql.Out{Dest: &newID})

    if err != nil {
        return 0, fmt.Errorf("create product: %w", err)
    }
    return newID, nil
}

Query dan Scan #

type Product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    Category  string
    CreatedAt time.Time
}

func getProduct(ctx context.Context, db *sql.DB, id int) (*Product, error) {
    var p Product
    err := db.QueryRowContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE id = :1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock,
        &p.Category, &p.CreatedAt)

    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("get product: %w", err)
    }
    return &p, nil
}

func listProducts(ctx context.Context, db *sql.DB, category string) ([]*Product, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products
        WHERE category = :1
        ORDER BY name
    `, category)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock,
            &p.Category, &p.CreatedAt); err != nil {
            return nil, err
        }
        products = append(products, &p)
    }
    return products, rows.Err()
}

Pagination di Oracle #

Oracle versi lama menggunakan ROWNUM, Oracle 12c+ mendukung FETCH FIRST:

func listPaged(ctx context.Context, db *sql.DB, page, perPage int) ([]*Product, error) {
    offset := (page - 1) * perPage

    // Oracle 12c+ — mirip PostgreSQL/SQL Server
    rows, err := db.QueryContext(ctx, `
        SELECT id, name, price, stock, category
        FROM products
        ORDER BY id
        OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY
    `, offset, perPage)

    // Oracle < 12c — gunakan ROWNUM
    // rows, err := db.QueryContext(ctx, `
    //     SELECT id, name, price, stock, category FROM (
    //         SELECT p.*, ROWNUM rn FROM (
    //             SELECT id, name, price, stock, category
    //             FROM products ORDER BY id
    //         ) p WHERE ROWNUM <= :1
    //     ) WHERE rn > :2
    // `, page*perPage, offset)

    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category)
        products = append(products, &p)
    }
    return products, rows.Err()
}

Stored Procedure dan REF CURSOR #

Oracle menggunakan REF CURSOR untuk mengembalikan result set dari stored procedure:

// Stored procedure Oracle:
// CREATE OR REPLACE PROCEDURE get_products_by_cat(
//     p_category IN VARCHAR2,
//     p_cursor   OUT SYS_REFCURSOR
// ) AS BEGIN
//     OPEN p_cursor FOR
//         SELECT id, name, price FROM products WHERE category = p_category;
// END;

func getProductsViaSP(ctx context.Context, db *sql.DB, category string) ([]*Product, error) {
    // godror mendukung REF CURSOR via sql.Out + *sql.Rows
    var cursor driver.Rows

    _, err := db.ExecContext(ctx,
        "CALL get_products_by_cat(:1, :2)",
        category,
        sql.Out{Dest: &cursor},
    )
    if err != nil {
        return nil, fmt.Errorf("call SP: %w", err)
    }

    // Konversi driver.Rows ke *sql.Rows
    rows := sql.OpenDBConnLevelRows(cursor)
    defer rows.Close()

    var products []*Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price)
        products = append(products, &p)
    }
    return products, rows.Err()
}

Transaksi #

func transferStock(ctx context.Context, db *sql.DB, fromID, toID, qty int) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Kurangi stok
    res, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock - :1 WHERE id = :2 AND stock >= :3",
        qty, fromID, qty)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return errors.New("stok tidak mencukupi atau produk tidak ditemukan")
    }

    // Tambah stok
    if _, err := tx.ExecContext(ctx,
        "UPDATE products SET stock = stock + :1 WHERE id = :2",
        qty, toID); err != nil {
        return err
    }

    return tx.Commit()
}

Contoh Program Lengkap #

package main

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log"
    "time"

    _ "github.com/godror/godror"
)

var ErrNotFound = errors.New("data tidak ditemukan")

type Product struct {
    ID        int
    Name      string
    Price     float64
    Stock     int
    Category  string
    CreatedAt time.Time
}

type ProductRepo struct{ db *sql.DB }

func NewProductRepo(db *sql.DB) *ProductRepo { return &ProductRepo{db} }

func (r *ProductRepo) Create(ctx context.Context, p *Product) (int64, error) {
    var id int64
    _, err := r.db.ExecContext(ctx, `
        INSERT INTO products (name, price, stock, category, created_at)
        VALUES (:1, :2, :3, :4, SYSTIMESTAMP)
        RETURNING id INTO :5
    `, p.Name, p.Price, p.Stock, p.Category, sql.Out{Dest: &id})
    return id, err
}

func (r *ProductRepo) FindByID(ctx context.Context, id int) (*Product, error) {
    var p Product
    err := r.db.QueryRowContext(ctx, `
        SELECT id, name, price, stock, category, created_at
        FROM products WHERE id = :1
    `, id).Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category, &p.CreatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &p, err
}

func (r *ProductRepo) List(ctx context.Context) ([]*Product, error) {
    rows, err := r.db.QueryContext(ctx,
        "SELECT id, name, price, stock, category FROM products ORDER BY id")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var list []*Product
    for rows.Next() {
        var p Product
        rows.Scan(&p.ID, &p.Name, &p.Price, &p.Stock, &p.Category)
        list = append(list, &p)
    }
    return list, rows.Err()
}

func (r *ProductRepo) Update(ctx context.Context, p *Product) error {
    res, err := r.db.ExecContext(ctx, `
        UPDATE products SET name=:1, price=:2, stock=:3, category=:4
        WHERE id=:5
    `, p.Name, p.Price, p.Stock, p.Category, p.ID)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

func (r *ProductRepo) Delete(ctx context.Context, id int) error {
    res, err := r.db.ExecContext(ctx,
        "DELETE FROM products WHERE id = :1", id)
    if err != nil {
        return err
    }
    if n, _ := res.RowsAffected(); n == 0 {
        return ErrNotFound
    }
    return nil
}

func main() {
    dsn := `user="appuser" password="AppPass!23" connectString="localhost:1521/FREEPDB1"`

    db, err := sql.Open("godror", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(20)
    db.SetConnMaxLifetime(5 * time.Minute)

    if err := db.Ping(); err != nil {
        log.Fatal("Koneksi Oracle gagal:", err)
    }
    fmt.Println("✓ Terhubung ke Oracle Database")

    ctx := context.Background()
    repo := NewProductRepo(db)

    // Insert
    fmt.Println("\n=== Insert Produk ===")
    products := []Product{
        {Name: "Oracle DB Enterprise", Price: 500_000_000, Stock: 1, Category: "software"},
        {Name: "Sun Server T4", Price: 200_000_000, Stock: 3, Category: "hardware"},
        {Name: "Java EE License", Price: 50_000_000, Stock: 10, Category: "software"},
    }

    var ids []int64
    for _, p := range products {
        p := p
        id, err := repo.Create(ctx, &p)
        if err != nil {
            log.Printf("Gagal insert %s: %v", p.Name, err)
            continue
        }
        ids = append(ids, id)
        fmt.Printf("  [%d] %s — Rp%.0f\n", id, p.Name, p.Price)
    }

    // List semua
    fmt.Println("\n=== Semua Produk ===")
    list, err := repo.List(ctx)
    if err != nil {
        log.Println(err)
    }
    for _, p := range list {
        fmt.Printf("  [%d] %-30s Rp%15.0f  stok=%d\n",
            p.ID, p.Name, p.Price, p.Stock)
    }

    // FindByID
    if len(ids) > 0 {
        fmt.Println("\n=== FindByID ===")
        p, err := repo.FindByID(ctx, int(ids[0]))
        if err != nil {
            log.Println(err)
        } else {
            fmt.Printf("  %s, dibuat: %s\n", p.Name, p.CreatedAt.Format("2006-01-02 15:04:05"))
        }
    }

    // Update
    if len(ids) > 0 {
        fmt.Println("\n=== Update ===")
        err = repo.Update(ctx, &Product{
            ID: int(ids[0]), Name: "Oracle DB Enterprise 21c",
            Price: 600_000_000, Stock: 1, Category: "software",
        })
        fmt.Printf("  Update: %v\n", err)
    }

    // Delete
    if len(ids) > 0 {
        fmt.Println("\n=== Delete ===")
        err = repo.Delete(ctx, int(ids[len(ids)-1]))
        fmt.Printf("  Delete ID %d: %v\n", ids[len(ids)-1], err)
    }

    // Transaksi
    if len(ids) >= 2 {
        fmt.Println("\n=== Transfer Stok ===")
        err = transferStock(ctx, db, int(ids[1]), int(ids[0]), 1)
        fmt.Printf("  Transfer: %v\n", err)
    }
}

Ringkasan #

godror membutuhkan Oracle Instant Client — set LD_LIBRARY_PATH ke direktori Instant Client sebelum menjalankan.
Placeholder :1, :2, … atau named :namaParam — bukan ? seperti MySQL.
IDENTITY column (Oracle 12c+) atau SEQUENCE + TRIGGER untuk auto-increment ID.
RETURNING INTO dengan sql.Out{Dest: &id} untuk mendapatkan ID setelah INSERT.
FETCH FIRST n ROWS ONLY (Oracle 12c+) untuk pagination modern; gunakan ROWNUM untuk versi lama.
defer rows.Close() wajib setelah setiap QueryContext agar koneksi kembali ke pool.
sql.ErrNoRows untuk mendeteksi baris tidak ditemukan dari QueryRowContext.
Transaksi: defer tx.Rollback() + tx.Commit() di akhir — Rollback setelah Commit aman.
Named parameter lebih readable untuk query panjang dengan banyak parameter.
REF CURSOR untuk stored procedure yang mengembalikan result set dari Oracle.

← Sebelumnya: MSSQL Berikutnya: PostgreSQL →