Oracle #

# 1. Unduh dari https://www.oracle.com/database/technologies/instant-client/downloads.html
# Pilih: Basic Package + SDK Package
# Contoh untuk Linux x86-64: instantclient-basic-linux.x64-21.11.0.0.0.zip
#                              instantclient-sdk-linux.x64-21.11.0.0.0.zip

# 2. Ekstrak ke direktori permanen
sudo mkdir -p /opt/oracle
sudo unzip instantclient-basic-linux.x64-21.11.0.0.0.zip -d /opt/oracle
sudo unzip instantclient-sdk-linux.x64-21.11.0.0.0.zip   -d /opt/oracle

# 3. Buat symlink versi (mungkin diperlukan ruby-oci8)
cd /opt/oracle/instantclient_21_11
sudo ln -s libclntsh.so.21.1 libclntsh.so

# 4. Konfigurasi library path
echo "/opt/oracle/instantclient_21_11" | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig

# 5. Set environment variable — tambahkan ke ~/.bashrc atau ~/.zshrc
export ORACLE_HOME=/opt/oracle/instantclient_21_11
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH

# macOS dengan Homebrew
brew tap InstantClientTap/instantclient
brew install instantclient-basic instantclient-sdk

# 6. Install ruby-oci8
gem install ruby-oci8

# Jika gagal, tentukan path Instant Client:
gem install ruby-oci8 -- --with-instant-client-dir=/opt/oracle/instantclient_21_11

# Gemfile
gem 'ruby-oci8',        '~> 2.2'   # driver Oracle
gem 'activerecord-oracle_enhanced-adapter', '~> 7.1'  # untuk Rails
gem 'sequel',           '~> 5.75'  # query builder opsional

require 'oci8'

# Format 1: Easy Connect (paling mudah, tidak perlu tnsnames.ora)
# Format: host[:port][/service_name]
klien = OCI8.new(
  "appuser",           # username
  ENV["ORACLE_PASSWORD"],  # password
  "//localhost:1521/ORCL"  # //host:port/service_name
)

# Format 2: TNS alias (memerlukan file tnsnames.ora)
# Pastikan ORACLE_HOME dan TNS_ADMIN sudah diset
ENV["TNS_ADMIN"] = "/etc/oracle"   # direktori tnsnames.ora
klien = OCI8.new("appuser", ENV["ORACLE_PASSWORD"], "PROD_DB")

# Format 3: Connection descriptor lengkap (tanpa tnsnames.ora)
descriptor = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))" \
             "(CONNECT_DATA=(SERVICE_NAME=ORCL)))"
klien = OCI8.new("appuser", ENV["ORACLE_PASSWORD"], descriptor)

# Format 4: Oracle Autonomous Database (ATP/ADW) dengan Wallet
ENV["TNS_ADMIN"] = "/path/to/wallet"   # direktori berisi wallet
klien = OCI8.new("admin", ENV["ORACLE_PASSWORD"], "mydb_high")

# Periksa koneksi
puts klien.ping ? "Terhubung!" : "Gagal!"

# Informasi versi
cursor = klien.exec("SELECT * FROM v$version WHERE ROWNUM = 1")
puts cursor.fetch.first
cursor.close

# Tutup koneksi
klien.logoff

require 'oci8'

klien = OCI8.new("appuser", ENV["ORACLE_PASSWORD"], "//localhost/ORCL")

# Cara 1: exec — langsung eksekusi, iterasi cursor
cursor = klien.exec("SELECT * FROM PRODUK WHERE ROWNUM <= 10")
while (baris = cursor.fetch)
  puts "#{baris[0]}: #{baris[1]} - Rp #{baris[2]}"
end
cursor.close

# Cara 2: parse + execute — untuk query yang akan dieksekusi berulang
cursor = klien.parse("SELECT ID, NAMA, HARGA FROM PRODUK WHERE AKTIF = 1")
cursor.exec
while (baris = cursor.fetch_hash)   # fetch_hash → Hash dengan nama kolom
  puts baris.inspect
  # => {"ID"=>1, "NAMA"=>"Laptop", "HARGA"=>BigDecimal("15000000")}
end
cursor.close

# Cara 3: fetch semua sekaligus
cursor = klien.exec("SELECT ID, NAMA, HARGA FROM PRODUK ORDER BY NAMA")
semua_baris = cursor.fetch_all   # Array of Arrays
puts semua_baris.first.inspect   # => [1, "Keyboard", BigDecimal("450000")]
cursor.close

