Oracle Stored Procedure: Membaca Dan Menulis Ke File Excel

Seseorang pernah bertanya bagimana membaca dan menulis file Excel melalui API PL/SQL (tanpa memakai Oracle Form, dsb). Tentu saja yang pertama kali terbayang dalam pikiranku adalah googling dulu. Aku menemukan beberapa alternatif solusi, seperti menulis dan membaca dalam format CSV untuk di-export dan di-import ke file XLS. Tapi tidak ada cara gampang untuk membaca format native Excel. Bagaimana jika si-penanya bersikeras untuk tetap ingin database-nya bisa membaca file Excel native melalui stored procedure? Stored procedure dalam Oracle tidak harus selalu berupa PL/SQL, kenapa tidak mencoba Java? Apache POI adalah salah satu API open source berbasis Java untuk membaca dan menulis file native Excel & Word. Aku akan mencoba mensimulasikan apa yang ingin dicapai oleh si-penanya melalui langkah-langkah sederhana berikut.

Pertama-tama, aku men-download JAR Apache POI dan memasukkannya ke dalam database Oracle agar dapat di-referensi oleh stored procedure Java nantinya. Perintah yang aku berikan kira-kira seperti berikut:

loadjava -u snake/password poi-3.2-FINAL-20081019.jar

Lalu, aku membuat program Java yang nantinya akan dijalankan oleh database Oracle, seperti berikut:

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;


public class ExcelFile {

  public static String read(String dataFile) {
    try {
      POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(new File(dataFile)));
      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);
      HSSFRow row = sheet.getRow(1);
      String str = row.getCell(0).getRichStringCellValue().getString();
      return str;
    } catch (Exception e) {
      return e.getMessage();
    }
  }

}

Karena sedang coding dengan terburu-buru, aku membuat method read() dengan argumen berupa file Excel yang ingin dibaca. Tentu saja, pada kenyataannya, akan lebih baik jika ini berupa data BLOB (jika file Excel berada pada host yang berbeda) atau BFILE (jika file Excel berada pada host yang sama). Fungsi sederhana ini akan mengembalikan nilai String dari baris kedua dan kolom pertama. Setelah men-compile file ini, aku memasukkannya ke dalam database Oracle dengan perintah seperti berikut di command prompt:

loadjava -u snake/password ExcelFile.class

Kemudian, aku membuat PL/SQL wrapper untuk class Java, seperti berikut:

