Oracle

Membahas semua hal tentang oracle

Create JOB di oracle

0
371 days
by in Oracle

Scheduller merupakan konsep penting untuk menjalankan sesuatu secara otomatis pada waktu yang sudah ditentukan, misal kita ingin melakukan update ke suata tabel secara otomatis setiap hari maka scheduller adalah yang kita perlukan. sekarang kita akan mencoba membuat scheduler di oracle. Oracle mempunya paket namanya DBMS_JOB, DBMS_JOB mulai dikenalkan sejak Oracle versi 7.3.4.

Untuk membuat JOb dioracle kita bisa menggunakan procedure 

DBMS_JOB.SUBMIT(
  JOB            OUT BINARY_INTEGER,
  WHAT         IN  VARCHAR2,
  NEXT_DATE IN  DATE           DEFAULT SYSDATE,
  INTERVAL    IN  VARCHAR2       DEFAULT 'NULL',
  NO_PARSE   IN  BOOLEAN        DEFAULT FALSE,
  INSTANCE   IN  BINARY_INTEGER DEFAULT 0,
  FORCE       IN  BOOLEAN        DEFAULT FALSE);

Sebagai contoh kita akan melakukan update ke table TMP_JOB

Membuat Procedure update 

CREATE OR REPLACE PROCEDURE TMPUPD
IS
BEGIN
   UPDATE TMP_JOB SET KOL1=2;
   COMMIT;
END;

Menjalankan procedure TMPUPD setiap malam jam 00

DECLARE
v_jobId NUMBER(9);
BEGIN
   DBMS_JOB.SUBMIT (
    JOB                => v_jobId,
    WHAT             => 'TMPUPD;',
    NEXT_DATE     => TRUNC(SYSDATE) + 1,
    INTERVAL        => 'TRUNC(SYSDATE)+1'
   );

   DBMS_OUTPUT.PUT_LINE ('Job Created with Id : '||v_jobId);
END;

Menjalankan procedure TMPUPD setiap jam dimulai sekarang

DECLARE
v_jobId NUMBER(9);
BEGIN
   DBMS_JOB.SUBMIT (
    JOB                => v_jobId,
    WHAT             => 'TMPUPD;',
    NEXT_DATE     => SYSDATE,
    INTERVAL        => 'SYSDATE+(1/24)'
   );

   DBMS_OUTPUT.PUT_LINE ('Job Created with Id : '||v_jobId);
END;

Send Email using PL/SQL

0
373 days
by in Oracle

Email merupakan service yang sering kita gunakan, terkadang kita membutuhkan notifikasi email jika ada sesuatu yang berkaitan dengan kita, Nah sekarang kita mencoba mengirim Email dari PL/SQL berikut langkah-langkah yang perlu dilakukan.
1. Prasyarat
Pertama didatabase oracle kita harus sudah terinstall paket UTL_SMTP.paket ini mulai dikenalkan pada oracle versi 8.1.7, jika belum ada anda dapet menginstall dari source nya yang berada pada direktori {ORACLE_HOME}/rdbms/admin/utlsmtp.sql.
Anda harus mempunyai pengetahuan tentang protokol SMTP.

2. Membuat program PL/SQL untuk Konek ke SMTP
Berikut adalah contoh script PL/SQL untuk membangun koneksi ke server SMTP

CREATE OR REPLACE FUNCTION SMTP_CONNECT (
   pIn_smtp_host IN VARCHAR2,
   pIn_smtp_port IN INTEGER,
   pIn_domain     IN VARCHAR2,
   pIn_username  IN VARCHAR2,
   pIn_password  IN VARCHAR2)
RETURN UTL_SMTP.CONNECTION
AS
v_conn UTL_SMTP.CONNECTION;
BEGIN
   v_conn := UTL_SMTP.OPEN_CONNECTION (pIn_smtp_host,pIn_smtp_port);
   UTL_SMTP.HELO(v_conn,pIn_domain);
   UTL_SMTP.command (v_conn, 'AUTH LOGIN');
   UTL_SMTP.command (v_conn, pIn_username);
   UTL_SMTP.command (v_conn, pIn_password);

RETURN v_conn

EXCEPTION WHEN OTHERS
RETURN NULL;

END SMTP_CONNECT;

pIn_username dan pIn_password merupakan account email dan password yang sudah di base64 encoding, untuk lebih mudahnya anda bisa melakukan encode di level oraclenya. Fungsi untuk membangun koneksi ke smtp sudah berhasil kita buat selanjutnya kita buat fungsi untuk memgirim pesan.

3. Membuat fungsi untuk send Message

Mengirim pesan di smtp cukup simple berikut sample PL/SQL untuk send message 

CREATE OR REPLACE PROCEDURE SMTP_SEND (
   pIn_conn      IN  OUT NOCOPY  UTL_SMTP.CONNECTION,
   pIn_sender    IN VARCHAR2,
   pIn_recipient IN VARCHAR2,
   pIn_subject   IN VARCHAR2,
   pIn_message IN VARCHAR2)
