Oracle #

dart pub add http
dart pub add dart_jsonwebtoken  # untuk autentikasi JWT jika diperlukan

import 'dart:convert';
import 'package:http/http.dart' as http;

class OracleORDSClient {
  final String baseUrl;
  final String schema;
  String? _token;

  OracleORDSClient({
    required this.baseUrl,    // contoh: 'https://oracle-server:8443/ords'
    required this.schema,     // nama schema/workspace ORDS
  });

  // Login dan dapatkan token OAuth2
  Future<void> autentikasi(String clientId, String clientSecret) async {
    final response = await http.post(
      Uri.parse('$baseUrl/oauth/token'),
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
        'Authorization': 'Basic ${base64Encode(utf8.encode('$clientId:$clientSecret'))}',
      },
      body: 'grant_type=client_credentials',
    );

    if (response.statusCode != 200) {
      throw Exception('Autentikasi gagal: ${response.body}');
    }

    final data = jsonDecode(response.body);
    _token = data['access_token'] as String;
  }

  Map<String, String> get _headers => {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer $_token',
  };

  // Eksekusi query via ORDS REST endpoint
  Future<List<Map<String, dynamic>>> query(
    String endpoint, {
    Map<String, String>? params,
  }) async {
    var uri = Uri.parse('$baseUrl/$schema/$endpoint');
    if (params != null) {
      uri = uri.replace(queryParameters: params);
    }

    final response = await http.get(uri, headers: _headers);

    if (response.statusCode != 200) {
      throw Exception('Query gagal (${response.statusCode}): ${response.body}');
    }

    final data = jsonDecode(response.body) as Map<String, dynamic>;
    // ORDS mengembalikan data dalam field 'items'
    return (data['items'] as List? ?? []).cast<Map<String, dynamic>>();
  }

  // POST untuk insert/update via ORDS AutoREST
  Future<Map<String, dynamic>> post(
    String endpoint,
    Map<String, dynamic> body,
  ) async {
    final response = await http.post(
      Uri.parse('$baseUrl/$schema/$endpoint'),
      headers: _headers,
      body: jsonEncode(body),
    );

    if (response.statusCode != 200 && response.statusCode != 201) {
      throw Exception('POST gagal (${response.statusCode}): ${response.body}');
    }

    return jsonDecode(response.body) as Map<String, dynamic>;
  }
}

// Penggunaan
Future<void> main() async {
  final client = OracleORDSClient(
    baseUrl: 'https://oracle-server:8443/ords',
    schema: 'toko',
  );

  await client.autentikasi('myapp_client_id', 'myapp_client_secret');

  // ORDS AutoREST: GET /ords/toko/produk/
  final produk = await client.query('produk', params: {'limit': '10'});
  for (final p in produk) {
    print('${p['NAMA']}: ${p['HARGA']}');
  }
}

dart pub add dart_oracle

# macOS — via Homebrew
brew install oracle-instant-client

# Linux
# Download dari https://www.oracle.com/database/technologies/instant-client/downloads.html
# Ekstrak dan set LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/path/to/instant_client:$LD_LIBRARY_PATH

# Windows
# Download dan tambahkan folder ke PATH

import 'package:dart_oracle/dart_oracle.dart';

Future<void> main() async {
  // Format connection string Oracle:
  // host:port/service_name  (untuk Oracle 12c+)
  // atau //host:port/service_name (ezconnect format)
  final conn = await OracleConnection.connect(
    connectionString: 'localhost:1521/ORCLPDB1', // atau ORCL untuk CDB
    username: 'SCOTT',
    password: 'tiger',
  );

  print('Terhubung ke Oracle Database');

  await conn.close();
}

// Beberapa format string koneksi Oracle yang umum:

// EZConnect (paling sederhana)
'localhost:1521/ORCLPDB1'

// EZConnect dengan service name eksplisit
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))'

// TNS alias (jika tnsnames.ora dikonfigurasi)
'MYDB'

// RAC / failover
'(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=OFF)'
'(ADDRESS_LIST='
'  (ADDRESS=(PROTOCOL=TCP)(HOST=rac1)(PORT=1521))'
'  (ADDRESS=(PROTOCOL=TCP)(HOST=rac2)(PORT=1521)))'
'(CONNECT_DATA=(SERVICE_NAME=MYSERVICE)))'

-- MySQL / PostgreSQL        | Oracle (PL/SQL)
-- -------------------------|--------------------------------
-- AUTO_INCREMENT            | SEQUENCE + TRIGGER atau GENERATED AS IDENTITY (12c+)
-- LIMIT n                   | ROWNUM <= n (lama) atau FETCH FIRST n ROWS ONLY (12c+)
-- NOW() atau CURRENT_TIMESTAMP | SYSDATE atau SYSTIMESTAMP
-- CONCAT(a, b)              | a || b
-- IFNULL(x, y)             | NVL(x, y) atau COALESCE(x, y)
-- TRUE / FALSE              | 1 / 0 (tidak ada tipe BOOLEAN di SQL Oracle)
-- SHOW TABLES               | SELECT table_name FROM user_tables
-- SELECT 1                  | SELECT 1 FROM DUAL
-- information_schema        | data_dictionary views (user_tables, all_columns, dll.)
-- VARCHAR                   | VARCHAR2 (gunakan ini, bukan VARCHAR)

