Get Yahoo Exchange Rate using PL/SQL

Setelah mencari api yang disediakan oleh Bank Indonesia atau bank-bank swasta/nasional indonesia tidak tersedia. maka dengan terpaksa kebutuhan exchange rate real time saya ambil dari yahoo api.

Melalui Yahoo Query Language kita dapat peroleh URI untuk exchange rate USD-IDR :

http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.xchange where pair in ("USDIDR")&env=store://datatables.org/alltableswithkeys

Lalu kemudian kita bangun fungsi didalam oracle untuk bisa dipakai dalam operasi sql pl/sql atau di APEX.

create or replace function get_yahoo_rate 
return number 
is

/******************************************************************************
   NAME:       get_yahoo_rate
   PURPOSE:    get latest USD-IDR rate from Yahoo finance exchange

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        10/10/2014   Lutfi Hedir     1. Created this function.

******************************************************************************/
  l_xml xmltype;
  l_yahoo_uri constant varchar2(500) := 'http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20(%22USDIDR%22)&env=store://datatables.org/alltableswithkeys';
  l_rate      varchar2(500);
  l_rate_n    number(10);
begin
  -- get xml data
  l_xml := xmltype(utl_http.request(l_yahoo_uri));
  -- extract rate value from xml data  
  select extractvalue(l_xml, '/query/results/rate/Rate/text()') into l_rate
    from dual;  
  -- convert to number   
  l_rate_n := to_number(REGEXP_SUBSTR(l_rate,'[0-9]+'));
  
  return l_rate_n;
  
end get_yahoo_rate;
/

Catatan :

• Jangan lupa setting ACL untuk 11g supaya bisaakses internet.
• Jika akses SSL buatlah wallet terlebih dahulu
• Jika akses proxy modifikasi utl_http.request dengan signature proxy dan wallet

How to create executable package/procedure on URL

Posting ini sebenarnya pernah saya bahas, namun sekarang saya akan membahasnya lebih spesifik pada download file dengan web server apex listener.

Adapun hal yang harus diperhatikan pada bagian

1. Package/procedure
• Contoh

create or replace PACKAGE BODY web_utils IS
  -- retrieves image from blob in product info
  PROCEDURE display_bu_logo (p_id number, p_product_name varchar2) 
  IS
    l_mime        varchar2 (255);
    l_length      number;
    l_file_name   varchar2 (2000);
    lob_loc       blob;
  begin
    if p_id is not null then
      select mimetype
           , filename
           , nvl(product_image, empty_blob()) blob_content
           , dbms_lob.getlength(product_image)
        into l_mime
           , l_file_name
           , lob_loc 
           , l_length
        from demo_product_info
       where product_id = p_id;    
    elsif p_product_name is not null then
      select mimetype
           , filename
           , nvl(product_image, empty_blob()) blob_content
           , dbms_lob.getlength(product_image)
        into l_mime
           , l_file_name
           , lob_loc 
           , l_length
        from demo_product_info
       where lower(product_name) = lower(p_product_name);     
    end if;


   owa_util.mime_header (nvl (l_mime, 'application/octet'), false);

   -- set the size so the browser knows how much to download
   htp.p ('Content-length: ' || l_length);
   -- the filename will be used by the browser if the users does a save as
   htp.p (   'Content-Disposition:  attachment; filename="'
          || replace (replace (substr (l_file_name,
                                       instr (l_file_name, '/') + 1
                                      ),
                               chr (10),
                               null
                              ),
                      chr (13),
                      null
                     )
          || '"'
         );

   -- close the headers
   owa_util.http_header_close;
   -- download the BLOB
   wpg_docload.download_file (lob_loc);

  END display_bu_logo;
END web_utils;

• Tambahkan nvl(lob_column, empty_blob())
• Gunakan htp.p
• Untuk kasus image jika ingin tampil inline, lakukan comment pada bagian

--   htp.p (   'Content-Disposition:  attachment; filename="'
--          || replace (replace (substr (l_file_name,
--                                       instr (l_file_name, '/') + 1
--                                      ),
--                               chr (10),
--                               null
--                              ),
--                      chr (13),
--                      null
--                     )
--          || '"'
--         );