# Metadata kolom
cursor = klien.parse("SELECT ID, NAMA, HARGA FROM PRODUK")
cursor.exec
puts cursor.column_metadata.map { |col| "#{col.name}(#{col.data_type})" }.inspect
# => ["ID(NUMBER)", "NAMA(VARCHAR2)", "HARGA(NUMBER)"]
cursor.close

# ANTI-PATTERN: string interpolasi — SQL injection dan tidak efisien
nama = params[:nama]
cursor = klien.exec("SELECT * FROM PRODUK WHERE NAMA = '#{nama}'")

# BENAR: bind variable — aman dan lebih efisien
# Oracle me-cache execution plan sehingga query berikutnya lebih cepat
cursor = klien.parse("SELECT * FROM PRODUK WHERE NAMA = :nama AND HARGA < :harga")
cursor.bind_param(":nama",  nama_input)
cursor.bind_param(":harga", harga_maks)
cursor.exec

while (baris = cursor.fetch_hash)
  puts "#{baris['NAMA']}: #{baris['HARGA']}"
end
cursor.close

# Atau bind positional (menggunakan nomor)
cursor = klien.parse("SELECT * FROM PRODUK WHERE KATEGORI_ID = :1 AND AKTIF = :2")
cursor.bind_param(1, kategori_id)
cursor.bind_param(2, 1)
cursor.exec

class ProdukOracleRepository
  def initialize(klien)
    @klien = klien
  end

  # CREATE — Oracle tidak punya AUTO_INCREMENT, gunakan SEQUENCE
  def buat(nama:, harga:, stok:, kategori_id:)
    # Dapatkan ID berikutnya dari sequence
    id_cursor = @klien.exec("SELECT SEQ_PRODUK.NEXTVAL FROM DUAL")
    id_baru   = id_cursor.fetch.first.to_i
    id_cursor.close

    cursor = @klien.parse(<<~SQL)
      INSERT INTO PRODUK (ID, NAMA, HARGA, STOK, KATEGORI_ID, AKTIF, CREATED_AT)
      VALUES (:id, :nama, :harga, :stok, :kategori_id, 1, SYSDATE)
    SQL
    cursor.bind_param(":id",          id_baru)
    cursor.bind_param(":nama",        nama)
    cursor.bind_param(":harga",       harga)
    cursor.bind_param(":stok",        stok)
    cursor.bind_param(":kategori_id", kategori_id)
    cursor.exec
    @klien.commit
    cursor.close

    temukan(id_baru)
  end

  # READ
  def temukan(id)
    cursor = @klien.parse(
      "SELECT * FROM PRODUK WHERE ID = :id"
    )
    cursor.bind_param(":id", id)
    cursor.exec
    baris = cursor.fetch_hash
    cursor.close
    baris
  end

  def cari_semua(aktif: true, limit: 50, offset: 0)
    aktif_val = aktif ? 1 : 0
    cursor = @klien.parse(<<~SQL)
      SELECT p.*, k.NAMA AS KATEGORI_NAMA
      FROM PRODUK p
      LEFT JOIN KATEGORI k ON p.KATEGORI_ID = k.ID
      WHERE p.AKTIF = :aktif
      ORDER BY p.CREATED_AT DESC
      OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
    SQL
    cursor.bind_param(":aktif",  aktif_val)
    cursor.bind_param(":offset", offset)
    cursor.bind_param(":limit",  limit)
    cursor.exec

    hasil = []
    while (baris = cursor.fetch_hash)
      hasil << baris
    end
    cursor.close
    hasil
  end

  # UPDATE
  def perbarui(id, harga: nil, stok: nil, nama: nil)
    update_parts = []
    params       = {}

    if harga
      update_parts << "HARGA = :harga"
      params[:harga] = harga
    end
    if stok
      update_parts << "STOK = :stok"
      params[:stok] = stok
    end
    if nama
      update_parts << "NAMA = :nama"
      params[:nama] = nama
    end

    return false if update_parts.empty?

    params[:id] = id
    sql = "UPDATE PRODUK SET #{update_parts.join(', ')}, UPDATED_AT = SYSDATE WHERE ID = :id"
    cursor = @klien.parse(sql)
    params.each { |k, v| cursor.bind_param(":#{k}", v) }
    cursor.exec
    rows = cursor.row_count
    @klien.commit
    cursor.close
    rows > 0
  end

  # Soft delete
  def nonaktifkan(id)
    cursor = @klien.parse(
      "UPDATE PRODUK SET AKTIF = 0, UPDATED_AT = SYSDATE WHERE ID = :id"
    )
    cursor.bind_param(":id", id)
    cursor.exec
    rows = cursor.row_count
    @klien.commit
    cursor.close
    rows > 0
  end
