Oracle #

# 1. Download from https://www.oracle.com/database/technologies/instant-client/downloads.html
# Choose: Basic Package + SDK Package
# Example for Linux x86-64: instantclient-basic-linux.x64-21.11.0.0.0.zip
#                              instantclient-sdk-linux.x64-21.11.0.0.0.zip

# 2. Extract to a permanent directory
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. Create a version symlink (may be needed by ruby-oci8)
cd /opt/oracle/instantclient_21_11
sudo ln -s libclntsh.so.21.1 libclntsh.so

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

# 5. Set environment variables — add to ~/.bashrc or ~/.zshrc
export ORACLE_HOME=/opt/oracle/instantclient_21_11
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH

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

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

# If it fails, specify the Instant Client path:
gem install ruby-oci8 -- --with-instant-client-dir=/opt/oracle/instantclient_21_11

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

require 'oci8'

# Format 1: Easy Connect (easiest, no tnsnames.ora needed)
# Format: host[:port][/service_name]
client = OCI8.new(
  "appuser",           # username
  ENV["ORACLE_PASSWORD"],  # password
  "//localhost:1521/ORCL"  # //host:port/service_name
)

# Format 2: TNS alias (requires the tnsnames.ora file)
# Make sure ORACLE_HOME and TNS_ADMIN are set
ENV["TNS_ADMIN"] = "/etc/oracle"   # directory of tnsnames.ora
client = OCI8.new("appuser", ENV["ORACLE_PASSWORD"], "PROD_DB")

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

# Format 4: Oracle Autonomous Database (ATP/ADW) with a Wallet
ENV["TNS_ADMIN"] = "/path/to/wallet"   # directory containing the wallet
client = OCI8.new("admin", ENV["ORACLE_PASSWORD"], "mydb_high")

# Check the connection
puts client.ping ? "Connected!" : "Failed!"

# Version information
cursor = client.exec("SELECT * FROM v$version WHERE ROWNUM = 1")
puts cursor.fetch.first
cursor.close

# Close the connection
client.logoff

# /etc/oracle/tnsnames.ora
# or $ORACLE_HOME/network/admin/tnsnames.ora

PROD_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-prod.company.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDB.company.com)
    )
  )

DEV_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = ORCL)
    )
  )

require 'oci8'

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

# Method 1: exec — execute directly, iterate the cursor
cursor = client.exec("SELECT * FROM PRODUCTS WHERE ROWNUM <= 10")
while (row = cursor.fetch)
  puts "#{row[0]}: #{row[1]} - Rp #{row[2]}"
end
cursor.close

# Method 2: parse + execute — for queries executed repeatedly
cursor = client.parse("SELECT ID, NAME, PRICE FROM PRODUCTS WHERE ACTIVE = 1")
cursor.exec
while (row = cursor.fetch_hash)   # fetch_hash → Hash with column names
  puts row.inspect
  # => {"ID"=>1, "NAME"=>"Laptop", "PRICE"=>BigDecimal("15000000")}
end
cursor.close

# Method 3: fetch everything at once
cursor = client.exec("SELECT ID, NAME, PRICE FROM PRODUCTS ORDER BY NAME")
all_rows = cursor.fetch_all   # Array of Arrays
puts all_rows.first.inspect   # => [1, "Keyboard", BigDecimal("450000")]
cursor.close

# Column metadata
cursor = client.parse("SELECT ID, NAME, PRICE FROM PRODUCTS")
cursor.exec
puts cursor.column_metadata.map { |col| "#{col.name}(#{col.data_type})" }.inspect
# => ["ID(NUMBER)", "NAME(VARCHAR2)", "PRICE(NUMBER)"]
cursor.close

# ANTI-PATTERN: string interpolation — SQL injection and inefficient
name = params[:name]
cursor = client.exec("SELECT * FROM PRODUCTS WHERE NAME = '#{name}'")

# CORRECT: bind variables — safe and more efficient
# Oracle caches the execution plan so subsequent queries are faster
cursor = client.parse("SELECT * FROM PRODUCTS WHERE NAME = :name AND PRICE < :price")
cursor.bind_param(":name",  name_input)
cursor.bind_param(":price", max_price)
cursor.exec

while (row = cursor.fetch_hash)
  puts "#{row['NAME']}: #{row['PRICE']}"
end
cursor.close

# Or positional binding (using numbers)
cursor = client.parse("SELECT * FROM PRODUCTS WHERE CATEGORY_ID = :1 AND ACTIVE = :2")
cursor.bind_param(1, category_id)
cursor.bind_param(2, 1)
cursor.exec