2. Security
• Anda harus tahu environment server apa yang dipakai, apakah EPG, OHS atau APEX Listener
• Untuk APEX Listener/OHS anda cukup grant APEX_PUBLIC_USER
• Untuk OHS grant APEX_PUBLIC_USER dan daftarkan procedure yang akan dipanggil di APEX_XXXXXX.wwv_flow_epg_include_mod_local
• Untuk EPG anda grant ke ANONYMOUS dan aktifkan procedure di APEX_XXXXXX.wwv_flow_epg_include_mod_local
3. Cara memanggil dengan signature parameter.
• mulai dengan ? pisahkan dengan &
• Posisi tidak penting namun harus disebutkan semua kecuali punya default value
• Untuk kasus APEX listener parameter signature akan selalu dirubah dengan menjadi lowercase, hati-hati jika memakai parameter non-case-sensitive seperti BI Publisher web service
• contoh

http://localhost:8888/ords/hr.web_utils.display_bu_logo?p_product_name=Blouse&p_id=

• contoh array parameter

http://localhost:8888/ords/!hr.web_utils.display_bu_logo?p_product_name=Blouse&p_id=

Catatan :

• APEX Listener / ORDS 2.0.8.163.10.40

ORA-02069: global_names parameter must be set to TRUE for this operation

Pernah mengalami error seperti itukah?
jika anda menggunakan operasi DML terhadap sebuah database link maka mungkin anda akan mengalami hal tersebut.

Cause : You are trying to make DML operation on the remote database using local function.
artinya anda memakai sebuah lokal function untuk digunakan dalam proses DML.
contoh :

 
insert into mytable@mylink 
     ( ename
     , nipeg
     , start_date
     , end_date)
select ename
     , nipeg
     , get_start(nipeg)
     , get_end(nipeg)
  from emp;

Itu karena  value dari global name database anda adalah false :

SELECT NAME, VALUE 
  FROM V$PARAMETER
 WHERE NAME = 'global_names'

Biasanya developer bukan seorang DBA, maka solusi yang bisa pakai tanpa merubah value global name menjadi true adalah merubah proses DML dengan tidak memakai fungsi get_start,get_end secara langsung dalam proses insert. bisa memakai sebuah cursor atau collection. Merubah global name value menjadi TRUE juga memberi implikasi pada dblink yang lain, kemungkinan besar akan error jika tidak memakai nama link seperti global names db tujuan
Contoh dalam cursor :

begin
  for c_emp in ( select ename
                      , nipeg
                      , get_start(nipeg) start_date
                      , get_end(nipeg) end_date
                   from emp)
  loop
    insert into mytable@mylink 
         ( ename
         , nipeg
         , start_date
         , end_date)
    values 
         ( c_emp.ename
         , c_emp.nipeg
         , c_emp.start_date
         , c_emp.end_date);
  end loop;
end;

contoh dalam collection :

declare
 type t_emp is table of mytable@mylink%rowtype
      index by pls_integer;
 c_emp t_emp;
begin
  select ename
       , nipeg
       , get_start(nipeg) start_date
       , get_end(nipeg) end_date
    bulk  collect  
    into c_emp;

  for i in c_emp.first .. c_emp.last
  loop
    insert into mytable@mylink 
         ( ename
         , nipeg
         , start_date
         , end_date)
    values (c_emp(i).ename
         , c_emp(i).nipeg
         , c_emp(i).start_date
         , c_emp(i).end_date);
  end loop;
end;

Catatan : anda tidak bisa memakai operasi forall thd database link;

Bulk collect and forall

Delapan tahun freelance ternyata seperti halnya katak dalam tempurung, karena penyelesaian maslah biasanya bersifat shortterm yang penting bisa solve dalam sebuah kasus, tanpa lebih lanjut inspeksi kedalam teknik yang lebih baik lagi.

Salah satunya adalah terjadi ketika sebuah teknik kursor yang melibatkan proses DML, biasanya saya memakai teknik kuno (old fashion way), yaitu memakai cursor dengan row-by-row proses. Ketika di teliti oleh teman seperjuangan di AUS. Dia dapat menunjukkan cara terbaik. Contoh berikut adalah proses sebanyak 10.000 row

• Cursor Teknik lama : Elapsed: 00:00:12.703

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;
 l_tab  a_cur%ROWTYPE;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  into l_tab;
    exit when a_cur%notfound;  
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab.id, l_tab.code, l_tab.description);         
  end loop;  
  close a_cur;
  commit;
end;
/