// Contoh sintaks Oracle yang perlu diperhatikan

// 1. Pagination Oracle 12c+ (direkomendasikan)
final paginasi12c = await conn.execute(
  'SELECT id, nama, harga FROM produk '
  'ORDER BY dibuat_pada DESC '
  'OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY',
  {'offset': 0, 'limit': 20},
);

// 2. Pagination Oracle 11g dan sebelumnya (ROWNUM)
final paginasiLama = await conn.execute(
  'SELECT * FROM ( '
  '  SELECT a.*, ROWNUM rnum FROM ( '
  '    SELECT id, nama, harga FROM produk ORDER BY dibuat_pada DESC '
  '  ) a WHERE ROWNUM <= :limit '
  ') WHERE rnum > :offset',
  {'limit': 20, 'offset': 0},
);

// 3. INSERT dengan SEQUENCE (Oracle tradisional)
// Perlu buat sequence dulu: CREATE SEQUENCE seq_produk START WITH 1 INCREMENT BY 1
await conn.execute(
  'INSERT INTO produk (id, nama, harga, dibuat_pada) '
  'VALUES (seq_produk.NEXTVAL, :nama, :harga, SYSDATE)',
  {'nama': 'Laptop', 'harga': 15000000},
);

// 4. INSERT dengan GENERATED AS IDENTITY (Oracle 12c+)
await conn.execute(
  'INSERT INTO produk (nama, harga, dibuat_pada) VALUES (:nama, :harga, SYSDATE)',
  {'nama': 'Mouse', 'harga': 250000},
);

// 5. Query ke tabel dual (untuk fungsi tanpa tabel)
final tanggal = await conn.execute('SELECT SYSDATE AS sekarang FROM DUAL');

import 'package:dart_oracle/dart_oracle.dart';
import 'dart:convert';

class ProdukRepository {
  final OracleConnection _conn;

  ProdukRepository(this._conn);

  // CREATE — dengan RETURNING untuk dapatkan ID
  Future<int> tambah({
    required String nama,
    required double harga,
    required int stok,
  }) async {
    // RETURNING ... INTO untuk mendapatkan nilai yang baru diinsert
    final result = await _conn.execute(
      'INSERT INTO produk (id, nama, harga, stok, aktif, dibuat_pada) '
      'VALUES (seq_produk.NEXTVAL, :nama, :harga, :stok, 1, SYSDATE) '
      'RETURNING id INTO :id_baru',
      {'nama': nama, 'harga': harga, 'stok': stok},
      outParams: {'id_baru': OracleType.number},
    );
    return result.outValues['id_baru'] as int;
  }

  // READ semua
  Future<List<Map<String, dynamic>>> ambilSemua({
    int halaman = 1,
    int perHalaman = 20,
  }) async {
    final offset = (halaman - 1) * perHalaman;
    final result = await _conn.query(
      'SELECT id, nama, harga, stok '
      'FROM produk '
      'WHERE aktif = 1 '
      'ORDER BY nama '
      'OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY',
      {'offset': offset, 'limit': perHalaman},
    );
    return result.rows.map((row) => row.toMap()).toList();
  }

  // READ satu
  Future<Map<String, dynamic>?> ambilById(int id) async {
    final result = await _conn.query(
      'SELECT * FROM produk WHERE id = :id AND aktif = 1',
      {'id': id},
    );
    return result.rows.isEmpty ? null : result.rows.first.toMap();
  }

  // UPDATE
  Future<bool> perbarui(int id, {String? nama, double? harga, int? stok}) async {
    final fields = <String>[];
    final params = <String, dynamic>{'id': id};

    if (nama != null) { fields.add('nama = :nama'); params['nama'] = nama; }
    if (harga != null) { fields.add('harga = :harga'); params['harga'] = harga; }
    if (stok != null) { fields.add('stok = :stok'); params['stok'] = stok; }
    if (fields.isEmpty) return false;

    // Oracle: SYSDATE sebagai timestamp
    fields.add('diubah_pada = SYSDATE');

    final result = await _conn.execute(
      'UPDATE produk SET ${fields.join(', ')} WHERE id = :id',
      params,
    );
    return result.rowsAffected > 0;
  }

  // DELETE soft
  Future<bool> hapus(int id) async {
    final result = await _conn.execute(
      'UPDATE produk SET aktif = 0, diubah_pada = SYSDATE WHERE id = :id',
      {'id': id},
    );
    return result.rowsAffected > 0;
  }
}

import 'package:dart_oracle/dart_oracle.dart';

