Posts tagged Oracle

EXECUTE IMMEDIATE

0
711 days
by in Oracle

Execute immediate merupakan pengganti dari DBMS_SQL, jika kita sebelumnya menggunakan DBMS_SQL cukup merepotkan sekarang anda tidak perlu kawatir lagi. Yang perlu diingat perintah execute immediate tidak otomatis commit untuk perintah-perintah DML, jadi kita harus melakukan commit atau rollback manual. Sql yang dieksekusi tidak perlu diakhiri dengan tanda titik koma (;).

Berikut beberapa contoh penggunaan execute immediate.

Menggunakan perintah DDL

begin
execute immediate 'create table belajar(t1 number(3))';
end;

SQL> select * from belajar;

T1
---

Menggunakan Variable dalam dinamik SQL

Contoh berikut adalah insert data ketable belajar.

declare
v_t1 number(3); -- deklarasi variable v_t1
begin

v_t1 := 123; -- assignment varibale v_t1 dengna nilai 123

execute immediate 'insert into belajar(t1) values (:t1) ' using v_t1;
commit;

select * from belajar;

end;

SQL> select * from belajar;

T1
----
123

“:t1″ adalah variable yang akan digantikan oleh v_t1, binding yang digunakan adalah “:”

Mengembalikan Nilai

Untuk mendapatkan nilai dari execute immediate kita menggunakan keyword INTO

declare
v_t1 number(3); -- deklarasi variable
begin

execute immediate 'select t1 + 5 from belajar'  into v_t1;

dbms_output.put_line ('Hasil : '||v_t1);

Hasil : 128

end;

jika select membutuhkan kondisi, misal dengan tambahan filter t1=x query menjadi execute ‘select t1 + 5 from belajar where t1=:x’ into v_t1 using x ;

Menjalankan Package/Store Procedure / Function

Contoh procedure yang digunakan adalah

declare
  v_str varchar2(20);
  v_serviceName varchar2(40) : 'MyPackage.printHello';
begin
  v_str := 'Selamat Datang di Nailuvar Education Center';
  execute immediate 'BEGIN '|| v_serviceName || ' (:a,:b); END;' using in v_str , out v_status;

 dbms_output.put_line ('Status = '||v_status);
end;

Menyimpan hasil dalam bentuk Record PL/SQL
contoh ini akan menampilkan data employee pada schema HR, data yang akan ditampilkan adalah employee_id dan nama.

declare
-- deklarasi type record  dengan nama RData
TYPE RData IS RECORD (
 emp_id number(9),
 emp_name varchar2(40)
);

--deklarasi variable v_data dengan type RData.

begin
  EXECUTE IMMEDIATE 'select employee_id,first_name || last_name from employee where employee_id = 1234 ' into v_data;
end;

demikian beberapa contoh penggunaan execute immediate yang membuat pemrosesan query dinamis menjadi lebih simple jika dibandingkan menggunakan DBMS_SQL. Yang harus diperhatikan adalah penanganan error, setiap kemungkinan error harus ditangani.

Selamat Mencoba.

ORA-12540: TNS:internal limit restriction exceeded

0
720 days
by in Error Description

Deskripsi

Error ini ditemukan ketika ada pekerjaan untuk membuat skema baru di oracle. setelah mencoba konek ke databse menemukan error.

Command :
sqlplus / as sysdba

Error

ORA-12540: TNS:internal limit restriction exceeded

Penyebab

Terlalu banyak koneksi didalam database

Solusi

Kemungkinan solusi yang bisa dilakukan
Menunggu sehingga tersedia link koneksi
Menambah Memori atau seting SGA

Oracle External Tables

0
731 days
by in Oracle

External Table pada oracle memungkinkan kita melakukan query pada flat file untuk oracle versi 9i dan melakukan manipulasi pada oracle 10g.
Oracle external table menggabungkan fungsionalitas SQL Loader dan data pump, sebagai contoh kita mempunyai data pelanggan dalam bentuk csv dan kita ingin melakukan query pada data tersebut kita bisa dengan mudah melakukannya menggunakan external tables.

Membuat External Tables

File yang akan diakses harus bisa dibaca oleh Oracle process maka langkah pertama adalah membuat directory dan memberikan akses pada direktori tersebut.

$cd /opt/data
$mkdir customer
$ls -l /opt/data
drwx------  2 oracle oinstall 4096 Mar  1 17:05 customer

simpan file customer.csv kedalam direktori /opt/data/customer

contoh content dari data customer.csv adalah sebagai berikut

1,massri,massri@nailuvar.com,bandung
2,nindri,nindri@nailuvar.com,bojongsoang

Langkah berikutnya adalah membuat direktori tersebut di oracle dengan memberikan hak read dan write 

SQL> connect / as sysdba
Enter password:
Connected.
SQL> create or replace directory cust_data_dir
  2  as '/opt/data/customer';

Directory created.

SQL> grant read,write on directory cust_data_dir to hr;

Grant succeeded.