AS
BEGIN
   UTL_SMTP.MAIL (pIn_conn,pIn_sender);
   UTL_SMTP.RCPT(pIn_conn,pIn_recipient);
   UTL_SMTP.OPEN_DATA (pIn_conn);
   UTL_SMTP.WRITE_DATA (pIn_conn, 'From: '||pIn_sender  || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (pIn_conn, 'To: '||pIn_recipient  || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (pIn_conn, 'Subject: '||pIn_subject  || UTL_TCP.CRLF);

      -- Set priority:
      --   High      Normal       Low
      --   1     2     3     4     5
   UTL_SMTP.WRITE_DATA (pIn_conn, 'X-Priority: 1'  || UTL_TCP.CRLF);
   UTL_SMTP.WRITE_DATA (pIn_conn, pIn_message);
END SMTP_SEND;

prosedur untuk mengirim data sudah kita lakukan, berikutnya adlah fungsi untuk close connection

4. CLosed Connection

sekarang kita buat fungsi untuk menutup koneksi SMTP 

CREATE OR REPLACE PROCEDURE SMTP_CLOSED (
  pIn_conn    IN OUT NOCOPY UTL_SMTP.CONNECTION
)
AS
BEGIN
  UTL_SMTP.CLOSE_DATA(pIn_conn);
  UTL_SMTP.QUIT (pIn_conn);
END SMTP_CLOSED;

jika semua fungsi dan prosedur diatas sudah selesai dicompile saatnya kita coba buat script untuk menguji mengirim email 

DECLARE
v_conn UTL_SMTP.CONNECTION;
BEGIN
  v_conn := SMTP_CONECT(
   pIn_smtp_host => 'smtp.nailuvar.com',
   pIn_smtp_port => 25,
   pIn_domain     => 'nailuvar.com',
   pIn_username  => 'xxxxxxx=',
   pIn_password  => 'GDAJAKDY='); 

-- silahkan diganti dengan data di server anda
  SMTP_SEND (
    pIn_conn       => v_conn,
    pIn_sender    => 'massri@nailuvar.com',
    pIn_recipient => 'massri@gmail.com',
    pIn_subject  => 'Test Send Mail',
    pIn_message => 'Ini pesannya silahkan di baca ... '
  );

   SMTP_CLOSED (pIn_conn ==> v_conn);

END;

selesai selamat mencoba ….

Write Query result to File

0
376 days
by in Oracle

Jika kita ingin menyimpan hasil suatu query ke file dalam format csv salah satu cara yang biasa dilakukan adalah menggunakan PL/SQL (UTL_FILE). Beikut saya tuliskan contoh sederhana untuk menampilkan data EMPLOYEES dari Schema HR ke format csv.

1. Membuat Directory virtual di Oracle

mulai oracle versi 9i untuk bisa mengakses direktori OS di oracle kita harus membuat direktori itu yang berrelasi dengan direktori OS, sebagai contoh untuk penyimpanan file kita akan tempatkan di direktori OS /home/oracle/utl_file_dir

CREATE DIRECTORY UTL_FILE_DIR AS '/home/oracle/utl_file_dir'

Perintah ini akan membuat Directory di Oracle dengan nama UTL_FILE_DIR. Nama ini tidak harus sama dengan nama direktori di OS.

untuk mengecek bisa melakukan query ke ALL_OBJECTS

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'DIRECTORY'

2. Memberikan Hak Akses Direktory ke User

GRANT READ ON DIRECTORY UTL_FILE_DIR TO HR
GRANT WRITE ON DIRECTORY UTL_FILE_DIR TO HR

3. Membuat Procedure untuk menuliskan data ke file 


CREATE OR REPLACE PROCEDURE SAVE_EMP

AS

v_handle  UTL_FILE.FILE_TYPE;

v_dirname VARCHAR2(40) := 'UTL_FILE_DIR';

v_filename VARCHAR2(40) := 'emp.csv';

v_data      VARCHAR2(255);

CURSOR C1 IS SELECT first_name, last_name from EMPLOYEES;

BEGIN

v_handle := UTL_FILE.FILE_OPEN(v_dirname,v_filename,'w');

OPEN C1;

LOOP

FETCH C1 into v1,v2;

EXIT WHEN C1%NOTFOUND;

v_data := v1||','||v2;

UTL_FILE.PUT_LINE(v_handle,v_data);

END LOOP;

UTL_FILE.FCLOSE;

EXCEPTION

WHEN OTHERS THEN

UTL_FILE.FCLOSE_ALL;

END SAVE_EMP;

selamat mencoba … :D

Call Store Procedure dari Java

0
444 days
by in Glassfish, Java, Oracle

Sebagai contoh saya mempunyai package oracle seperti dibawah ini
Secara umum format pemanggilan procedure di java dapat dilihat pada template coding dibawah ini : 

CallableStatement cs;
try {
    Memanggil prosedur tanpa parameter
    cs = connection.prepareCall("{call myproc}");
    cs.execute();

  // Pemanggilan procedure dengan nama myprocin dengan satu parameter
    cs = connection.prepareCall("{call myprocin(?)}");

    // Set nilai parameter
    cs.setString(1, "a string");

    // Execute the stored procedure
    cs.execute();

  // Call a procedure with one OUT parameter
    cs = connection.prepareCall("{call myprocout(?)}");

    // Register the type of the OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);

    // Execute the stored procedure and retrieve the OUT value
    cs.execute();
    String outParam = cs.getString(1);     // OUT parameter

  // Call a procedure with one IN/OUT parameter
    cs = connection.prepareCall("{call myprocinout(?)}");

    // Register the type of the IN/OUT parameter
    cs.registerOutParameter(1, Types.VARCHAR);

    // Set the value for the IN/OUT parameter
    cs.setString(1, "a string");

    // Execute the stored procedure and retrieve the IN/OUT value
    cs.execute();
    outParam = cs.getString(1);            // OUT parameter
} catch (SQLException e) {
}

berikutnya kita coba dengan sample menmanggil oracle procedure.

« Previous
Next »
Go to Top