Oracle #

Oracle Database is an enterprise database system widely used in large companies, banking, and telecommunications. Go supports Oracle through the github.com/godror/godror driver, which uses the Oracle Instant Client (ODPI-C) underneath. Oracle has several quirks compared to other databases: :1 or :paramName placeholders, no AUTO_INCREMENT (uses SEQUENCEs), RETURNING INTO to get values after INSERT, and ROWNUM/FETCH FIRST for pagination.

Prerequisite — Oracle Instant Client #

The godror driver requires Oracle Instant Client installed on the system:

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

# After installing, set the library path
export LD_LIBRARY_PATH=/opt/oracle/instantclient_21_9:$LD_LIBRARY_PATH
export PATH=/opt/oracle/instantclient_21_9:$PATH

Installation #

go get github.com/godror/godror

Connecting to Oracle #

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

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

    // Alternative Easy Connect (more concise)
    // 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 failed:", err)
    }
    fmt.Println("Connected to Oracle Database!")
}

The Oracle Database Connection Architecture #

The godror driver uses structured interaction with the Oracle Listener to allocate a Dedicated Server Process on the Oracle Instance:

flowchart TD
    Client["Go Application (godror driver)"] -->|"1. Connection Request (TCP/IP Port 1521)"| Listener["Oracle Listener"]
    Listener -->|"2. Spawn Dedicated Process"| Service["Dedicated Server Process (PGA)"]
    Service -->|"3. Access Instance"| Instance["Oracle Database Instance (SGA)"]
    Client -.->|"4. Direct Communication (Session Established)"| Service

Placeholders — `:1` and Named Binding #

Oracle uses :1, :2, … or named parameters :paramName:

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

// Named placeholders (more readable for long queries)
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", "electronics"),
   sql.Named("minPrice", 100_000),
   sql.Named("maxPrice", 5_000_000))

CREATE TABLE with SEQUENCE #

Oracle doesn’t have AUTO_INCREMENT. Use a SEQUENCE + TRIGGER or an IDENTITY column (Oracle 12c+):

-- Oracle 12c+ — GENERATED ALWAYS AS IDENTITY (like 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 — use a 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 with RETURNING INTO #

Oracle uses RETURNING INTO to get values after an INSERT — similar to OUTPUT in SQL Server:

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

    // RETURNING INTO captures the newly inserted id
    _, 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 and 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 in Oracle #

Older Oracle versions use ROWNUM; Oracle 12c+ supports FETCH FIRST:

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

    // Oracle 12c+ — similar to 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 — use 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 Procedures and REF CURSORs #

Oracle uses REF CURSORs to return result sets from stored procedures:

// Oracle stored procedure:
// 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 supports REF CURSORs 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)
    }

    // Convert driver.Rows to *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()
}

Transactions #

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

    // Reduce the stock
    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("insufficient stock or product not found")
    }

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

    return tx.Commit()
}

Complete Example Program #

package main

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

    _ "github.com/godror/godror"
)

var ErrNotFound = errors.New("data not found")

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("Oracle connection failed:", err)
    }
    fmt.Println("✓ Connected to Oracle Database")

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

    // Insert
    fmt.Println("\n=== Inserting Products ===")
    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("Failed to insert %s: %v", p.Name, err)
            continue
        }
        ids = append(ids, id)
        fmt.Printf("  [%d] %s — Rp%.0f\n", id, p.Name, p.Price)
    }

    // List all
    fmt.Println("\n=== All Products ===")
    list, err := repo.List(ctx)
    if err != nil {
        log.Println(err)
    }
    for _, p := range list {
        fmt.Printf("  [%d] %-30s Rp%15.0f  stock=%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, created: %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)
    }

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

Summary #

godror requires the Oracle Instant Client — set LD_LIBRARY_PATH to the Instant Client directory before running.
Placeholders :1, :2, … or named :paramName — not ? like MySQL.
IDENTITY columns (Oracle 12c+) or SEQUENCE + TRIGGER for auto-increment IDs.
RETURNING INTO with sql.Out{Dest: &id} to get the ID after an INSERT.
FETCH FIRST n ROWS ONLY (Oracle 12c+) for modern pagination; use ROWNUM for older versions.
defer rows.Close() is mandatory after every QueryContext so the connection returns to the pool.
sql.ErrNoRows detects a missing row from QueryRowContext.
Transactions: defer tx.Rollback() + tx.Commit() at the end — Rollback after Commit is safe.
Named parameters are more readable for long queries with many parameters.
REF CURSORs for stored procedures returning result sets from Oracle.

← Previous: MSSQL Next: PostgreSQL →