Bagaimana Memproteksi Kode PL/SQL Memakai Function dan Procedure wrapping DBMS_DDL?

Kebutuhan ini sangat vital dimiliki programmer APEX untuk melindungi hak intelektual atau kerahasian sebuah kode contoh menyembunyikan teknik otentifikasi dari user. Karena sampai versi terakhir disisi aplikasi belum menyediakan tool ini.

Dengan memakai feature dari oracle database 11g kita akan memakai CREATE_WRAPPED Procedures atau WRAP Functions yang dijelaskan di dokumentasi “Oracle® Database PL/SQL Packages and Types Reference 11g”. berbeda dengan teknik 10g pada 11g jauh lebih simple.

Namun penjelasannya sangat singkat dan membingungkan sehingga perlu penjelasan lebih lanjut. Contoh pemakaian DBMS_DDL.CREATE_WRAPPED ada dua cara:

1. Cara singkat dengan single parameter tipe varchar2:

DBMS_DDL.CREATE_WRAPPED (
   ddl VARCHAR2);

2. Cara detil dengan 3 parameter tipe DBMS_SQL.VARCHAR2A dan PLS_INTEGER

DBMS_DDL.CREATE_WRAPPED(
    ddl DBMS_SQL.VARCHAR2A,
    lb PLS_INTEGER,
    ub PLS_INTEGER);

Dengan bantuan mbah google akhirnya diperoleh contoh pemakaiannya yaitu:

1. Cara pertama memakai bantuan quote q!' ddl script'! (sumber Tyler Muth’s Blog) :

declare
  l_function    varchar2(32767);
begin
  l_function := q'!
    create or replace function wrap_test
      return varchar2
    is
    begin
       return 'Yep, it worked';
    end wrap_test; !';
  sys.dbms_ddl.create_wrapped(l_function);
end;
/

2. Cara kedua (sumber Oracle Base) :

DECLARE
  l_source  DBMS_SQL.VARCHAR2A;
  l_wrap    DBMS_SQL.VARCHAR2A;
BEGIN
  l_source(1) := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS ';
  l_source(2) := 'BEGIN ';
  l_source(3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
  l_source(4) := 'END get_date_string;';

  l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source,
                              lb  => 1,
                              ub  => l_source.count);


END;
/

Contoh hasil wrapping cara pertama :

CREATE OR REPLACE function wrap_test wrapped 
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
6e 9a
IgQHR6lRS46V6yUCBpIlirzcrOowg8eZgcfLCNL+XhaW8lbjlqFi0e4JptbKschQyiQCfMbK
FyjGyu9LTRd8dcDAMv7S1sXWJl7QWWJQzIVWzBZylpfcLszEOdmYkr4WRlutOdsHUzkHVNV8
pqbvCP2M
/

Anda bisa juga memakai function DBMS_DDL.WRAP dengan menganalisa dari contoh pemakaian procedure DBMS_DDL.CREATE_WRAPPED.

Mengirim HTML email melalui PL/SQL and Gmail (2)

Pada contoh posting sebelumnya masih sederhana dimana kita belum bisa mengirim email dengan attachment, html. untuk contoh selanjutnya kita akan memperluas procedure kirim email melalui procedure berikut dengan sedikit modifikasi pada bagian :

• deklarasi  spesifik ke gmail :

v_smtp_domain    varchar2 (256)     := 'gmail.com';

• menambahkan

utl_smtp.ehlo(v_mail_conn, v_smtp_domain);

• menon aktikan

-- utl_smtp.helo(v_mail_conn, p_smtp_host);

Maka sekarang kita bisa gunakan untuk kebutuhan gmail dengan stunnel :

CREATE OR REPLACE PROCEDURE custom_send_mail (
  p_to          IN VARCHAR2,
  p_cc          IN VARCHAR2 DEFAULT NULL,
  p_bcc         IN VARCHAR2 DEFAULT NULL,
  p_from        IN VARCHAR2,
  p_subject     IN VARCHAR2,
  p_text_msg    IN VARCHAR2 DEFAULT NULL,
  p_html_msg   IN CLOB DEFAULT NULL,
  p_attachments IN table_attachments DEFAULT NULL,
  p_smtp_host   IN VARCHAR2,
  p_smtp_port   IN NUMBER DEFAULT 25,
  p_username    IN VARCHAR2 DEFAULT NULL,
  p_password    IN VARCHAR2 DEFAULT NULL)