• Cursor bulk collect : Elapsed: 00:00:11.281

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;
 TYPE myarray IS TABLE OF a_cur%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  bulk collect into l_tab limit 1000;
    exit when l_tab.count = 0;
    for i in l_tab.first .. l_tab.last loop
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);
      
    end loop;    
    
  END LOOP;
  close a_cur;

    commit;
END;
/

• Cursor bulk collect dan forall : Elapsed: 00:00:01.969

set timing on
DECLARE
 CURSOR a_cur IS 
 SELECT *
    from forall_test;

 TYPE myarray IS TABLE OF a_cur%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  OPEN a_cur;
  loop
    fetch a_cur  bulk collect into l_tab limit 1000;
    exit when l_tab.count = 0;
    forall i in l_tab.first .. l_tab.last 
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);   
    
  END LOOP;
  close a_cur;

    commit;
end;
/

• Tanpa Cursor bulk collect dan forall : Elapsed: 00:00:01.797

set timing on
DECLARE
 TYPE myarray IS TABLE OF forall_test%ROWTYPE index BY PLS_INTEGER;
 l_tab myarray;
begin
  execute immediate 'TRUNCATE TABLE forall_test2';
  select *
  bulk collect into l_tab 
  from forall_test;
   
    forall i in l_tab.first .. l_tab.last 
      INSERT INTO forall_test2 (id, code, description)
      values (l_tab(i).id, l_tab(i).code, l_tab(i).description);
    commit;
END;
/

Special Thanks to Chris Finney for point this out.
Source :
1. Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle.
2. PL/SQL Collections and Records.
3. Tuning PL/SQL Applications for Performance.

Hidden treasure of Interactive Report

Salah satu feature terbaik dari APEX adalah Interactive Report. Pada posting kali ini saya akan membuka feature dari IR dalam hal query reference.

• Feature query IR<OPERATOR>_<COLUMN_ALIAS>.
Salah satu yang sering kita pakai adalah IR_DEPARTMENT_ID artinya kita melakukan filter terhadap kolom department ID, IR by default: EQUAL. Ternyata banyak lagi operator yang bisa kita pakai dalam melakukan filter.

Daftar valid operator

Operator	Description
EQ	Equals (this is the default)
NEQ	Not equals
LT	Less than
LTE	Less than or equal to
GT	Greater than
GTE	Greater than or equal to
LIKE	SQL 'like' operator
N	is null
NN	is not null
C	Contains
NC	not contains
IN	SQL 'in' Operator
NIN	SQL 'not in' Operator

sumber : Advanced Interactive Report by David Peake.
 
• URL reference terhadap saved report.

Jika kita memiliki beberapa saved report, maka kita bisa melakukan reference ke page tujuan dengan memilih apakah memakai saved report 1 atau yang lain dalam contoh : IR_REPORT_DEFAULT atau IR_REPORT_CHART.

Handling ssl on Soap UI

Jika anda mengalami masalah saat url ssl pada Soap UI seperti berikut :

Maka perlu lakukan hal berikut untuk menambahkan certificate keystore ke dalam cacerts Soap UI:

• Import certificate root kedalam file contoh : D:\myca.cer (gunakan Chrome atau IE).
• lalu memakai bantuan java buka command prompt, pastikan PC anda telah terinstall java sdk
• lalu jalankan perintah berikut : keytool -import -alias owsservices -file -keystore "c:\program files (x86)\smartbear\soapui-4.6.4\jre\lib\security\cacerts".

Jika masih bermasalah coba jalankan script diatas di java bin lengkap contoh : C:\Program Files\Java\jdk1.6.0_31\bin

Note : Thanks Hoki for point this out.

Tips Belajar Oracle APEX

Posting ini adalah sharing pengalaman saya saat belajar teknologi ini, mulai dari 0 sampai sekarang, perasaan sih masih 0 juga :D.Kenapa saya katakan dari 0 karena saya berangkat dari oracle form dan report sejak tahun 2002 sampai 2009. Yang ternyata hanya pengetahuan PL/SQL yang akan digunakan sebagai modal untuk kedunia APEX. Sedangkan HTML/CSS, JS dengan segala tetek bengeknya sangat asing buat saya.