CREATE  OR REPLACE FUNCTION bacaExcel (namaFile IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'ExcelFile.read(java.lang.String) return java.lang.String';

Selanjutnya, masih dalam keadaan terburu-buru, aku harus memberikan permission agar stored procedure Java di schema SNAKE boleh membaca dan menulis file di direktori yang aku tentukan. Agar tidak rumit, aku login sebagai user yang memiliki akses administrasi Java, misalnya SYS, dan memberikan wewenang agar program Java di schema SNAKE boleh membaca dan menulis seluruh file:

call dbms_java.grant_permission('SNAKE', 'java.io.FilePermission', '<<ALL FILES>>', 'read,write' );

Sekarang, aku siap menggunakan stored procedure untuk membaca file Excel tersebut, misalnya seperti berikut:

SELECT bacaExcel('C:\sample.xls') FROM DUAL;

yang akan menghasilkan nilai dari cell A2 di sheet pertama di file C:\sample.xls. Proses pembuatan stored procedure menjadi cepat dan sederhana berkat Java yang didukung oleh komunitas open-source sehingga memiliki banyak API untuk berbagai kebutuhan 🙂 Jadi tidak harus selalu PL/SQL, bukan?

Iklan

Day 5: Stored Procedure Java di Oracle Database

Learning In HomeOriginal Date: 17 Januari 2009

Hari ini aku akan melatih membuat stored procedure dengan bahasa Java, bukan PL/SQL, di Oracle. Untuk melakukan query SQL di stored procedure berbasis Java, aku bisa menggunakan JDBC. Koneksi yang aku peroleh tidak perlu aku tutup karena kode Java ini dijalankan di server database sehingga koneksi database-nya selalu ada. Ini adalah contoh melakukan query SQL di stored procedure berbasis Java:


Connection con = DriverManager.
   getConnection("jdbc:default:connection");
String sql = "SELECT " + 
    namaKolom + " FROM " + namaTabel;
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

Salah satu kelebihan Java adalah multi-platform. Akan tetapi, kelebihan ini sekaligus menjadi kekurangannya, yaitu waktu eksekusi menjadi bertambah lama dibandingkan aplikasi native. Jika multi-platform bukanlah suatu kebutuhan, untuk mempercepat eksekusi kode Java di server database Oracle, aku bisa menggunakan native compiler. Bytecode class Java akan diterjemahkan ke dalam kode bahasa C yang selanjutnya di-compile dan di-link menjadi shared libraries (seperti DLL) yang tergantung pada platform tertentu. Tool ncomp dapat dipakai untuk keperluan ini. Sebelum tool ini dipakai, pengguna harus menentukan terlebih dahulu setting compiler C dan linker yang dipergunakan, tergantung pada platform dimana Oracle Database berjalan.

Day 3: Pengembangan Aplikasi Dengan Oracle

Learning In HomeOriginal Date: 15 Januari 2009

Aku menemukan satu hal yang cukup menjebak berkaitan dengan format tanggal untuk tahun dua digit. Jika nilai 15-01-92 diformat dengan DD-MM-YY, maka tahun yang dimaksud adalah 2092, bukan 1992. Itu karena format YY akan memakai nilai abad yang saat ini, yaitu 2000, bukan abad lalu, 1900. Jika aku memakai format DD-MM-RR, aku akan mendapatkan tahun 1992. Format RR akan mengubah tahun ke 19xx jika nilai RR berkisar dari 50 sampai 99, dan mengubah tahun ke 20xx jika nilai RR berkisar dari 00 hingga 49 (PS: ini berlaku jika tahun sistem antara 2000 sampai 2049).

O ya, Oracle juga memiliki fungsi untuk pencarian berdasaran regular expression (regex). Ini adalah contoh query untuk mencari kode barang yang tidak sesuai dengan format yang telah ditentukan dengan memanfaatkan regex:


select * from latihan where
  not regexp_like(kodebarang,
    '[a-zA-Z0-9]{1,2}\-[0-9]{3}/[0-9]{2}/[0-9]{2}');

Dengan PL/SQL dan Oracle HTTP Server, programmer sudah bisa membangun aplikasi berbasis web. Salah satu contoh aplikasi web yang dibangun dengan cara seperti ini, yang sudah pernah aku lihat, adalah Oracle Workflow Homepage.

Hari ini, aku mencoba latihan membuat aplikasi web dengan PL/SQL dan Oracle HTTP Server. Pertama-tama aku menambahkan entry DAD baru terlebih dahulu, seperti:


<Location /latihan>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  PlsqlDatabaseConnectString localhost:1521:latihan
  PlsqlAuthenticationMode Basic
  PlsqlDefaultPage latihanWebApp.home
</Location>

Dari DAD diatas, terlihat bahwa PL/SQL procedure yang akan dipanggil pertama kali adalah procedure home yang terletak di package latihanWebApp. Disini, aku membiarkan authentification dilakukan melalui browser sesuai dengan user dan password di database Oracle.

Berikutnya, aku akan membuat sebuah package baru dengan nama latihanWebApp yang di dalamnya berisi sebuah procedure seperti berikut ini:


  PROCEDURE HOME AS
  BEGIN
    htp.print('<html><head><title>TEST</title>

    <body><h1>Hello World</h1></body></html>');
  END HOME;

Seperti yang bisa ditebak, saat mengetik URL http://localhost:7777/latihan di browser. aku akan diminta login. Aku mengisi dengan user pemilik schema dimana package latihanWebApp berada, setelah itu, akan muncul tulisan Hello World di browser.

Untuk ber-interaksi dengan user, aku bisa menggunakan form di HTML dan memproses nilainya sebagai parameter di procedure PL/SQL. Sebagai contoh, aku mengubah procedure home untuk menampilkan form HTML:


  htp.print('<html><head>
    <title>test</title></head>');
  htp.print('<body>');
  htp.print('<form action="latihanwebapp.proses"
     method="GET">');
  htp.print('Nama Depan:
     <input type="text" name="namaDepan" />');
  htp.print('Nama Belakang:
     <input type="text" name="namaBelakang" />');
  htp.print('<input type="submit"/>');
  htp.print('</form></body></html>');

Prosedur di atas menghasilkan form HTML yang akan mengirimkan variabel melalui method GET ke procedure proses di package latihanWebApp. Isi procedure tersebut kira-kira seperti:


  PROCEDURE PROSES (namaDepan IN VARCHAR2,
     namaBelakang IN VARCHAR2) AS
  BEGIN
    htp.print('<html><head>

       <title>proses</title></head><body>');
    htp.print('Nama Depan = ' || namaDepan);
    htp.print(' dan Nama Belakang = '
       || namaBelakang);
    htp.print('</body></html>');
  END PROSES;

Oracle Security: Enkripsi Data

Procedure dan function yang berhubungan dengan algoritma enkripsi data dapat ditemukan di package DBMS_CRYPTO. Aku mencoba latihan sederhana dengan membuat sebuah fungsi enkripsi seperti berikut:


create or replace FUNCTION ENKRIP
( nilai IN VARCHAR2, key IN VARCHAR2
) RETURN RAW AS
  
  nilai_raw RAW(2000);
  key_raw RAW(2000);
  nilai_terenkripsi RAW(2000);

BEGIN
  
  nilai_raw := utl_i18n.string_to_raw(nilai, 
    'AL32UTF8');
  key_raw := utl_i18n.string_to_raw(key, 
    'AL32UTF8');
  
  nilai_terenkripsi := dbms_crypto.encrypt(
    src => nilai_raw,
    typ => dbms_crypto.des_cbc_pkcs5,
    key => key_raw
  );
  
  return nilai_terenkripsi;
  
END ENKRIP;

Pada fungsi di atas, aku harus menerjemahkan nilai yang akan di-enkripsi beserta key-nya ke dalam tipe data RAW, karena fungsi DBMS_CRYPT.ENCRYPT hanya menerima data dalam format RAW. Contoh di atas menggunakan algoritma enkripsi DES. Berikut ini adalah fungsi yang melakukan decrypt:


create or replace FUNCTION DEKRIP
( nilai IN raw, key IN VARCHAR2
) RETURN VARCHAR2 AS
  
  nilai_raw RAW(2000);
  key_raw RAW(2000);
  nilai_dekrip RAW(2000);

BEGIN
  
  key_raw := utl_i18n.string_to_raw(key, 
    'AL32UTF8');
  
  nilai_dekrip := dbms_crypto.decrypt(
    src => nilai,
    typ => dbms_crypto.des_cbc_pkcs5,
    key => key_raw
  );
  
  return utl_i18n.raw_to_nchar(nilai_dekrip, 
    'AL32UTF8');
  
END DEKRIP;

Sebagai contoh, aku melakukan enkripsi data VARCHAR2 ‘JOCKI HENDRY’ dengan key ‘testtesttest’:


select enkrip('JOCKI HENDRY', 'testtesttest') from dual;

yang menghasilkan:


BEB9B507432B91E116EC3F07364E38C5

Hasil decrypt-nya di dapat dengan:


select dekrip('BEB9B507432B91E116EC3F07364E38C5', 'testtesttest') from dual;

PL/SQL: Beberapa Package Yang Menarik

DBMS_RANDOM dapat dipakai untuk menghasilkan nilai acak. Fungsi NORMAL akan menghasilkan angka acak dalam tipe NUMBER, sementara fungsi STRING akan menghasilkan karakter acak. Contoh penggunannya antara lain:

declare
  kalimat VARCHAR2(10);
begin
  dbms_output.put_line(dbms_random.normal);
  dbms_output.put_line(
    dbms_random.string('x',10));
end;
/

 

DBMS_UTILITY mengandung banyak fungsi-fungsi pembantu. Salah satunya adalah PORT_STRING untuk mengetahui versi OS dimana Oracle Database berjalan. Untuk mengetahui versi database, fungsi DB_VERSION dapat dipergunakan. Ada juga CURRENT_INSTANCE untuk mengetahui berapa instance yang terkoneksi ke database saat ini.

UTL_FILE menyediakan procedure dan fungsi yang berhubungan dengan manipulasi file. Sebelum memulai menulis dan membaca file, aku membuat object directory dan men-assign pada user yang bersangkutan. Berikut ini adalah contoh menulis ke file dalam PL/SQL:

DECLARE
  file_handle utl_file.FILE_TYPE;
BEGIN
  file_handle := utl_file.fopen('LATIHAN',
     'sample.log', 'w');
  utl_file.put_line(file_handle,
     '## Example of Log File');
  utl_file.put_line(file_handle,
     '## This is dummy');
  utl_file.fflush(file_handle);
  utl_file.fclose(file_handle);
END;
/

 

Maksimal buffer untuk pemanggilan fungsi PUT atau PUT_LINE adalah 32767 bytes. Jadi sebelum tulisan di buffer mencapai angka tersebut, buffer flush harus dilakukan dengan fungsi FFLUSH.

Berikut ini adalah contoh PL/SQL yang membaca file yang ditulis oleh kode program sebelumnya:

DECLARE
  file_handle utl_file.FILE_TYPE;
  baris VARCHAR2(2000);
BEGIN
  file_handle := utl_file.fopen('LATIHAN',
    'sample.log', 'r');
  BEGIN
    LOOP
      utl_file.get_line(file_handle, baris);
      dbms_output.put_line(baris);
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('** EOF **');
  END;
  utl_file.fclose(file_handle);
END;
/

PL/SQL Obfuscating: Wrap

Orang-orang yang tidak suka berbagi source code pasti akrab dengan obfuscating. Mereka akan melakukan apapun agar source code mereka sukar dibongkar dan ditemukan. Oracle 10g datang dengan sebuah utility, yaitu wrap yang dapat men-obfuscate source code SQL. Kode-kode CREATE [OR REPLACE] FUNCTION, PROCEDURE, PACKAGE, PACKAGE BODY, TYPE .. AS OBJECT, TYPE .. UNDER .., dan TYPE BODY akan diterjemahkan menjadi kode-kode asing yang hanya dikenali oleh PL/SQL compiler.

Sebagai contoh, perhatikan isi file SQL berikut:

 

create or replace
PROCEDURE Proc_Latihan AS
  monthly_salary          NUMBER(6);
  number_of_days_worked   NUMBER(2);
  pay_per_day             NUMBER(6,2);

-- the following is the executable part,
-- from BEGIN to END
BEGIN
  monthly_salary := 2290;
  number_of_days_worked := 21;
  pay_per_day := monthly_salary /
     number_of_days_worked;
  DBMS_OUTPUT.PUT_LINE('The pay per day is ' ||
    TO_CHAR(pay_per_day));

EXCEPTION
  WHEN ZERO_DIVIDE THEN
    pay_per_day := 0;

END;

 

Aku menyimpan file tersebut dengan nama latihan.sql, kemudian aku menjalankan perintah berikut melalui command prompt:


wrap iname=c:\latihan.sql
oname=c:\obfuscated_latihan.sql

Setelah menjalankan perintah di atas, aku akan memperoleh sebuah file bernama obfuscated_latihan.sql yang isinya sebagai berikut:

 

create or replace
PROCEDURE Proc_Latihan wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
190 148
sd0UBKHsKqg1HM4SSQJ1KTf77qkwgzLQLm0VfC9AAMHqR9eeUfYorNESWhOzOQUUGiaD7XSZ
1YoZoq6sJBkmBltcW0ChQBeLkLFCE1HleqyGH0NlfVmryGOmFti9k4dePscp9lMCJhg2ECG5
EBgeB4pfidgMo5OdZzIHBoJdkS5KPmCPf+ITkh9Z4zbBg3oMrp3KSkksu4GrcHcrBsn2cqWB
MIJN8VEUukTqAvtJQ4Nwd7Kq/bT5dnCYMa+WsbUA9e4wRnA+1xrQHF0jH5lT3LNEef0fCnSy
z4zEGtIJ1UbdsrMEIJD6/zarvIzH4TxiAYM=

/

 

Mmm,sungguh beda jauh dari kode aslinya, bukan? Setidaknya pada pandangan pertama 😉 Sekarang aku akan mencoba apakah kode-kode aneh di atas masih dapat di-eksekusi oleh SQL*Plus. Saat memasukkan perintah:


SQL> @c:\obfuscated_latihan.sql

Oracle merespon dengan tulisan “Procedure created.” Ini berarti memang Oracle masih mengenali kode-kode aneh di atas 🙂

Object Type: Menyimpan Object Ke Dalam Table

Object yang sudah kita buat juga dapat kita simpan ke dalam sebuah table. Sebelumnya, kita harus membuat table untuk menampung object tersebut, dengan perintah seperti berikut:


CREATE TABLE TBL_INDIVIDUAL_CIF OF
INDIVIDUAL_CIF;

Setelah itu, kita bisa memasukkan data seperti biasanya:


INSERT INTO TBL_INDIVIDUAL_CIF VALUES (
'ID-1', 'ACC-1', 'Solid', 'Snake',
CURRENT_DATE
);

Kode berikut akan mengambil nilai object tersebut melalui PL/SQL:

DECLARE
  cif INDIVIDUAL_CIF;
BEGIN
  SELECT VALUE(P) INTO cif FROM
     tbl_individual_cif p;
  cif.print_account_info();
END;
/

PL/SQL Basic: Object Oriented Programming

PL/SQL mendukung OOP melalui penggunaan object type. Pada Java, object type hampir mirip seperti class. Mungkin lebih mirip ke class module di VB6, tetapi object type di PL/SQL mendukung inheritance dan polymorphism. Ini adalah salah satu contoh deklarasi dan definisi object type di PL/SQL:


CREATE TYPE CIF AS OBJECT (
  id_no       VARCHAR2(40),
  account_no  VARCHAR2(40),

  MEMBER PROCEDURE print_account_info
);
/

CREATE TYPE BODY CIF AS

  MEMBER PROCEDURE print_account_info IS
  BEGIN
    dbms_output.put_line('CIF ID = ' || SELF.id_no);
    dbms_output.put_line('Account No = ' || SELF.account_no);
  END;

END;
/

Pada contoh object type di atas, aku hanya membuat sebuah member procedure. Pada object type yang lebih rumit, bisa terdapat member function, static procedure, dan static function. Contoh penggunaan object type di atas dapat dilihat pada kode berikut ini:


SET SERVEROUTPUT ON;

DECLARE
  nasabah CIF;
BEGIN
  nasabah := CIF('ID-123', 'ACC-123');
  nasabah.print_account_info;
END;
/

Sekarang aku akan membuat dua buah turunan dari object CIF, yaitu IndividualCIF dan CorporateCIF. Aku akan mulai dengan membuat IndividualCIF seperti pada kode berikut:


CREATE TYPE INDIVIDUAL_CIF UNDER CIF (
  first_name VARCHAR2(200),
  last_name VARCHAR2(200),
  date_of_birth DATE,

  MEMBER FUNCTION age RETURN NUMBER
);
/

CREATE TYPE BODY INDIVIDUAL_CIF AS

  MEMBER FUNCTION age RETURN NUMBER IS
  BEGIN
    RETURN floor(
       months_between(
         current_date,self.date_of_birth)/12);
  END;

END;
/

Aku menambahkan sebuah function, yaitu age yang akan mengembalikan usia dari CIF tersebut. Contoh penggunaan object type ini misalnya:


DECLARE
  snake INDIVIDUAL_CIF;
BEGIN
  snake := INDIVIDUAL_CIF('ID-123', 'ACC-123',
      'Solid', 'Snake', DATE '1985-03-13');
  snake.print_account_info;
  dbms_output.put_line('Usia = ' || snake.age);
END;
/

Pada contoh di atas, aku masih tetap dapat menggunakan atribut dan method milik superclass CIF, plus aku dapat memakai atribut dan method tambahan yang khusus ada di INDIVIDUAL_CIF. Sekarang, aku akan membuat object turunan dari CIF lainnya, yaitu CORPORATE_CIF:


CREATE TYPE CORPORATE_CIF UNDER CIF (
  company_name VARCHAR2(200), 

  OVERRIDING MEMBER PROCEDURE print_account_info
);
/

CREATE TYPE BODY CORPORATE_CIF AS

  OVERRIDING MEMBER PROCEDURE print_account_info AS
  BEGIN
    dbms_output.put_line(
      'ID No = ' || SELF.id_no || ';' ||
      'Account No = ' || SELF.account_no || ';' ||
      'Company Name = ' || SELF.company_name);
  END;

END;
/

Kali ini aku akan men-override method print_account_info, sehingga yang dikerjakan oleh instance CORPORATE_CIF adalah method yang sudah di-override sesuai keperluan, bukan lagi method milik CIF. Berikut ini adalah contoh penggunaannya:


DECLARE
  aprdev CORPORATE_CIF;
BEGIN
  aprdev := CORPORATE_CIF('ID-124', 'ACC-124', 'APRDEV');
  aprdev.print_account_info;
END;
/

Output dari kode di atas adalah:


ID No = ID-124;Account No = ACC-124;Company Name = APRDEV

PL/SQL Basic: Resume Exception?

Penanganan exception di PL/SQL rasanya memang cukup terbatas jika dibandingkan dengan bahasa seperti VB.
Salah satu contoh kasus, misalnya, jika saya ingin melanjutkan eksekusi perintah ke baris berikutnya setelah
terjadi exception. Memang tidak ada statement untuk RESUME, tetapi bisa diakalin, seperti pada contoh berikut:

SET SERVEROUTPUT ON
DECLARE

  angka1 PLS_INTEGER;
  angka2 PLS_INTEGER;
  hasil  PLS_INTEGER DEFAULT 0;

BEGIN

  angka1 := 10;
  angka2 := 0;

  BEGIN
    hasil := angka1 / angka2;
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
       hasil := 0;
  END;

  dbms_output.put_line('Hasil = ' || hasil);

EXCEPTION

  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Terjadi kesalahan.');

END;
/

Pada contoh di atas, baris terakhir yang menampilkan isi variabel hasil selalu dikerjakan walaupun terjadi pembagian
dengan nol (0).

PL/SQL Basic: Dynamic SQL

PL/SQL memiliki fitur untuk menjalankan SQL secara dinamis. Kita dapat menyimpan string SQL
ke dalam sebuah variabel, kemudian meminta Oracle untuk mengerjakannya. Misalnya pada PL/SQL
yang menghapus tabel yang diinginkan, seperti:

SET SERVEROUTPUT ON
DECLARE

  TYPE DAFTAR_TABLE IS TABLE OF VARCHAR2(200);
  tabel DAFTAR_TABLE;
  sql_statement VARCHAR2(1000);
BEGIN

  tabel := DAFTAR_TABLE('LATIHAN', 'PERSON', 'PERSON_EMAIL_ADDR',
      'PERSON_EVENT', 'TBL_LATIHAN');

  FOR i IN tabel.FIRST .. tabel.LAST LOOP

    sql_statement := 'DROP TABLE ' || tabel(i);
    dbms_output.put_line('SQL Statement = ' || sql_statement);
    EXECUTE IMMEDIATE sql_statement;

  END LOOP;

END;
/