AS
  v_mail_conn   UTL_SMTP.connection;
  v_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
  v_step        PLS_INTEGER  := 24573;
  v_length PLS_INTEGER;
  v_begin PLS_INTEGER := 1;
  v_buffer_size INTEGER := 75;
  v_raw RAW(32767);
  v_smtp_domain    varchar2 (256)     := 'gmail.com';
BEGIN
  /* **** Author: Josep Coves
      **** Version: 1.0
      **** Date: 08/06/2012
      **** josepcoves@relational.es
      **** http://www.relational.es
      **** Based on Tim's procedure, Thanks  Tim!
      **** http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html
   */
  v_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  utl_smtp.ehlo(v_mail_conn, v_smtp_domain);
  utl_smtp.command( v_mail_conn, 'AUTH LOGIN'); 
 
  --Establish SMTP connection
  IF (p_username IS NOT NULL) THEN
    utl_smtp.command( v_mail_conn, UTL_RAW.cast_to_varchar2( UTL_ENCODE.base64_encode( UTL_RAW.cast_to_raw( p_username ))) );
    utl_smtp.command( v_mail_conn, UTL_RAW.cast_to_varchar2( UTL_ENCODE.base64_encode( UTL_RAW.cast_to_raw( p_password ))) );
  END IF;
 
 -- utl_smtp.helo(v_mail_conn, p_smtp_host);
  utl_smtp.mail(v_mail_conn, p_from);
  utl_smtp.rcpt(v_mail_conn, p_to);
 
  utl_smtp.open_data(v_mail_conn);
 
  utl_smtp.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  utl_smtp.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  utl_smtp.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  IF (p_cc IS NOT NULL) THEN
    utl_smtp.write_data(v_mail_conn, 'CC: ' || p_cc || UTL_TCP.crlf);
  END IF;
  IF (p_bcc IS NOT NULL) THEN
    utl_smtp.write_data(v_mail_conn, 'BCC: ' || p_bcc || UTL_TCP.crlf);
  END IF;
  utl_smtp.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  utl_smtp.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  utl_smtp.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  utl_smtp.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
  --Plain Text Body
  IF p_text_msg IS NOT NULL THEN
    utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
    utl_smtp.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
 
    utl_smtp.write_data(v_mail_conn, p_text_msg);
    utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
 
  ELSIF p_html_msg IS NOT NULL THEN
    -- HTML Body
    utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
    utl_smtp.write_data(v_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_html_msg) - 1 )/v_step) LOOP
      utl_smtp.write_data(v_mail_conn,  DBMS_LOB.SUBSTR(p_html_msg, v_step, i * v_step + 1));
    END LOOP;
 
    utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;
 
  IF p_attachments IS NOT NULL THEN
 
   FOR i IN 1..p_attachments.COUNT
   LOOP
 
     utl_smtp.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
     utl_smtp.write_data(v_mail_conn, 'Content-Type: ' ||p_attachments(i).attachment_mime || UTL_TCP.crlf);
     utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
     utl_smtp.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attachments(i).attachment_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
 
     v_length := DBMS_LOB.getlength(p_attachments(i).attachment_blob);
     v_begin := 1;
     v_raw := NULL;
     v_buffer_size := 75;
     WHILE v_begin < v_length LOOP
        DBMS_LOB.read( p_attachments(i).attachment_blob, v_buffer_size, v_begin, v_raw );
        utl_smtp.write_raw_data( v_mail_conn, UTL_ENCODE.base64_encode(v_raw) );
        utl_smtp.write_data( v_mail_conn, UTL_TCP.crlf );
        v_begin := v_begin + v_buffer_size;
     END LOOP ;
 
    utl_smtp.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
 
   END LOOP;
 
  END IF;
 
  utl_smtp.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);
  utl_smtp.close_data(v_mail_conn);
 
  utl_smtp.quit(v_mail_conn);
 
END;
/

Selanjutnya kita bisa pakai untuk mengirim, text bisa, html dan attachement. berikut contoh pemakaian untuk mengirim gambar product dari tabel DEMO_PRODUCT_INFO.

DECLARE
  v_clob clob := 'Test email';
  v_blob blob;
 
  t_a table_attachments := NULL;
  v_a mail_attachment ;
BEGIN
 
   SELECT mail_attachment(t.FILENAME,t.MIMETYPE,t.PRODUCT_IMAGE)
              BULK COLLECT INTO t_a
     FROM DEMO_PRODUCT_INFO t;
 
     custom_send_mail (p_to     => '<your_recipient@mail.com>',
                       p_from   => '<yourgmailname@gmail.com>',
                       p_subject     => 'test',
                       p_html_msg => v_clob,
                       p_attachments => t_a,
                       p_smtp_host =>  'localhost',
                       p_smtp_port   => 1925,
                       p_username => 'yourgmailname@gmail.com',
                       p_password  =>  'yourgmailpassword');