Ketika di masa oracle Form 10g terakhir yang saya pakai, saya mengambil langkah untuk belajar web murni, karena secara pribadi saya anggap oracle form sudah "obsolete". dan ingin masuk ke web "murni", mulailah coba install  PHP karena gak dapat project di situ akhirnya mandek. Terus mencoba belajar JAVA, pas install dan mau belajar java yang mana nih banyak bener variasinya? bingung lagi. Akhirnya seorang client diakhir 2008 meminta saya untuk mempelajari teknologi ini, namun karena project di oracle form saya lupakan. Saat masuk 2009 ketika nganggur gak ada proyek saya iseng bongkar file dan ketemu dokumen lama "2 Days + Developer Guide", lalu coba install dan ikuti langkah-langkahnya wah menarik dan mudah. Akhirnya saya memastikan ini yang akan saya pelajari.lalu saya coba ubah existing apps yang saya bangun dari oracle form menjadi APEX.

Ketika musim project mulai tidak mudah ternyata meyakinkan client untuk pindah, selain tidak populer juga akan merubah mindset user untuk cara pemakaian. Saya rayu kembali dengan demo apps versi apex, client belum juga goyah. Lalu langkah terakhir saya demokan fitur-fitur terbaik salah satunya yang membuat mereka luluh adalah Interactive Report "Wow" mereka suka dan diputuskan oke kita pindah ke APEX.

Oke bagi pembaca yang tertarik untuk belajar teknologi ini teknik ini berdasar pengalaman pribadi belajar apex tanpa mentor atau training resmi, cara ini saya anggap cara bonex:

• Ubah Paradigma dan persepsi.

Dulu ketika kita tahu Oracle maka persepsi kita akan tentang Database, lalu developer mereka oracle form, report, designer dan discoverer. Oracle APEX agak jauh dari itu, teknologi ini membawa anda ke dunia web development membutuhkan banyak kemampuan HTML/CSS, JS dan pastinya PL/SQL sebagai dasar.

Documentasi tutorial tentang APEX memang sangat mudah, mungkin seakan-akan tidak terlalu butuh skill web developer, saya pastikan YA untuk simple apps. Namun kita masuk dunia nyata di proyek dengan permintaan user yng semakin kompleks, UI yang interaktif maka disitulah pengetahuan web diminta.

Saya malah lebih prefer ambil engineer yang pengalaman di PHP, .net dll, dibanding harus ambil dari background oracle form. Saat perusahaan membutuhkan engineer APEX. karena selain dominan di SQL, PL/SQL, UI tidak lepas dari HTML/CSS dan JS.

• Belajarlah dari Oracle jangan dari Google.

Banyak yang saya lihat ketika seseorang mau belajar APEX langsung "Jump into action" dengan cara googling. Saya sarankan jangan, tapi lakukan install APEX di oracle XE, lalu download full library. Disitu anda akan memperkuat basic konsep dari APEX terlebih dahulu.

Lalu cobalah buat aplikasi dengan mengikuti dokumen dengan belajar melalui Oracle By Example.

• Belajar APEX dari APEX.

Salah satu feature learning apex yang banyak terlupakan adalah belajar APEX dari APEX. Caranya adalah dengan install Sample apps di apex lantas pelajarilah bagaimana mereka membangun aplikasi kecil itu. Disitu akan diperoleh banyak teknik yang berguna.

• Jangan pernah memulai dari Cook Book.

Segala sesuatu itu harus mulai dari nol dan bergerak perlahan, jangan berharap langsung bisa berlari tanpa harus merangkak terlebih dahulu. kenapa saya sama sekali tidak menyarankan baca cookbook untuk tingakt pelajar pemula karena disana anda hanya mempelajari shortcut2 tanpa memperkuat pengetahuan dasar fondasi konsep kuat dari APEX. Saran saya untuk selain Application Builder User's Guide adalah Beginning Application Express 4 lalu bisa lanjut ke Pro Oracle Application Express 4. Disana anda akan memperoleh kosep yang bagus bagaimana APEX bekerja dan dibangun.

• Implementasikan dalam sebuah Proyek.

Apalah artinya teori atau ikut kursus sebuah teknologi jika tidak pernah dipakai dalam real case sebuah pembuatan aplikasi dari teknologi tersebut?. Seperti yang telah saya ceritakan bagaimana nekatnya sy mencoba meyakinkan sebuah client untuk memakai teknologi ini dengan pengetahuan pas-pasan, gak apa-apa kalau berani jadi pintar pasti harus melalui jalan tolol dulu :D, kalau saya review coding lama maka terlihat bagaimana teknik yang dipakai kadang tidak efektif, aneh tapi sampai juga hehehe. belajar tapi salah gak apa-apa asal tidak dianggap menjadi sebuah kebenaran itu prinsip bonex apex...cling