Future<void> buatOrder(
  OracleConnection conn,
  int idPengguna,
  List<({int idProduk, int qty})> items,
) async {
  // Oracle tidak perlu BEGIN TRANSACTION eksplisit
  // Setiap sesi dimulai dalam mode auto-commit OFF secara default
  // Perlu COMMIT atau ROLLBACK secara eksplisit

  try {
    // Buat order
    final orderResult = await conn.execute(
      'INSERT INTO orders (id, id_pengguna, status, dibuat_pada) '
      'VALUES (seq_order.NEXTVAL, :uid, :status, SYSDATE) '
      'RETURNING id INTO :id_baru',
      {'uid': idPengguna, 'status': 'PENDING'},
      outParams: {'id_baru': OracleType.number},
    );
    final idOrder = orderResult.outValues['id_baru'] as int;

    double totalHarga = 0;
    for (final item in items) {
      // Cek dan kunci stok dengan SELECT FOR UPDATE
      final stokResult = await conn.query(
        'SELECT stok, harga FROM produk WHERE id = :id FOR UPDATE',
        {'id': item.idProduk},
      );

      if (stokResult.rows.isEmpty) {
        throw Exception('Produk ${item.idProduk} tidak ditemukan');
      }

      final row = stokResult.rows.first.toMap();
      final stok = row['STOK'] as int;
      final harga = (row['HARGA'] as num).toDouble();

      if (stok < item.qty) {
        throw Exception('Stok tidak mencukupi untuk produk ${item.idProduk}');
      }

      // Update stok
      await conn.execute(
        'UPDATE produk SET stok = stok - :qty WHERE id = :id',
        {'qty': item.qty, 'id': item.idProduk},
      );

      // Insert item order
      final subtotal = harga * item.qty;
      await conn.execute(
        'INSERT INTO order_item (id, id_order, id_produk, qty, harga, subtotal) '
        'VALUES (seq_order_item.NEXTVAL, :oid, :pid, :qty, :harga, :subtotal)',
        {
          'oid': idOrder, 'pid': item.idProduk,
          'qty': item.qty, 'harga': harga, 'subtotal': subtotal,
        },
      );

      totalHarga += subtotal;
    }

    // Update total
    await conn.execute(
      'UPDATE orders SET total = :total WHERE id = :id',
      {'total': totalHarga, 'id': idOrder},
    );

    // COMMIT — Oracle tidak auto-commit secara default
    await conn.commit();
    print('Order #$idOrder berhasil, total: Rp${totalHarga.toStringAsFixed(0)}');

  } catch (e) {
    // ROLLBACK semua perubahan
    await conn.rollback();
    print('Order gagal, rollback: $e');
    rethrow;
  }
}

import 'package:dart_oracle/dart_oracle.dart';

// Panggil stored procedure Oracle
Future<void> panggilStoredProcedure(OracleConnection conn) async {
  // Stored procedure dengan IN dan OUT parameter
  // Asumsi di Oracle:
  // CREATE OR REPLACE PROCEDURE sp_hitung_diskon(
  //   p_harga IN NUMBER,
  //   p_level IN VARCHAR2,
  //   p_diskon OUT NUMBER
  // ) AS BEGIN
  //   IF p_level = 'PREMIUM' THEN p_diskon := p_harga * 0.2;
  //   ELSE p_diskon := p_harga * 0.1; END IF;
  // END;

  final result = await conn.execute(
    'BEGIN sp_hitung_diskon(:p_harga, :p_level, :p_diskon); END;',
    {'p_harga': 150000, 'p_level': 'PREMIUM'},
    outParams: {'p_diskon': OracleType.number},
  );

  final diskon = result.outValues['p_diskon'] as double;
  print('Diskon: Rp${diskon.toStringAsFixed(0)}');
}

// Panggil function Oracle (mengembalikan nilai)
Future<double> panggilFunction(OracleConnection conn, int idProduk) async {
  // Oracle function bisa dipanggil dalam SELECT
  final result = await conn.query(
    'SELECT fn_harga_setelah_diskon(:id_produk) AS harga_diskon FROM DUAL',
    {'id_produk': idProduk},
  );
  return (result.rows.first.toMap()['HARGA_DISKON'] as num).toDouble();
}

// Eksekusi PL/SQL anonim
Future<void> plsqlAnonim(OracleConnection conn) async {
  // Blok PL/SQL anonim untuk logika yang kompleks
  await conn.execute('''
    DECLARE
      v_stok NUMBER;
      v_min_stok CONSTANT NUMBER := 10;
    BEGIN
      SELECT stok INTO v_stok FROM produk WHERE id = :id;

      IF v_stok < v_min_stok THEN
        INSERT INTO notifikasi (pesan, dibuat_pada)
        VALUES ('Stok produk ' || :id || ' hampir habis: ' || v_stok, SYSDATE);
        COMMIT;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL; -- produk tidak ada, abaikan
    END;
  ''', {'id': 42});
}