Migrasi Mysql to OracleSaat migrasi database dari Mysql ke oracle, saya pernah mengalami kendala. Mungin kendala ini pernah dialami oleh semua orang yang membuat program dengan mysql kemudian migrasi ke oracle. Proses pembuatan table baik table master maupun transaksi tidak ada masalah. Begitu juga dengan pembuatan view. Masalah baru timbul ketika saya akan membuat procedure di oracle. Kebetulan procedure yang saya buat di Mysql adalah procedure yang bisa menampilkan data berdasarkan beberapa kriteria sebagai parameter. Saya sudah membuat routin procedure yang identik dengan procedure di Mysql, tapi ketika dicompile selalu gagal.

Setelah “ngintip” oracle jdbc document, barulah saya temukan solusinya. Ternyata di oracle tidak bisa membuat procedure yang bisa langsung menampilkan data seperti di Mysql. Jalan keluarnya adalah dengan memanfaatkan CURSOR di PL/SQL oracle. Kita harus mendefinisikan sebuah type buatan di PL/SQL dengan type REF CURSOR. Cara simplenya adalah buat saja type tersebut dalam sebuah package PL/SQL. Nah setelah dibuat, type tersebut bisa kita pakai di semua procedure maupun function atau langsung di query SQL.
OK, langsung saja kita praktekan. Pertama buat sebuah package dengan nama terserah anda. Disini package saya beri nama mypackage. Dalam package definisikan sebuah type buatan, misalnya ref_cursor.
1
2
3
  CREATE OR REPLACE PACKAGE mypackage  AS
    TYPE ref_cursor IS REF CURSOR;
  END;
Nah type ref_cursor tersebut bisa kita jadikan sebuah type return value dari procedure/function yang akan kita buat. Dalam java, ref_cursor bisa kita tangkap dengan Resultset java sql.
Sekarang mari kita test. Misalnya buat sebuah table dengan nama mahasiswa seperti dibawah ini.
1
2
3
4
5
6
7
8
  CREATE TABLE mahasiswa(
    nim VARCHAR2(14) PRIMARY KEY NOT NULL,
    nama VARCHAR2(60),
    jurusan VARCHAR2(40),
    alamat VARCHAR2(60),
    email VARCHAR2(30),
    telepon VARCHAR2(15)
  );
dan jangan lupa isi dengan beberapa baris data. Sekarang buat sebuah function dengan PL/SQL yang mengembalikan return value ref_cursor tadi.
1
2
3
4
5
6
7
8
9
10
11
  CREATE OR REPLACE
  FUNCTION ListMahasiswa(
  v_nim IN mahasiswa.nim%TYPE
)RETURN mypackage.ref_cursor
AS
mhs_cursor mypackage.ref_cursor;
BEGIN
  OPEN mhs_cursor FOR
    SELECT * FROM mahasiswa WHERE nim LIKE v_nim||'%';
  return mhs_cursor;
END;
Setelah itu compile function tersebut. Jika tidak terjadi error, maka function tersebut bisa langsung di eksekusi. Cara memangil function tersebut dengan query adalah sebagai berikut.
1
2
3
  var mhs_cur refcursor;
  exec :mhs_cur := listMahasiswa('43A8700602082');
  print mhs_cur;
Hasilnya adalah seperti ini
Eksekusi function
Cara menampung hasil function tersebut kedalam resultset java sql berbeda dengan cara biasa. Kalau procedure biasa bisa kita perlakukan sama seperti query pada umumnya. Tapi function yang menghasilkan nilai balik dalam bentuk cursor ini harus diperlakukan dengan cara khusus. Ok langsung saja lihat contoh dibawah ini.
Pertama buat class koneksi oracle, misalnya DbUtility
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package demooracleprocedure.utlity;
 
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;
 
/**
 * @author ahza
 */
public class DbUtility {
    static Connection connection;
 
    public static Connection getConnection() {
        if(connection == null){
            try {
                OracleDataSource ds = new OracleDataSource();
                ds.setURL("jdbc:oracle:oci8:@localhost:1521:AKADEMIK");
                connection = ds.getConnection("ahza", "ahza1234");
                System.out.println("Koneksi Oracle berhasil");
            } catch (SQLException e) {
                System.out.println("Koneksi Oracle Gagal");
            }
        }
        return connection;
    }
 
}
Selanjutnya buat class main seperti ini
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package demooracleprocedure;
 
import demooracleprocedure.utlity.DbUtility;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
 
/**
 *
 * @author ahza
 */
public class Main {
 
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        String query = "begin ? := listmahasiswa(?); end;";
        Connection conn =  DbUtility.getConnection();
        CallableStatement statement = null;
        try {
            statement = conn.prepareCall(query);
            statement.registerOutParameter(1, OracleTypes.CURSOR);
            statement.setString(2, "43A8700602082");
            statement.execute();
            ResultSet rs = (ResultSet) statement.getObject(1);
            while(rs.next()){
                System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t"+
                        rs.getString(4)+"\t"+rs.getString(5)+"\t"+rs.getString(6));
            }
        } catch (SQLException e) {
            System.out.println("Error: "+e.getMessage());
        }
    }
}
Hasilnya seperti ini

Eksekusi dengan Jdbc
Eksekusi dengan Jdbc

Selamat mencoba