END;
/

Maka pada email penerima akan muncul:

Mengirim HTML email melalui PL/SQL and Gmail (1)

Didalam apex memungkinkan kita untuk integrasi dengan system email smtp yang telah ada. Anda bisa setup melalui WS: internal pada bagian Manage Instance>>Instance Settings>>Email

Dengan integrasi itu kita bisa memakai package yg telah disediakan apex yaitu : APEX_MAIL dan bisa melakan report subscription dan download report melalui email.

Namun saat kita tidak memiliki server email yang bisa kita pake, maka kita bisa memakai alternatif dari gmail. untuk mengimplementasikannya tanpa banyak nyeng-nyong lagi anda bisa pelajari dari bang Håvard Kristiansen blog.

Catatan :

• Anda harus mengerti konsep ACL (jika memakai 11g)
• Anda telah memberi akses ke package UTL_SMTP dan UTL_TCP.
• Email bisa dikirm melalui pesan html dengan memodifikasi code:

write_mime_header (l_conn, 'Content-Type', 'text/plain');

Menjadi

write_mime_header (l_conn, 'Content-Type', 'text/html');

• Anda bisa menambahkan inline image (image harus public bisa dibaca) atau style
• Saya sudah test kirim ke yahoo dan gmail, untuk yahoo image tampil, gmail tidak berhasil

Contoh Pemakaian :

DECLARE
  l_html VARCHAR2(32767);
BEGIN
  l_html := '<html>
    <head>
      <title>HTML with table message</title>
    </head>
    <body>
      <p>This is a <b>HTML with Table and Image</b> <i>version</i> of the test message.</p>
<table border="1"><tbody>
<tr> <th>Hari</th> <th>Topik</th>  </tr>
<tr> <td>Hari 1</td> 
<td><ul>
<li>Preview Oracle APEX</li>
<li>Instalasi APEX dengan arsitektur EPG</li>
<li>Membuat dan mengatur workspace</li>
<li>Akses file image memakai FireFTP</li>
</ul>
</td>
</tr>
<tr> <td>Hari 2</td> 
<td><ul>
<li>Konsep SQL Workshop</li>
<li>Manipulasi Objek Database memakai SQL Workshop (tutorial)</li>
<li>Konsep Application Builder</li>
<li>Konsep Session State &amp; URL Syntax</li>
<li>Membuat Aplikasi Database dari sebuah file spreadsheet (tutorial)</li>
</ul>
</td>
</tr>
<tr> <td>Hari 3</td> 
<td><ul>
<li>Konsep Form, Report</li>
<li>Konsep Page, region komponen (computation,process,validation dan branching)</li>
<li>Membuat Aplikasi Database From Scratch (tutorial)</li>
<li>Konsep chart, calendar dan map </li>
<li>Konsep Theme, template dan file </li>
<li>Membuat Aplikasi Database From Scratch II (tutorial)</li>
</ul>
</td>
</tr>
<tr> <td>Hari 4</td> 
<td><ul>
<li>Konsep Interactive Report</li>
<li>Konsep Shared Component</li>
<li>Konsep Navigasi,tree, list, tab, breadcrumb </li>
<li>Konsep Dynamic Action, Plugin</li>
<li>Demo tutorial </li>
</ul>
</td>
</tr>
<tr> <td>Hari 5</td> 
<td><ul>
<li>Konsep Administration</li>
<li>Konsep Application and Page Utilities</li>
<li>Konsep Security (authentication &amp; authorization)</li>
<li>Demo (tutorial)</li>
<li>Konsep Deploy Application</li>
<li>Demo (tutorial)</li>
</ul>
</td>
</tr>
</tbody></table>      
      <p><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDRQTzDp14vFVpCREZ3WKxvbK8RxY4y8XzLCrwjwcudiHVmn7uRIcQ_M3uPMl9kQp7HT5P8zPc9m_QnLRT4vxSdLc-QpurQ1ecPTqrDT_K1AjmP58ChPME9GLmrG8GL7511eiIVeHSq9w/s200/bonexlogo.png" alt="Bonex Logo" />
    </body>
  </html>';

           g_mail. send_mail_html (
      'yourgmailaccount@gmail.com'
    , 'yourmail_recipient'
    , 'subject html',l_html
   );
END;
/