• Ikutlah komunitas.

Inilah yang tidak kita miliki, mereka sangat bagus melakukan organisasi komunitas dan berbagi. Semakin banyak orang pintar dalam sebuah teknologi maka semakin cepat teknologi tersebut berkembang, taukah anda bahwa pengembang APEX sekarang adalah dulu dari orang-orang pemakai apex? bahwa sample apps, Team development diapex itu dari user2 yang pemakai yang akhirnya diadopsi oleh Oracle untuk masuk kedalam APEX?.

Maka saran ini berlaku pada anda yang ingin belajar, carilah di forum oracle apex, ikuti blog-blog, twitter para expertise APEX. Jangan malu bertanya, bahasa inggris pas-pasan gak masalah kalau perlu pakai bahasa tarzan. Toh mereka tau kita bukan orang inggris, dalam hal ini pakai lah adagium jawa "Isin Ra Katho'an" artinya anda boleh malu kalau gak pake sempak atau celana saja, selain itu gak usah malu. orang kita kan sering malu gak pada tempatnya.

• Apakah anda terlalu "Senior" untuk sebuah teknologi baru?

Ini pertanyaan paling awal sebenarnya sebelum memulai belajar APEX? tapi saya tempatkan terakhir :D. Ini sebuah dilema besar didunia IT Indonesia, programmer kita jarang yang ingin berlama-lama di posisi ini karena memang penghargaan pada programmer yang bagus tidak terlalu baik, orang yang agak senior sudah bisa dipastikan mulai menjauhi posisi ini, seakan-akan hanya buat anak kuliahan dan fresh grad. Jika menurut anda belum cukup tua maka silahkan explore dunia web teknologi yang luas ini untuk diimplementasikan di Oracle APEX. Mari sama-sama kita belajar

Salam Bonex APEX
Jangan tidur sebelum bisa !

Fenomena Oracle APEX di Indonesia

Tidak terasa sudah memasuki tahun ke 5 memakai teknologi ini. Dari release version 3.2 sampai 4.2.4 sangat menyenangkan, namun mengapa di asia tenggara teknologi ini tidak terlalu populer?

Jawabannya adalah karena yang mempopulerkan teknologi adalah kalangan komunitas oracle, bukan stakeholder dalam hal ini Oracle. Ada pengalaman menarik sebuah client pemakai APEX menyatakan di depan sales oracle , si sales malah balik tanya teknologi apa itu? hehehe.Kanapa para sales ini tidak mengajukan alternatif ini? karena sudah jelas ini sebuah feature dari Database oracle layaknya oracle SQL developer, data modeler dll yang tidak akan kena lisensi tambahan.dan tidak akan menjadi nilai tambah bagi mereka bukan.

Pernah juga disuruh melakukan test wawancara thd seorang kandidat yang katanya pernah pakai, ketika saya tanya apa itu APEX? jawaban lugasnya adalah "salah satu Oracle Report tool"!!!, saya cuma garuk-garuk kepala.

Saya pernah ajukan teknologi sebagai salah basis tenologi pengembangan dan cloud, jawabannya sudah bisa ditebak belum bisa meyakinkan :D. Sampai pada keputusan oke saya akan lakukan ini sendiri.

Cara melihat popularitas teknologi gampang, lihat saja di job hunter disebuah region, maka disitu akan menunjukkan trend populer sebuah teknologi. Sampai saat ini teknologi ini belum begitu populer di asia.

Semakin kuat  komunitas oracle di sebuah negara maka teknologi ini semakin populer itu yang sepanjang pengamatan pribadi saya.

Kelemahan sysdev programmer di asia adalah :

• Lemahnya komunitas.

Di asia pada umumnya komunitas developer tidak sekuat Amerika, Eropa dan Australia. Ini membawa dampak pada kekurangan resource untuk belajar bagi para pemula serta mengikuti trend teknologi. Di region yang saya sebutkan mereka sangat kuat bahkan konferensi selalu rutin dilakukan dengan resource yang tertata rapi. jangan salah sangka saya disini berbicara atas komunitas bukan kemampuan orang per orang.

•  Selalu under-priced sebagai developer.