end

-- Buat sequence
CREATE SEQUENCE SEQ_PRODUK
  START WITH 1
  INCREMENT BY 1
  NOCACHE           -- tidak cache nilai (lebih aman tapi lebih lambat)
  NOCYCLE;          -- tidak mulai dari awal setelah mencapai maksimum

-- Nilai berikutnya
SELECT SEQ_PRODUK.NEXTVAL FROM DUAL;

-- Nilai saat ini (setelah setidaknya satu NEXTVAL di session ini)
SELECT SEQ_PRODUK.CURRVAL FROM DUAL;

-- Gunakan di INSERT
INSERT INTO PRODUK (ID, NAMA) VALUES (SEQ_PRODUK.NEXTVAL, 'Laptop');

-- Oracle 12c+ — Identity Column (seperti AUTO_INCREMENT)
CREATE TABLE PRODUK (
  ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  NAMA VARCHAR2(200) NOT NULL
);

# Mendapatkan ID dari sequence sebelum INSERT
def nextval(klien, sequence_name)
  cursor = klien.exec("SELECT #{sequence_name}.NEXTVAL FROM DUAL")
  id = cursor.fetch.first.to_i
  cursor.close
  id
end

id = nextval(@klien, "SEQ_PRODUK")

# Atau gunakan RETURNING INTO untuk mendapat ID setelah INSERT
cursor = @klien.parse(<<~SQL)
  INSERT INTO PRODUK (ID, NAMA, HARGA)
  VALUES (SEQ_PRODUK.NEXTVAL, :nama, :harga)
  RETURNING ID INTO :new_id
SQL
cursor.bind_param(":nama",   "Monitor")
cursor.bind_param(":harga",  3_500_000)
cursor.bind_param(":new_id", OCI8::Cursor::NULL, Integer)  # OUT parameter
cursor.exec
id_baru = cursor[":new_id"]
cursor.close

# Oracle 12c+ — OFFSET FETCH (standar SQL, paling bersih)
def ambil_halaman_modern(halaman, per_halaman = 20)
  offset = (halaman - 1) * per_halaman
  cursor = @klien.parse(<<~SQL)
    SELECT ID, NAMA, HARGA, STOK
    FROM PRODUK
    WHERE AKTIF = 1
    ORDER BY NAMA ASC
    OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
  SQL
  cursor.bind_param(":offset", offset)
  cursor.bind_param(":limit",  per_halaman)
  cursor.exec
  hasil = []
  while (b = cursor.fetch_hash)
    hasil << b
  end
  cursor.close
  hasil
end

# Oracle 11g ke bawah — ROW_NUMBER() subquery (paling umum)
def ambil_halaman_subquery(halaman, per_halaman = 20)
  awal  = (halaman - 1) * per_halaman + 1
  akhir = halaman * per_halaman
  cursor = @klien.parse(<<~SQL)
    SELECT * FROM (
      SELECT p.*, ROW_NUMBER() OVER (ORDER BY NAMA ASC) AS RN
      FROM PRODUK p
      WHERE AKTIF = 1
    ) WHERE RN BETWEEN :awal AND :akhir
  SQL
  cursor.bind_param(":awal",  awal)
  cursor.bind_param(":akhir", akhir)
  cursor.exec
  hasil = []
  while (b = cursor.fetch_hash)
    hasil << b.reject { |k, _| k == "RN" }  # hapus kolom RN dari hasil
  end
  cursor.close
  hasil
end

# Oracle lama — ROWNUM (hanya untuk halaman pertama yang mudah)
# ROWNUM tidak bisa digunakan langsung untuk OFFSET
cursor = @klien.exec(
  "SELECT * FROM PRODUK WHERE AKTIF = 1 AND ROWNUM <= 20 ORDER BY NAMA"
)
# Perhatian: ORDER BY dieksekusi SETELAH ROWNUM — hasilnya mungkin tidak konsisten
# Gunakan ROW_NUMBER() atau OFFSET FETCH untuk pagination yang benar

# Memanggil procedure PL/SQL
def panggil_sp_update_stok(klien, produk_id, jumlah_delta)
  cursor = klien.parse("BEGIN SP_UPDATE_STOK(:p_id, :p_delta); END;")
  cursor.bind_param(":p_id",    produk_id,   Integer)
  cursor.bind_param(":p_delta", jumlah_delta, Integer)
  cursor.exec
  cursor.close
  klien.commit
end

