Selamat Tahun Baru 2009

Dua hari lagi, tahun 2008 akan kita tinggalkan, dan kita akan masuk ke tahun 2009. Begitu panjang rasanya hari-hari yang sudah dilewati, begitu terasa kelelahan yang kita rasakan. Tapi semua itu tidak akanberarti jika kita berhenti sampai disini.

Ya tahun depan akan kita songsong dgn semangat baru yang lebih optimis, berusaha dengan kreatif, dan percaya bahwa "when one door closed another one is open."


Selamat Tahun Baru 2009

Analytical Function w/ rowid

Pelajaran hari ini:
Jangan pernah menggunakan dan mengkombinasikan Single Row Character Functions spt pad, rpad, dll dengan analytical function spt: rank().., dense_rank()..
Hasil query-nya tidak akan benar.

Perhatikan query berikut ini:

Select y.Rowid As rid,
Y.UO || Y.KOTAMA || Y.SATPOR || '0000' ||
Lpad(
Rank()
Over (Partition By Y.UO, Y.KOTAMA, Y.SATPOR
Order By noreg, y.Rowid),
3,
'0'
)
As no_reg,
..
..


Tapi sebaiknya menjadi:

Select y.Rowid As rid,
y.uo ||
y.kotama ||
y.satpor ||
'0000' ||
Lpad('0', 3-length(Rank() Over (Partition By y.uo, y.kotama, y.satpor Order By Rowid)), '0') ||
Rank() Over (Partition By y.uo, y.kotama, y.satpor Order By Rowid)
As no_reg,
..
..


Bontortp-19Desember2008

Test

bontortp-17desember2008

Dalam menginsert data ke tabel, dari segi kecepatan:

Skala prioritas SQL,PLSQL, atau Java...

bontor_20081210

on update cascade