Sistem development di indonesia susah, coba sebutkan sebuah perusahaan IT besar yang berbasis sisdev murni, jawabannya tidak ada. semua harus di-support dengan jual hardware, lisensi dsb. Sebagai developer pasti akan dihargai lebih murah dari system analist atau bisnis analis apalagi dibandingakn dengan konsultan fungsional. Akhirnya programmer muda pikirannya selalu ingin cepat pindah dari dunia development ke "menejemen" supaya salary naik. Sedangkan di luar tidak demikian, programmer dihargai atas skillnya, bahkan kalau lihat trendnya sekarang trand salaryy tertinggi adalah mobile developer. Dengan fenomena ini apa efeknya? resource yang berbahasa indonesia/native di asia akan susah di cari yang bagus. Coba buktikan di toko buku IT berapa banyak buku karangan berbahasa indonesia yang tebalnya bisa ratusan halaman? isinya biasanya tentang pengenalan, cara install dan cookbook tipis-tipis. bahkan yang paling memalukan saya pernah beli buku tentang HTML yang ternyata isinya banyak menterjemahkan isi dari w3schools.

• Kurang ketat terhadap standard SDLC.

Ini lebih banyak tentang pengalaman pribadi sih, cuma sekarang setelah berkumpul disebuah PMA dengan programmer yang lain itu juga yang terjadi. Kita kesulitan mengikuti standard, mungkin kelamaan dengan teknik bonex hahaha, standard coding, SDLC, versioning, semua harus terukur :D. Hingga pada suatu saat ketika saya ngotot "menyeleweng" dari pakem teman pernah pengalaman di konsulatan asia pasifik, lantas bilang "coba tunjukkan sebuah software asia yang mendunia?" saya jawab eeng bingung juga kasi contoh :D. Ya itu salah satu alasannya dia bilang karena kita tidak terlalu bagus dalam standard.

Namun dengan segala kelemahan disekitar kita, maka kita harus selalu kreatif dan belajar untuk membangan dunia software yang semakin menarik, memjanjikan, profitable dan layak untuk dijadikan mata air penghidupan.

Tetap semangat, jangan tidur sebelum bisa !!!
Salam Bonex

APEX, QR Code dan googleapis

Pada dasarnya QR code bisa memakai third party atau bisa di install sendiri untuk menghasilkan return URL atau binary.Pada kasus ini kita akan mencoba memakai googleapis yang mengembalikan image .png (binary).

Adapun hal yang bisa kita lakukan pada Oracle APEX adalah.

• Setup ACL (11g).

--- create new ACL for HR
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'hr_acl_file.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'HR',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  COMMIT;
END;
/
-- assign ACL to networks chart.googleapis.com
BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'hr_acl_file.xml',
    host        => '*.googleapis.com', 
    lower_port  => 80,
    upper_port  => 9999); 

  COMMIT;
END;
/

• Buat wallet memakai OWM atau ORAPKI (jika akses SSL).
• Buat function atau procedure input URL yang mereturn Binary.

Retrieving HTML and Binaries into Tables Over HTTP

• Lalu tampilkan dalam Report dan Form process APEX untuk menyimpan ke dalam BLOB kolom. 

declare
  v_encode_url varchar2 (1000);
begin
 v_encode_url := 'https://chart.googleapis.com/chart?chs='||:P2_SIZE||'&cht=qr&chl='||utl_url.escape(:P2_QRCODE);
    update emp
    set EMP_PHOTO =  return_binary_from_url (v_encode_url)           
       , ATTACH_MIMETYPE =  'image/png'         
       , ATTACH_FILENAME  =  :P2_EMPNO||'_QR_CODE.png'          
       , ATTACH_LAST_UPDATE = sysdate            
    where empno = :P2_EMPNO;
end;

Penampakan sebelum upload:

Penampakan sesudah upload:

Cara test koneksi tanpa proxy :

 
select utl_http.request('https://www.google.com'
                         , null
                         , 'file:/mywallets/google.com'
                         , 'your wallet password')
  from dual;

Cara test koneksi dengan proxy :

 
select utl_http.request('https://www.google.com'
                         , 'username:pwdproxy@yourproxyhost:yourproxyport'
                         , 'file:/mywallets/google.com'
                         , 'your wallet password')
  from dual;

Demo tidak bisa dibuat di apex.oracle karena tidak memungkinkan setup ACL dan wallet

QR Codes on APEX

Anda ingin menampilkan QR code kedalam APEX seperti ini ?