class ProductOracleRepository
  def initialize(client)
    @client = client
  end

  # CREATE — Oracle has no AUTO_INCREMENT, use a SEQUENCE
  def create(name:, price:, stock:, category_id:)
    # Get the next ID from the sequence
    id_cursor = @client.exec("SELECT SEQ_PRODUCTS.NEXTVAL FROM DUAL")
    new_id    = id_cursor.fetch.first.to_i
    id_cursor.close

    cursor = @client.parse(<<~SQL)
      INSERT INTO PRODUCTS (ID, NAME, PRICE, STOCK, CATEGORY_ID, ACTIVE, CREATED_AT)
      VALUES (:id, :name, :price, :stock, :category_id, 1, SYSDATE)
    SQL
    cursor.bind_param(":id",          new_id)
    cursor.bind_param(":name",        name)
    cursor.bind_param(":price",       price)
    cursor.bind_param(":stock",       stock)
    cursor.bind_param(":category_id", category_id)
    cursor.exec
    @client.commit
    cursor.close

    find(new_id)
  end

  # READ
  def find(id)
    cursor = @client.parse(
      "SELECT * FROM PRODUCTS WHERE ID = :id"
    )
    cursor.bind_param(":id", id)
    cursor.exec
    row = cursor.fetch_hash
    cursor.close
    row
  end

  def find_all(active: true, limit: 50, offset: 0)
    active_val = active ? 1 : 0
    cursor = @client.parse(<<~SQL)
      SELECT p.*, k.NAME AS CATEGORY_NAME
      FROM PRODUCTS p
      LEFT JOIN CATEGORIES k ON p.CATEGORY_ID = k.ID
      WHERE p.ACTIVE = :active
      ORDER BY p.CREATED_AT DESC
      OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
    SQL
    cursor.bind_param(":active", active_val)
    cursor.bind_param(":offset", offset)
    cursor.bind_param(":limit",  limit)
    cursor.exec

    results = []
    while (row = cursor.fetch_hash)
      results << row
    end
    cursor.close
    results
  end

  # UPDATE
  def update(id, price: nil, stock: nil, name: nil)
    update_parts = []
    params       = {}

    if price
      update_parts << "PRICE = :price"
      params[:price] = price
    end
    if stock
      update_parts << "STOCK = :stock"
      params[:stock] = stock
    end
    if name
      update_parts << "NAME = :name"
      params[:name] = name
    end

    return false if update_parts.empty?

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

  # Soft delete
  def deactivate(id)
    cursor = @client.parse(
      "UPDATE PRODUCTS SET ACTIVE = 0, UPDATED_AT = SYSDATE WHERE ID = :id"
    )
    cursor.bind_param(":id", id)
    cursor.exec
    rows = cursor.row_count
    @client.commit
    cursor.close
    rows > 0
  end
end

-- Create a sequence
CREATE SEQUENCE SEQ_PRODUCTS
  START WITH 1
  INCREMENT BY 1
  NOCACHE           -- don't cache values (safer but slower)
  NOCYCLE;          -- don't restart after reaching the maximum

-- The next value
SELECT SEQ_PRODUCTS.NEXTVAL FROM DUAL;

-- The current value (after at least one NEXTVAL in this session)
SELECT SEQ_PRODUCTS.CURRVAL FROM DUAL;

-- Use in an INSERT
INSERT INTO PRODUCTS (ID, NAME) VALUES (SEQ_PRODUCTS.NEXTVAL, 'Laptop');

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

# Getting an ID from a sequence before INSERT
def nextval(client, sequence_name)
  cursor = client.exec("SELECT #{sequence_name}.NEXTVAL FROM DUAL")
  id = cursor.fetch.first.to_i
  cursor.close
  id
end

id = nextval(@client, "SEQ_PRODUCTS")

# Or use RETURNING INTO to get the ID after INSERT
cursor = @client.parse(<<~SQL)
  INSERT INTO PRODUCTS (ID, NAME, PRICE)
  VALUES (SEQ_PRODUCTS.NEXTVAL, :name, :price)
  RETURNING ID INTO :new_id
SQL
cursor.bind_param(":name",   "Monitor")
cursor.bind_param(":price",  3_500_000)
cursor.bind_param(":new_id", OCI8::Cursor::NULL, Integer)  # OUT parameter
cursor.exec
new_id = cursor[":new_id"]
cursor.close