Tampilan di email yahoo penerima :

How to Obfuscate DADS Password on OHS 11g Windows

Pada posting sebelumnya untuk OHS 10g bisa dilihat disini.
Untuk OHS 11g ternyata berbeda, file dadtool.README yang memuat step-by-step obfuscate password tidak ada. tapi bisa di temui langsung di file dadtool.pl atau melalui script

perl dadTool.pl -h

Namun meskipun kita diikuti tetap tidak berhasil untuk kasus windows. Setelah coba sana-sini plus googling ternyata langkahnya berbeda. Yaitu:

1. Lewat command prompt masuk ke direktori %ORACLE_HOME%\bin :

cd D:\bea_default\Oracle_WT1\bin

2. Set ORACLE_HOME :

set ORACLE_HOME=D:\bea_default\Oracle_WT1

3. Lakukan Obfuscating:

perl dadTool.pl -f D:\bea_default\Oracle_WT1\instances\instance1\config\OHS\ohs1\mod_plsql\dads.conf

4. Pastikan Hasilnya:

All passwords successfully obfuscated. New obfuscations : 1

Catatan :

• Oracle Home: D:\bea_default\Oracle_WT1
• File dads.conf ada di: D:\bea_default\Oracle_WT1\instances\instance1\config\OHS\ohs1\mod_plsql

Migrating APEX from EPG to OHS

Mungkinkah hal itu bisa dilakukan dalam mesin yang sama?
Ternyata bisa dan cukup mudah. workspace dan aplikasi yang telah ada tidak ada pengaruhnya, hanya masalah memindahkan broker dan File images saja antara EPG menjadi OHS.
Lakukan hal-hal berikut:

1. Install OHS. contoh home D:\bea_default\Oracle_WT1
2. Pastikan akses ke http://localhost:7777 berhasil (default OHS 7777)

3. Pindahkan file images ke folder images dimana /i/ OHS akan di definisikan. contoh D:\bea_default\APEX41. (pindahkan memakai fireFTP, Filezilla atau cyberduck).
4. Unlock dan beri password user APEX_PUBLIC_USER
5. Edit dads.conf

Alias /i/ "D:\bea_default\APEX41\images/"
<location /pls/apex>
Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString localhost:1521:ORCL 
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword password_APEX_PUBLIC_USER_ente
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</location>

6. Stop OPM.
7. Delete file images yang ada di database (memakai fireFTP, Filezilla atau cyberduck).
8. Tutup port 8080 yang dipake EPG

EXEC DBMS_XDB.SETHTTPPORT (0);

9. Lock user ANONYMOUS.
10. Start OPM dan HTTP server.
11. Akses kembali apex dengan http://localhost:7777/pls/apex 

Catatan : hati-hati pada langkah no 3 adalah memindahkan dari images EPG yg ada didatabase bukan images hasil extract apex. sehingga kerjaan sebelumnya tidak akan berubah.

Changing Listener Port Numbers

Pada kasus ini kita akan mencoba merubah port apex yang terinstall dalam db XE dari default 1521 ke 1522. Hal ini dilakukan karena dalam pc ada dua db yang memakai port yang sama yaitu 1521. jadi salah satu harus kita ubah supaya tidak terjadi konflik.

1. Stop the listener
2. Buka file listener.ora yang ada di DIR:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\
3. Ubah port 1521 menjadi 1522.
4. Simpan file.
5. Start listener.
6. melalui command prompt masuk kedalam sqlplus
7. Connect dengan system

8. ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))";

9. ALTER SYSTEM REGISTER;

10. verifikasi memakai command lsnrctl status pastikan muncul "Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service..."
11. akses kembali apex http://localhost:8080/apex 

Sumber : Managing Network Connections on Oracle® Database Express Edition 2 Day DBA

Oracle Application Express 4.2 on horizon !

Per tanggal 2 September 2012 apex.oracle.com telah di-upgrade ke Oracle Application Express 4.2. Ehm...menunggu kapan release versionnya untuk dicoba lebih detil. Akankah sesuai dengan janji SOD-nya di April 2012.

Perasaan kalo di release di tahun ini terlalu cepat yak... menurut saya harus lebih lama dulu ditahan supaya bug-bugnya banyak berkurang. Sejak release 4.0 Oracle agresif bener  membuat release terbaru...hebaaat padahal ini teknologi katanya gak bakal di charge include lisensi DB. Jadi ngeper juga nih nanti lama-lama bisa jadi muncul lisensi juga.

Silahkan langsung cek ke TKP perubahan tampilan dan feature-nya.