Saya ingin memodifikasi constraint referencial key suatu tabel. Dimana tiap kali ada perubahan terhadap tabel referensi (induknya) otomatis tabel tersebut (tabel anak) juga akanmengikuti perubahan.
Misalnya dengan skrip di bawah ini:
ALTER TABLE SISN.ALIAS_KOLOM modify (
CONSTRAINT PK_ALIAS_KOLOM
PRIMARY KEY
(NAMA_TABEL, NAMA_KOLOM) on update cascade;
ORA-00907: missing right parenthesis

Mengatur Auto Open di Toad

Mengatur Auto Open di Toad
Untuk mengatur Auto Open di TOAD

1. Buka Vew>Toad Options


2. Pilih Tab [Windows], di panel "Behavior" sebelah kanan ada beberapa kolom. Yang perl diperhatikan dalam hal ini adalah kolom Window dan Auto Open
3. Cocokkkan kolom Window dan kolom Auto Open, centang fitur apa yang akan di buka secara otomatis
4. Tekan tombol "Apply" lalu tombol "OK"

Dengan penyetelan seperti ini, setiap Anda buka sesi baru, maka jendela-jendela tersebut akan otomatis terbuka.

PS: Telah dicoba di TOAD Versi 9725

Bulk Demonstration

Herewith a procedure demonstrating bulk collection

   1:  CREATE OR REPLACE PROCEDURE bulk_demonstration

   2:  IS

   3:    -- The maximum rows collected in the bulk collect operation

   4:    c_maxrows                       CONSTANT PLS_INTEGER := 5;

   5:   

   6:    -- Subtypes based on the columns in the cursor

   7:    SUBTYPE empno_t IS emp.empno%TYPE;

   8:    SUBTYPE ename_t IS emp.ename%TYPE;

   9:    SUBTYPE job_t IS emp.job%TYPE;

  10:    SUBTYPE mgr_t IS emp.mgr%TYPE;

  11:    SUBTYPE hiredate_t IS emp.hiredate%TYPE;

  12:    SUBTYPE sal_t IS emp.sal%TYPE;

  13:    SUBTYPE comm_t IS emp.comm%TYPE;

  14:    SUBTYPE deptno_t IS emp.deptno%TYPE;

  15:   

  16:    -- Column Collections for every column in the cursor

  17:    TYPE empno_cc IS TABLE OF empno_t       INDEX BY BINARY_INTEGER;

  18:    TYPE ename_cc IS TABLE OF ename_t       INDEX BY BINARY_INTEGER;

  19:    TYPE job_cc IS TABLE OF job_t           INDEX BY BINARY_INTEGER;

  20:    TYPE mgr_cc IS TABLE OF mgr_t           INDEX BY BINARY_INTEGER;

  21:    TYPE hiredate_cc IS TABLE OF hiredate_t INDEX BY BINARY_INTEGER;

  22:    TYPE sal_cc IS TABLE OF sal_t           INDEX BY BINARY_INTEGER;

  23:    TYPE comm_cc IS TABLE OF comm_t         INDEX BY BINARY_INTEGER;

  24:    TYPE deptno_cc IS TABLE OF deptno_t     INDEX BY BINARY_INTEGER;

  25:   

  26:    -- The cursor used

  27:    CURSOR emp_cur

  28:    IS

  29:      SELECT emp.empno

  30:           , emp.ename

  31:           , emp.job

  32:           , emp.mgr

  33:           , emp.hiredate

  34:           , emp.sal

  35:           , emp.comm

  36:           , emp.deptno

  37:        FROM emp;

  38:   

  39:    -- Local variables to 'catch' the results from the cursor

  40:    l_empno                                  empno_cc;

  41:    l_ename                                  ename_cc;

  42:    l_job                                    job_cc;

  43:    l_mgr                                    mgr_cc;

  44:    l_hiredate                               hiredate_cc;

  45:    l_sal                                    sal_cc;

  46:    l_comm                                   comm_cc;

  47:    l_deptno                                 deptno_cc;

  48:    -- local function to do something with the data

  49:    function raisesal(sal_in sal_t) return sal_t

  50:    is

  51:      l_returnvalue sal_t;

  52:    begin

  53:      l_returnvalue := sal_in * 1.1;

  54:      RETURN l_returnvalue;

  55:    end raisesal;

  56:      

  57:  BEGIN

  58:    -- open the cursor

  59:    OPEN emp_cur;

  60:    -- start a simple loop

  61:    LOOP

  62:      -- clear out the collections

  63:      l_empno.delete;

  64:      l_ename.delete;

  65:      l_job.delete;

  66:      l_mgr.delete;

  67:      l_hiredate.delete;

  68:      l_sal.delete;

  69:      l_comm.delete;

  70:      l_deptno.delete;

  71:      -- fetch from the cursor using bulk collect for the result, but limited to limit

  72:      FETCH emp_cur BULK COLLECT INTO l_empno

  73:                                    , l_ename

  74:                                    , l_job

  75:                                    , l_mgr

  76:                                    , l_hiredate

  77:                                    , l_sal

  78:                                    , l_comm

  79:                                    , l_deptno

  80:      LIMIT c_maxrows;

  81:      -- if we fetched any data

  82:      IF l_empno.count > 0 THEN

  83:        -- do some complicated stuff

  84:        FOR idx IN l_empno.first..l_empno.last LOOP

  85:  --        l_sal(idx) := l_sal(idx) * 1.1;

  86:          l_sal(idx) := raisesal(l_sal(idx));

  87:        END LOOP;

  88:        -- update the records in the database, using a bulk operation

  89:        FORALL idx IN l_empno.first..l_empno.last

  90:          UPDATE emp

  91:             SET sal = l_sal(idx)

  92:           WHERE empno = l_empno(idx);

  93:      END IF;

  94:      -- exit when we didn't fetch our maximum rows

  95:      EXIT WHEN l_empno.count < c_maxrows;

  96:    END LOOP;

  97:  END bulk_demonstration;

Then a small script to see if it works

   1:  CLEAR screen

   2:  SET serveroutput on

   3:  SELECT emp.empno

   4:       , emp.sal

   5:    FROM emp;

   6:  EXEC bulk_demonstration;

   7:  SELECT emp.empno

   8:       , emp.sal

   9:    FROM emp;

Reusing Primary Key

I's working in development project. I was doing reuse, rename, drop, and create some tables a lot. And I like creating constraints manually with specific naming that fits me.

SQL> Alter Table YA_JUDUL_TEMA Rename Constraint PK_JUDUL_TABEL To PK_JUDUL_TEMA_DIsabled;

Elapsed: 00:00:00.04
SQL> alter table judul_tabel add constraint PK_judul_tabel primary key (id_judul);
alter table judul_tabel add constraint PK_judul_tabel primary key (id_judul)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Elapsed: 00:00:00.03

What object??
While renaming table for backing up and creating new one, I want to reuse the same constaints, say primary key. As a matter of fact that everythime I create primary key, Oracle automatically create index with the same name as constraints.

So, if I want to reuse that primary key name for the new table I need to rename to other, like wise the index must be rename axpliciltly.

Here are the script:


SQL> select * from user_indexes where table_name='YA_JUDUL_TEMA';

INDEX_NAME |INDEX_TYPE |TABLE_OWNER |TABLE_NAME |TABLE_TYPE
---------------|------------|------------|----------------|-----------
PK_JUDUL_TABEL |NORMAL |SISN |YA_JUDUL_TEMA |TABLE

SQL> alter index PK_JUDUL_TABEL rename to PK_JUDUL_Tema_disabled;

Index altered.


Bare Conslusion.

1. Oracle unallowed user creating objects with the same name


2. Index is object

Anonymous Case on Modifying Column

Kasus Anonim...
Sintaks untuk mengalter table yang merubah tipe data kolom adalah:


ALTER TABLE [nama_tabel] MODIFY [nama_kolom][tipe_data];

Saya baca di manualnya bahwa syarat untuk memodifikasi tipe data pada kolom, maka kolom harus dalam keadaan kosong. Nah, saya coba buktikan, apakah benar ketetaan seperti itu, dengan mengeksekusi perintah di bawah ini:



SQL> alter table ya_judul_tabel_20081201 modify id_judul integer;
alter table ya_judul_tabel_20081201 modify id_judul integer
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype


Tapi hal ini tidak berlaku pada kasus ini, ketika saya mengalter kolom tersebut ke tipe lain (misal tipe nvarchar2), tabel teralter dangan sukses. Seperti pada skrip berikut ini:


SQL> alter table ya_judul_tabel_20081201 modify id_judul nvarchar2(5);

Table altered.


Saya masih mencari referensi dan pustaka lain untuk menjelaskan fenomena ini.