# Oracle 12c+ — OFFSET FETCH (standard SQL, cleanest)
def fetch_page_modern(page, per_page = 20)
  offset = (page - 1) * per_page
  cursor = @client.parse(<<~SQL)
    SELECT ID, NAME, PRICE, STOCK
    FROM PRODUCTS
    WHERE ACTIVE = 1
    ORDER BY NAME ASC
    OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
  SQL
  cursor.bind_param(":offset", offset)
  cursor.bind_param(":limit",  per_page)
  cursor.exec
  results = []
  while (r = cursor.fetch_hash)
    results << r
  end
  cursor.close
  results
end

# Oracle 11g and below — ROW_NUMBER() subquery (most common)
def fetch_page_subquery(page, per_page = 20)
  start_row  = (page - 1) * per_page + 1
  end_row    = page * per_page
  cursor = @client.parse(<<~SQL)
    SELECT * FROM (
      SELECT p.*, ROW_NUMBER() OVER (ORDER BY NAME ASC) AS RN
      FROM PRODUCTS p
      WHERE ACTIVE = 1
    ) WHERE RN BETWEEN :start AND :end
  SQL
  cursor.bind_param(":start", start_row)
  cursor.bind_param(":end",   end_row)
  cursor.exec
  results = []
  while (r = cursor.fetch_hash)
    results << r.reject { |k, _| k == "RN" }  # remove the RN column from results
  end
  cursor.close
  results
end

# Old Oracle — ROWNUM (only easy for the first page)
# ROWNUM can't be used directly for OFFSET
cursor = @client.exec(
  "SELECT * FROM PRODUCTS WHERE ACTIVE = 1 AND ROWNUM <= 20 ORDER BY NAME"
)
# Caution: ORDER BY executes AFTER ROWNUM — results may be inconsistent
# Use ROW_NUMBER() or OFFSET FETCH for correct pagination

# Calling a PL/SQL procedure
def call_sp_update_stock(client, product_id, delta_amount)
  cursor = client.parse("BEGIN SP_UPDATE_STOCK(:p_id, :p_delta); END;")
  cursor.bind_param(":p_id",    product_id,   Integer)
  cursor.bind_param(":p_delta", delta_amount, Integer)
  cursor.exec
  cursor.close
  client.commit
end

# PL/SQL function — returns a value
def call_fn_discounted_price(client, product_id, discount_pct)
  cursor = client.parse(
    "BEGIN :result := FN_DISCOUNTED_PRICE(:p_id, :p_discount); END;"
  )
  cursor.bind_param(":result",    nil,           OCI8::BDPARAM_TYPE_OUT, Float)
  cursor.bind_param(":p_id",      product_id,    Integer)
  cursor.bind_param(":p_discount", discount_pct, Float)
  cursor.exec
  discounted_price = cursor[":result"]
  cursor.close
  discounted_price
end

# Procedure with OUT parameters
def get_stock_info(client, product_id)
  cursor = client.parse(<<~SQL)
    BEGIN
      SP_GET_STOCK_INFO(
        P_PRODUCT_ID => :p_id,
        P_NAME       => :p_name,
        P_STOCK      => :p_stock,
        P_STATUS     => :p_status
      );
    END;
  SQL
  cursor.bind_param(":p_id",     product_id, Integer)
  cursor.bind_param(":p_name",   nil,        OCI8::BDPARAM_TYPE_OUT, String, 200)
  cursor.bind_param(":p_stock",  nil,        OCI8::BDPARAM_TYPE_OUT, Integer)
  cursor.bind_param(":p_status", nil,        OCI8::BDPARAM_TYPE_OUT, String, 20)
  cursor.exec

  result = {
    name:   cursor[":p_name"],
    stock:  cursor[":p_stock"],
    status: cursor[":p_status"]
  }
  cursor.close
  result
end

# Anonymous PL/SQL block — move logic to Ruby
cursor = client.parse(<<~SQL)
  DECLARE
    v_total NUMBER := 0;
    v_count NUMBER := 0;
  BEGIN
    FOR r IN (SELECT PRICE FROM PRODUCTS WHERE CATEGORY_ID = :cat_id AND ACTIVE = 1) LOOP
      v_total := v_total + r.PRICE;
      v_count := v_count + 1;
    END LOOP;

    :p_total := v_total;
    :p_count := v_count;
  END;