Jangan khawatir anda bisa memakai bantuan Google Developer untuk menampilkan di APEX.

Demo : di sini

Dynamic Highlight on classic & interactive report

Pada posting kali ini sya akan menunjukkan bagaimana melakukan highlight secara bersamaan pada classic report dan interactive report memakai Dynamic Action(DA).

Skenario :

• Sebuah Item Parameter bisa hidden atau select list : PXX_DEPT_HL.
• Sebuah report classic thd : DEPT 

select null "icon"
,deptno, DNAME
,case when deptno = :PXX_DEPT_HL then 'HIGHLIGHT' else null end HIGHLIGHT 
from dept

• Sebuah report interactive EMP.

select EMP.EMPNO as EMPNO,
    EMP.ENAME as ENAME,
    EMP.JOB as JOB,
    EMP.MGR as MGR,
    EMP.HIREDATE as HIREDATE,
    EMP.SAL as SAL,
    EMP.COMM as COMM,
    EMP.DEPTNO as DEPTNO ,case when deptno = :PXX_DEPT_HL then 'HIGHLIGHT' else null end HIGHLIGHT 
 from EMP EMP

• Saat icon pada report calssic di clik / atau nilai PXX_DEPT_HL berubah akan memeri hilghlight pada kedua report sesuai nilainya

Logic :

• Pada column icon buatlah sebuah link dengan action url:

  javascript:$s('PXX_DEPT_HL','#DEPTNO#');

• Pada column dname HTML Expression:

  <span id="#HIGHLIGHT#DNAME#R#">#DNAME#</span>

• Pada report interactive buatlah format highlight row dengan kodisi highlight=HIGHLIGHT.
• Buatlah DA thd item PXX_DEPT_HL dengan event : change dan action : refresh report classic.
• Buatlah DA pada report classic dengan event : after refresh dengan action : Execute Javascript Code :

  $("[id^=HIGHLIGHT]").closest('tr').children('td').css("background-color", "#FD4");

  dan Refresh region report interactive.

Demo : disini

Dynamic Interactive Report Using Collection

Salah satu kelemahan Interactive Report (IR) adalah hanya menyediakan type source SQL tidak ada pilihan untuk PL/SQL. Sehingga kita tidak bisa membuat dynamic query seperti pada classic report.

Namun hal tersebut masih kita bisa cari solusinya dengan cara :

• Membuat function return table pipelined atau.
• Memakai bantuan query terhadap APEX collection.

Pada posting saya ini akan menjelaskan pada teknik kedua yaitu Memakai bantuan query terhadap APEX collection.

Skenario :

• Kita akan membuat IR thd semua tabel yang ada di  skema HR untuk contoh demo (EMP & OEHR_EMPLOYEES).
• Kita hanya membatasi max  kolom sejumlah 10.
• Skema akses apex memiliki system privilege : SELECT ANY DICTIONARY.

Logic & Process :

• Membuat sebuah fungsi  yg secara dinamis bisa me-return 10 nama kolom tabel input.

create or replace 
function get_report_headers 
(
  p_table     in varchar2 default 'EMP'
, p_separator in varchar2 default ','
, p_type      in varchar2 default 'HEADER'
, p_num       in number default 1
) return varchar2 as 
  v_temp_arr apex_application_global.vc_arr2;
  v_list varchar2(32767);
begin
 if p_type = 'HEADER' then
  SELECT column_name bulk collect
    into v_temp_arr
    FROM USER_TAB_COLUMNS
   WHERE owner = 'HR'
     and table_name = p_table
     and owner = 'HR'
     and data_type <> 'BLOB'
   order by column_id;
  v_list := apex_util.table_to_string(v_temp_arr,p_separator); 
  return v_list;
 else
  if p_num > 10 then
   v_list := null;
  else
   begin 
    SELECT initcap(REPLACE(column_name,'_',' ')) 
      into v_list
      FROM USER_TAB_COLUMNS
     WHERE owner = 'HR'
       and table_name = p_table
       and owner = 'HR'
       and column_id = p_num; 
    exception when others then v_list := null;   
   end;    
   end if;
  return v_list;   
  
 end if;
end get_report_headers;

• Buatlah sebuah page dengan 10 item dan sebuah IR.
• Pada before header buatlah sebuah process untuk membuat sebuah collection unik.

declare
 v_query VARCHAR2(32767):=null;
 v_collection varchar2(100) := nvl(:P20_COLL_NAME,'DYNAMIC_'||:SESSION_ID);