langkah terakhir adalah membuat external table untuk data diatas 

SQL> connect hr
Enter password:
Connected.
SQL> create table customer_ext
  2      ( empl_id varchar2(3),
  3        name varchar2(50),
  4        email varchar2(100),
  5        city varchar2(40)
  6      )
  7      organization external
  8      ( default directory cust_data_dir
  9        access parameters
 10        ( records delimited by newline
 11          fields terminated by ','
 12        )
 13        location ('customer.csv')
 14    );

Table created.

Nah sekarang kita sudah mempunya table dengan nama customer_ext dan kita bisa melakukan operasi SQL select untuk menambilkan data tersebut seperti normal table.

SQL> select * from customer_ext;

EMP NAME       EMAIL                         CITY
--- ---------- ----------------------- ---------
   1 massri       massri@nailuvar.com      bandung
   2 nindri        nindri@nailuvar.com       bojongsoang

2 rows selected.

Dynamic Variable Binding in Dynamic SQL

0
771 days
by in Oracle

Untuk membuat query dinamis dengan kondisi yang dinamis juga sesuai dengan pilihan user cukup sering ditemui pada query untuk menampilkan report. Sebagai contoh saya ingin membuat laporan untuk data transaksi yang bisa dilakukan filter tanggal transaksi , petugas yang melakukan transaksi, jenis transaksi, lokasi transaksi dan filter lainnya yang mungkin untuk dilakukan. Untuk membuat query dinamis bisa menggunakan EXECUTE IMMEDIATE

Cara Pertama

Cara ini yang paling mudah dengan tanpa menggunakan binding. 

   Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);

  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE('||pIn_startDate||',''DD/MM/YYYY'')';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE('||pIn_endDate||',''DD/MM/YYYY'')';
        END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = '|| pIn_locationId;
        END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = '|| pIn_userId;
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql;

  END ViewReport;

Cara pertama ini memiliki kekurangan karena oracle harus melakukan hard parser terhadap setiap query yang dikirimkan ke server. Setiap kombinasi filter yang berbeda akan dianggap sql yang berbeda oleh oracle.

Cara Kedua
Cara kedua ini menggunakan binding variable seperti contoh dibawah ini 

  Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);

  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE(:pIn_startDate,''DD/MM/YYYY'')';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE(:pIn_endDate,''DD/MM/YYYY'')';
        END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = :pIn_locationId';
        END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = :pIn_userId';
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql using pIn_startDate, pIn_endDate, pIn_locationId, pIn_userId;

  END ViewReport;

Procedure diatas akan error jika salah satu filter tidak dipilih, solusinya adalah kita harus memodifikasi procedure sehingga binding yang menjadi tepat. Solusi pertama adalah membuat kondisi pada eksekusi sql sehingga jumlah variable yang di binding dengan nilai penggantinya bersesuaian.

   Procedure ViewReport (
     pIn_startDate IN VARCHAR2,
     pIn_endDate   IN VARCHAR2,
     pIn_locationId IN NUMBER,
     pIn_userId      IN NUMBER,
     pOut_data      OUT TCursor -- Oracle Ref Cursor
   ) AS
    v_sql   VARCHAR2(2048);
    v_where VARCHAR2 (512);
    v_startDate  VARCHAR2(30) := pIn_startDate;
    v_endDate    VARCHAR2(30) := pIn_endDate;
    v_locationId  NUMBER(9) := pIn_locationId;
    v_userId      NUMBER(9) := pIn_userId;
  BEGIN
        IF pIn_startDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS >= TO_DATE(:pIn_startDate,''DD/MM/YYYY'')';
        ELSE
             v_where := v_where || '  AND ''1'' = :pIn_startDate  ';
             v_startDate := '1';
        END IF;

        IF pIn_endDate IS NOT NULL
        THEN
            v_where := v_where || '  AND TGL_TRANS <= TO_DATE(:pIn_endDate,''DD/MM/YYYY'')';
       ELSE
             v_where := v_where || '  AND ''1'' = :pIn_endDate ';
             v_endDate := '1';
       END IF;

       IF pIn_locationId > 0
       THEN
          v_where := v_where || ' AND LOC_ID = :pIn_locationId';
       ELSE
           v_where := v_where || ' AND 1 = :pIn_locationId';
           v_locationId := 1;
       END IF;

      IF pIn_userId > 0
      THEN
          v_where := v_where || ' AND USER_ID = :pIn_userId';
       ELSE
          v_where := v_where || ' AND 1 = :pIn_userId';
          v_userId := 1;
      END IF; 

        v_sql := 'SELECT * FROM DATA_TRANSAKSI WHERE 1 = 1 '|| v_where;

        OPEN pOut_data FOR v_sql using v_startDate, v_endDate, v_locationId, v_userId;

  END ViewReport;

Dengan procedure diatas kita bisa membuat dinamik sql dan dinamik varibale binding untuk kebutuhan pelaporan data.

« Previous
Next »
Go to Top