Manipulasi Variabel PL/SQL pada Oracle Heterogeneous System

June 26, 2008

Oracle Heterogeneous System. Saya singkat Oracle HS biar enak nulisnya. Apa itu? Oracle HS adalah sistem yang memungkinkan untuk melakukan manipulasi database lain di luar sistem databse yang sedang dihubungi. Sistem lain itu bisa merupakan database Oracle, atau database non-Oracle, misalnya SQL Server atau bahkan MySQL.

Dengan pengaturan sistem Oracle HS ini, kita bisa melakukan query SQL pada database lain tersebut pada session Oracle kita. Misalnya kita ingin fetch data pada suatu tabel di database lain, maka query-nya akan menjadi semacam ini:

SELECT * FROM rfq@hsmsql

Ada penambahan @hsmsql yang menunjukkan database link Oracle HS yang telah di set-up sebelumnya (setup Oracle HS tidak dibahas pada posting ini).

Nah, masalah terjadi ketika Oracle HS digunakan di dalam script PL/SQL. Oracle HS tidak bisa membaca maupun mengisi variabel-variabel PL/SQL. Perhatikan query-query PL/SQL berikut ini:

SELECT Count( * ) INTO v_count FROM rfq@hsmsql

SELECT * FROM rfq@hsmsql WHERE rfqnum = v_rfqnum

Di query biasa tanpa Oracle HS, query di atas bisa berjalan dengan baik. v_count akan berisi jumlah row yang didapat dari tabel rfq, sedangkan query kedua akan terfilter khusus untuk baris yang memiliki rfqnum sama dengan v_rfqnum. Tapi di Oracle HS tidak. Oracle HS tidak dapat memasukkan jumlah Count( * ) ke dalam variabel v_count dan tidak dapat memfilter record rfqnum dengan isi variabel  v_rfqnum.

Solusinya, kita bisa menggunakan mekanisme yang dinamakan Passthrough-SQL. Ada package built-in dari Oracle yang bernama DBMS_HS_PASSTHROUGH yang khusus digunakan untuk mendapatkan data dan/atau sebaliknya dari/ke Oracle HS. Berikut ini adalah cara untuk mendapatkan hasil dari Oracle HS dan disimpan pada variabel PL/SQL

con := DBMS_HS_PASSTHROUGH.open_cursor@hsmsql.world;

DBMS_HS_PASSTHROUGH.parse@hsmsql.world (con,

‘SELECT COUNT( * ) FROM rfq_mxm
WHERE rfqnum = ”’
|| vrfq.rfqnum
|| ””
);

rs := DBMS_HS_PASSTHROUGH.fetch_row@hsmsql.world (con);

DBMS_HS_PASSTHROUGH.get_value@hsmsql.world (con, 1, vcount);

DBMS_OUTPUT.put_line (’count: ‘ || vcount);

Jika kode di atas dijelaskan secara konseptual, maka langkah-langkahnya adalah sebagai berikut:

1. Buka kursor HS (pengertian kursor di sini berbeda dengan pengertian PL/SQL cursor)
2. Query dengan fungsi parse
3. Tarik hasil setiap baris dan masukkan ke dalam variabel PL/SQL dengan fungsi get_value

Memang jauh lebih rumit, tetapi inilah cara yang paling aman untuk memanipulasi variabel-variabel PL/SQL dengan sistem heterogeneous (Oracle HS).

Artikel terkait:

• Using Heterogeneous Service Agents
• Understanding Oracle Heterogeneous Services
• Application Development with Heterogeneous Services
• DBMS_HS_PASSTHROUGH for Passthrough-SQL
• Oracle Transparent Gateway Case Studies
• Using PL/SQL Collections and Records

Posted in Oracle