Oracle #

dart pub add http
dart pub add dart_jsonwebtoken  # for JWT authentication if needed

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

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

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

  // Login and get an OAuth2 token
  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('Authentication failed: ${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',
  };

  // Execute a query via the 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 failed (${response.statusCode}): ${response.body}');
    }

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

  // POST for inserts/updates 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 failed (${response.statusCode}): ${response.body}');
    }

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

// Usage
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 from https://www.oracle.com/database/technologies/instant-client/downloads.html
# Extract and set LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/path/to/instant_client:$LD_LIBRARY_PATH

# Windows
# Download and add the folder to PATH

import 'package:dart_oracle/dart_oracle.dart';

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

  print('Connected to Oracle Database');

  await conn.close();
}

// Several common Oracle connection string formats:

// EZConnect (simplest)
'localhost:1521/ORCLPDB1'

// EZConnect with an explicit service name
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))'

// TNS alias (if tnsnames.ora is configured)
'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 or GENERATED AS IDENTITY (12c+)
-- LIMIT n                   | ROWNUM <= n (old) or FETCH FIRST n ROWS ONLY (12c+)
-- NOW() or CURRENT_TIMESTAMP | SYSDATE or SYSTIMESTAMP
-- CONCAT(a, b)              | a || b
-- IFNULL(x, y)             | NVL(x, y) or COALESCE(x, y)
-- TRUE / FALSE              | 1 / 0 (no BOOLEAN type in Oracle SQL)
-- SHOW TABLES               | SELECT table_name FROM user_tables
-- SELECT 1                  | SELECT 1 FROM DUAL
-- information_schema        | data dictionary views (user_tables, all_columns, etc.)
-- VARCHAR                   | VARCHAR2 (use this, not VARCHAR)

// Oracle syntax examples to watch out for

// 1. Oracle 12c+ pagination (recommended)
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. Oracle 11g and earlier pagination (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 with a SEQUENCE (traditional Oracle)
// Create the sequence first: 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 with 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 the dual table (for functions without a table)
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 — with RETURNING to get the ID
  Future<int> tambah({
    required String nama,
    required double harga,
    required int stok,
  }) async {
    // RETURNING ... INTO to get the newly inserted value
    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 all
  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 one
  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 as the timestamp
    fields.add('diubah_pada = SYSDATE');

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

  // Soft DELETE
  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 doesn't need an explicit BEGIN TRANSACTION
  // Every session starts in auto-commit OFF mode by default
  // Need explicit COMMIT or ROLLBACK

  try {
    // Create the 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) {
      // Check and lock stock with 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('Product ${item.idProduk} not found');
      }

      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('Insufficient stock for product ${item.idProduk}');
      }

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

      // Insert the order item
      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 the total
    await conn.execute(
      'UPDATE orders SET total = :total WHERE id = :id',
      {'total': totalHarga, 'id': idOrder},
    );

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

  } catch (e) {
    // ROLLBACK all changes
    await conn.rollback();
    print('Order failed, rolled back: $e');
    rethrow;
  }
}

import 'package:dart_oracle/dart_oracle.dart';

// Call an Oracle stored procedure
Future<void> panggilStoredProcedure(OracleConnection conn) async {
  // Stored procedure with IN and OUT parameters
  // Assumed in 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('Discount: Rp${diskon.toStringAsFixed(0)}');
}

// Call an Oracle function (returns a value)
Future<double> panggilFunction(OracleConnection conn, int idProduk) async {
  // Oracle functions can be called within 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();
}

// Execute anonymous PL/SQL
Future<void> plsqlAnonim(OracleConnection conn) async {
  // Anonymous PL/SQL block for complex logic
  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 ('Stock for product ' || :id || ' is nearly out: ' || v_stok, SYSDATE);
        COMMIT;
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL; -- product doesn't exist, ignore
    END;
  ''', {'id': 42});
}