# Function PL/SQL — mengembalikan nilai
def panggil_fn_harga_setelah_diskon(klien, produk_id, persen_diskon)
  cursor = klien.parse(
    "BEGIN :hasil := FN_HARGA_DISKON(:p_id, :p_diskon); END;"
  )
  cursor.bind_param(":hasil",    nil,          OCI8::BDPARAM_TYPE_OUT, Float)
  cursor.bind_param(":p_id",     produk_id,    Integer)
  cursor.bind_param(":p_diskon", persen_diskon, Float)
  cursor.exec
  harga_diskon = cursor[":hasil"]
  cursor.close
  harga_diskon
end

# Procedure dengan OUT parameter
def dapatkan_info_stok(klien, produk_id)
  cursor = klien.parse(<<~SQL)
    BEGIN
      SP_GET_STOK_INFO(
        P_PRODUK_ID  => :p_id,
        P_NAMA       => :p_nama,
        P_STOK       => :p_stok,
        P_STATUS     => :p_status
      );
    END;
  SQL
  cursor.bind_param(":p_id",     produk_id, Integer)
  cursor.bind_param(":p_nama",   nil,       OCI8::BDPARAM_TYPE_OUT, String, 200)
  cursor.bind_param(":p_stok",   nil,       OCI8::BDPARAM_TYPE_OUT, Integer)
  cursor.bind_param(":p_status", nil,       OCI8::BDPARAM_TYPE_OUT, String, 20)
  cursor.exec

  hasil = {
    nama:   cursor[":p_nama"],
    stok:   cursor[":p_stok"],
    status: cursor[":p_status"]
  }
  cursor.close
  hasil
end

# Blok PL/SQL anonim — tangguhkan logika di Ruby
cursor = klien.parse(<<~SQL)
  DECLARE
    v_total NUMBER := 0;
    v_count NUMBER := 0;
  BEGIN
    FOR r IN (SELECT HARGA FROM PRODUK WHERE KATEGORI_ID = :kat_id AND AKTIF = 1) LOOP
      v_total := v_total + r.HARGA;
      v_count := v_count + 1;
    END LOOP;

    :p_total := v_total;
    :p_count := v_count;
  END;
SQL
cursor.bind_param(":kat_id",  2,   Integer)
cursor.bind_param(":p_total", nil, OCI8::BDPARAM_TYPE_OUT, Float)
cursor.bind_param(":p_count", nil, OCI8::BDPARAM_TYPE_OUT, Integer)
cursor.exec
puts "Total: #{cursor[':p_total']}, Count: #{cursor[':p_count']}"
cursor.close

# Oracle: auto-commit TIDAK aktif secara default di ruby-oci8
klien.autocommit = false   # ini adalah default

def dengan_transaksi(klien)
  begin
    hasil = yield
    klien.commit
    hasil
  rescue => e
    klien.rollback
    raise e
  end
end

# Penggunaan
dengan_transaksi(@klien) do
  # Semua operasi dalam satu transaksi
  cursor = @klien.parse(
    "UPDATE REKENING SET SALDO = SALDO - :jumlah WHERE ID = :id"
  )
  cursor.bind_param(":jumlah", 500_000)
  cursor.bind_param(":id",     1)
  cursor.exec
  cursor.close

  cursor = @klien.parse(
    "UPDATE REKENING SET SALDO = SALDO + :jumlah WHERE ID = :id"
  )
  cursor.bind_param(":jumlah", 500_000)
  cursor.bind_param(":id",     2)
  cursor.exec
  cursor.close
end

# SAVEPOINT — transaksi bertingkat
klien.exec("SAVEPOINT sp_awal")
begin
  # operasi berisiko
  klien.exec("SAVEPOINT sp_tengah")
  # lebih banyak operasi
  klien.commit
rescue => e
  klien.exec("ROLLBACK TO SAVEPOINT sp_tengah")
  # tangani sebagian
end

gem install activerecord-oracle_enhanced-adapter

# config/database.yml
default: &default
  adapter: oracle_enhanced
  database: //localhost:1521/ORCL
  username: <%= ENV["ORACLE_USER"] %>
  password: <%= ENV["ORACLE_PASSWORD"] %>
  schema:   APPSCHEMA     # schema Oracle (opsional)
  pool:     <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: //localhost:1521/ORCL

production:
  <<: *default
  database: <%= ENV["ORACLE_CONNECTION"] %>