begin
 v_query := 'select '||
             get_report_headers(p_table => :P20_TABLE_NAME) ||
            ' from HR.' || nvl(:P20_TABLE_NAME,'EMP');

IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => v_collection) THEN                 
  APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v_collection);
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
                                              ,p_query           => v_query );
else
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
                                              ,p_query           => v_query );
end if;
end;

• Pada before header buatlah sebuah process mengisi 10 item yang telah dipersiapkan untuk nama header kolom.

:P20_C01 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 1);
:P20_C02 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 2);
:P20_C03 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 3);
:P20_C04 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 4);
:P20_C05 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 5);
:P20_C06 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 6);
:P20_C07 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 7);
:P20_C08 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 8);
:P20_C09 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 9);
:P20_C10 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 10);

• Buatlah IR :

select *
  from APEX_collections
 where collection_name = :P20_COLL_NAME;

• Buatlah heading column secara dinamis me-refer ke item serta kondisi thd nilai item reference (C001...C010).

• Buatlah semua kolom condition : never (selain C001...C010).

Demo :  Dynamic IR with collection

Shuttle Item on APEX

Pada posting kali ini saya akan membahas bagaimana memakai Shuttle Item pada APEX. yang meliputi bagaimana :

• Membuat default value berdasarkan isi tabel.
• Submit kedalam tabel.
• Melakukan filter nilai lihat detil di post ini : Enhanced Shuttle Item by Christoper Beck.
• Dynamic Action (DA) terhadap Shuttle Item untuk mengambil nilai.
• Dynamic Action (DA) terhadap Shuttle Item untuk melakukan query thd report EMP-DEPT.

Skenario kita adalah table EMP sebagai source pegawai dan EMP_ASSIGN sebagai data pegawai yang telah dipilih dalam Shuttle Ittem. Table EMP_ASSGIN hanya berisi satu kolom atau lebih yang menyimpan data EMPNO dan EMPNAME.

• Membuat default value berdasarkan isi tabel.

Pada bagian ini anda harus sudah membuat shuttle item (PXX_SELECTED_EMPLOYEES) dengan query sbb:

SELECT ENAME
     , EMPNO
  FROM EMP
ORDER by 1

Lalu pada bagian render before header buatlah process : GET_SELECTED:

declare
 l_emp_array wwv_flow_global.vc_arr2;
 l_list varchar2(32767);
begin
 -- get all selected emp into array
 select empno 
   bulk collect
  into l_emp_array
  from emp_assign;
 -- convert array to colon seperated string
  l_list := apex_util.table_to_string(l_emp_array,':');
 :PXX_SELECTED_EMPLOYEES := l_list;
end;

Proses selanjutnya adalah bagaimana cara melakukan submit pegawai yang dipilih kedalam sebuah tabel.

• Submit kedalam tabel.

Pada bagian submitting page buatlah sebuah proces : SUBMIT_ASSIGNMENT :

DECLARE
v_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
 --delete existing assignment
 delete emp_assign;

 -- Convert the colon separated string into an array
 v_selected := APEX_UTIL.STRING_TO_TABLE(:PXX_SELECTED_EMPLOYEES);

 -- Loop over array and insert into table
 FOR i IN 1..v_selected.count LOOP
   insert into emp_assign
     (empno)
   values
     (v_selected(i));
END LOOP;
END;

Lalu proses selanjutnya adalah bagaimana memberi sebuah proses DA thd shuttle, dalam contoh ini kita akan mengambil nilai EMPNO yang dipilih kedalam sebuah text item : PXX_SELECTED_EMPNO.

• Dynamic Action (DA) terhadap Shuttle Item untuk mengambil nilai.

Pada proses ini buatlah DA thd PXX_SELECTED_EMPLOYEES beri nama :shuttle_changed dengan event : CHANGE. dengan kondisi is null, ini adalah bagian tricky khusus untuk tipe item shuttle, karena untum item biasa kita tidak pelu membuat kondisi biasanya :D.

Lalu pada TRUE action lakukan proses action Set Value dengan PL/SQL Expression : null.
Pada FALSE action Set Value dengan Javascript Expression :

$v("PXX_SELECTED_EMPLOYEES")

.
Keduanya terhadap Effected Elements : item : PXX_SELECTED_EMPNO.

Beres....

Keterangan :

• Demo hasil disini.