SQL
cursor.bind_param(":cat_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 is NOT active by default in ruby-oci8
client.autocommit = false   # this is the default

def with_transaction(client)
  begin
    result = yield
    client.commit
    result
  rescue => e
    client.rollback
    raise e
  end
end

# Usage
with_transaction(@client) do
  # All operations in one transaction
  cursor = @client.parse(
    "UPDATE ACCOUNTS SET BALANCE = BALANCE - :amount WHERE ID = :id"
  )
  cursor.bind_param(":amount", 500_000)
  cursor.bind_param(":id",     1)
  cursor.exec
  cursor.close

  cursor = @client.parse(
    "UPDATE ACCOUNTS SET BALANCE = BALANCE + :amount WHERE ID = :id"
  )
  cursor.bind_param(":amount", 500_000)
  cursor.bind_param(":id",     2)
  cursor.exec
  cursor.close
end

# SAVEPOINT — nested transactions
client.exec("SAVEPOINT sp_start")
begin
  # risky operations
  client.exec("SAVEPOINT sp_middle")
  # more operations
  client.commit
rescue => e
  client.exec("ROLLBACK TO SAVEPOINT sp_middle")
  # handle partially
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     # Oracle schema (optional)
  pool:     <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

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

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

# config/initializers/oracle.rb
# Configure oracle_enhanced for Rails conventions
ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.tap do |config|
    # Use emulated booleans — NUMBER(1) columns treated as booleans
    config.emulate_booleans = true

    # Sequence naming convention — e.g. for table "products" → "seq_products"
    config.default_sequence_start_value = 1

    # Convert UPPERCASE column names to snake_case
    config.cache_columns = true
  end
end

# Model with oracle_enhanced
class Product < ApplicationRecord
  self.table_name  = "PRODUCTS"    # Oracle table name in uppercase
  self.primary_key = "ID"

  # oracle_enhanced automatically looks for SEQ_PRODUCTS_ID or PRODUCTS_SEQ
  self.sequence_name = "SEQ_PRODUCTS"

  belongs_to :category, foreign_key: "CATEGORY_ID"

  # Emulated boolean — NUMBER(1) columns treated as true/false
  attribute :active, :boolean

  scope :active,  -> { where(ACTIVE: 1) }
  scope :newest,  -> { order(CREATED_AT: :desc) }

  validates :NAME,  presence: true, length: { maximum: 200 }
  validates :PRICE, numericality: { greater_than: 0 }
end

# Queries work like regular ActiveRecord
Product.active.newest.limit(10)
Product.where("PRICE BETWEEN :min AND :max", min: 100_000, max: 5_000_000)
Product.includes(:category).active

require 'sequel'

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

# Queries with Sequel
DB[:PRODUCTS].all
DB[:PRODUCTS].where(ACTIVE: 1).all
DB[:PRODUCTS].where { PRICE < 5_000_000 }.all
DB[:PRODUCTS].order(Sequel.desc(:CREATED_AT)).limit(10).all

# Pagination — Sequel automatically generates OFFSET FETCH or ROW_NUMBER
DB[:PRODUCTS].where(ACTIVE: 1).limit(20).offset(40).all

# INSERT
DB[:PRODUCTS].insert(
  ID:          Sequel.function(:SEQ_PRODUCTS__NEXTVAL),  # sequence in Oracle via Sequel
  NAME:        "Monitor",
  PRICE:       3_500_000,
  STOCK:       15,
  CATEGORY_ID: 2,
  ACTIVE:      1,
  CREATED_AT:  Sequel::CURRENT_TIMESTAMP
)

# Bind variables via Sequel (automatically safe)
name_input = params[:name]
DB[:PRODUCTS].where(NAME: name_input).all   # SAFE, Sequel parameterizes automatically

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

-- DUAL TABLE — Oracle needs FROM DUAL for queries without a real table
-- MySQL/PostgreSQL: SELECT 1+1;
-- Oracle:           SELECT 1+1 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT SEQ_PRODUCTS.NEXTVAL FROM DUAL;

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

-- NULL-SAFE CONCAT
-- MySQL/PG: 'a' || NULL = NULL  or  CONCAT('a', NULL) = 'a' (MySQL)
-- Oracle:   'a' || NULL = 'a'   (Oracle treats '' = NULL too!)

-- EMPTY STRING vs NULL
-- Oracle considers '' (empty string) the SAME as NULL
-- This differs from every other database!
INSERT INTO table (name) VALUES ('');   -- in Oracle this = NULL!

-- Old Oracle OUTER JOIN syntax
-- Oracle:     WHERE a.id = b.id(+)  -- (+) on the optional side
-- Standard SQL: LEFT JOIN ... ON ...  -- Oracle 9i+ supports this too

-- SYSDATE vs NOW()
-- MySQL/PG: NOW(), CURRENT_TIMESTAMP
-- Oracle:   SYSDATE (local date+time), SYSTIMESTAMP (with timezone)
--           CURRENT_DATE (date), CURRENT_TIMESTAMP (with timezone)