# config/initializers/oracle.rb
# Konfigurasi oracle_enhanced untuk konvensi Rails
ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.tap do |config|
    # Gunakan emulated booleans — kolom NUMBER(1) diperlakukan sebagai boolean
    config.emulate_booleans = true

    # Sequence naming convention — misal untuk tabel "produk" → "seq_produk"
    config.default_sequence_start_value = 1

    # Konversi nama kolom UPPERCASE ke snake_case
    config.cache_columns = true
  end
end

# Model dengan oracle_enhanced
class Produk < ApplicationRecord
  self.table_name  = "PRODUK"    # nama tabel Oracle uppercase
  self.primary_key = "ID"

  # oracle_enhanced otomatis cari sequence SEQ_PRODUK_ID atau PRODUK_SEQ
  self.sequence_name = "SEQ_PRODUK"

  belongs_to :kategori, foreign_key: "KATEGORI_ID"

  # Emulated boolean — kolom NUMBER(1) diperlakukan sebagai true/false
  attribute :aktif, :boolean

  scope :aktif,   -> { where(AKTIF: 1) }
  scope :terbaru, -> { order(CREATED_AT: :desc) }

  validates :NAMA,  presence: true, length: { maximum: 200 }
  validates :HARGA, numericality: { greater_than: 0 }
end

# Query sama seperti ActiveRecord biasa
Produk.aktif.terbaru.limit(10)
Produk.where("HARGA BETWEEN :min AND :max", min: 100_000, max: 5_000_000)
Produk.includes(:kategori).aktif

require 'sequel'

DB = Sequel.connect(
  adapter:  "oracle",
  database: "//localhost:1521/ORCL",
  user:     "appuser",
  password: ENV["ORACLE_PASSWORD"]
)

# Query dengan Sequel
DB[:PRODUK].all
DB[:PRODUK].where(AKTIF: 1).all
DB[:PRODUK].where { HARGA < 5_000_000 }.all
DB[:PRODUK].order(Sequel.desc(:CREATED_AT)).limit(10).all

# Pagination — Sequel otomatis generate OFFSET FETCH atau ROW_NUMBER
DB[:PRODUK].where(AKTIF: 1).limit(20).offset(40).all

# INSERT
DB[:PRODUK].insert(
  ID:          Sequel.function(:SEQ_PRODUK__NEXTVAL),  # sequence di Oracle via Sequel
  NAMA:        "Monitor",
  HARGA:       3_500_000,
  STOK:        15,
  KATEGORI_ID: 2,
  AKTIF:       1,
  CREATED_AT:  Sequel::CURRENT_TIMESTAMP
)

# Bind variable via Sequel (otomatis safe)
nama_input = params[:nama]
DB[:PRODUK].where(NAMA: nama_input).all   # AMAN, Sequel parameterize otomatis

# Raw PL/SQL
DB.run("BEGIN SP_UPDATE_STOK(1, -5); END;")
DB.fetch("SELECT * FROM PRODUK WHERE ROWNUM <= ?", 10).all

-- DUAL TABLE — Oracle butuh FROM DUAL untuk query tanpa tabel nyata
-- MySQL/PostgreSQL: SELECT 1+1;
-- Oracle:           SELECT 1+1 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT SEQ_PRODUK.NEXTVAL FROM DUAL;

-- LIMIT / TOP
-- MySQL:       SELECT * FROM tabel LIMIT 10
-- SQL Server:  SELECT TOP 10 * FROM tabel
-- Oracle 12c+: SELECT * FROM tabel FETCH FIRST 10 ROWS ONLY
-- Oracle lama: SELECT * FROM (SELECT * FROM tabel) WHERE ROWNUM <= 10

-- NULL-SAFE CONCAT
-- MySQL/PG: 'a' || NULL = NULL  atau  CONCAT('a', NULL) = 'a' (MySQL)
-- Oracle:   'a' || NULL = 'a'   (Oracle memperlakukan '' = NULL juga!)

-- EMPTY STRING vs NULL
-- Oracle menganggap '' (string kosong) SAMA dengan NULL
-- Ini berbeda dari semua database lain!
INSERT INTO tabel (nama) VALUES ('');   -- di Oracle ini = NULL!

-- OUTER JOIN syntax lama Oracle
-- Oracle:     WHERE a.id = b.id(+)  -- (+) di sisi yang opsional
-- Standar SQL: LEFT JOIN ... ON ...  -- Oracle 9i+ mendukung ini juga

-- SYSDATE vs NOW()
-- MySQL/PG: NOW(), CURRENT_TIMESTAMP
-- Oracle:   SYSDATE (tanggal+waktu lokal), SYSTIMESTAMP (dengan timezone)
--           CURRENT_DATE (tanggal), CURRENT_TIMESTAMP (dengan timezone)