Archive

Posts Tagged ‘database’

TRIM pada SQL Server

January 9, 2012 1 comment
SQL-Server

SQL-Server

Trim adalah fungsi standar yang biasa digunakan oleh bahasa pemrograman untuk menghilangkan karakter spasi dalam sebuah string. Misalnya ‘     Hello world    ‘ dengan fungsi trim(‘      Hello world    ‘) akan menjadi ‘Hello world’.

Pada SQL Server, fungsi trim tidak disediakan secara default. Kita perlu melakukan trick tertentu untuk menghilangkan spasi. Caranya menggunakan LTRIM dan RTRIM bersama-sama.

Berikut contoh query untuk menghilangkan spasi di SQL Server :

select LTRIM(RTRIM(' Hello world '))

Grant privilege seluruh object schema di oracle

January 5, 2012 Leave a comment
oracle

oracle

Di oracle, setiap user/schema mempunya objek sendiri. Objek dapat berupa table, view, procedure, function atau package. Secara default user tidak dapat mengakses objek milik user2 yang lain, kecuali setelah owner objek memberikan hak akses pada user tersebut.

Pemberian hak akses terhadap objek schema di oracle dilakukan per objek. Jika seorang schema memiliki 100 objek, kita harus menjalankan script 100 kali untuk memberikan seluruh objek. Tetapi ada trik yang dapat kita lakukan untuk mengatasi hal itu.

Untuk memberikan hak akses seluruh objek yang dimiliki suatu schema, lakukan langkah-langkah berikut :

  1. login sebagai user owner
  2. jalankan script berikut :

    begin
    for i in (select object_name from user_objects where object_type in ('TABLE','VIEW'))
    LOOP
    execute immediate 'grant select on '|| i.object_name||' to bb';
    end loop;
    end;
    /

From Varchar to CLOB @Oracle

October 20, 2008 7 comments

Sekedar info… di oracle tidak ada type field text. untuk menyimpan data alfanumeris biasanya menggunakan tipe data varchar2. tetapi varchar2 memiliki keterbatasan yaitu jumlah karakter yang tidak boleh > 4000 karakter. Lalu bagaimana agar bisa menyimpan data yang terdiri dari > 4000 karakter ???. caranya dengan menggunakan tipe data CLOB.

Tetapi hal diatas menjadi agak rumit ketika column yang akan kita alter dari varchar2 ke CLOB telah memiliki data. Sedangkan di oracle kita tidak bisa langsung mengganti tipe data varchar2 ke CLOB seperti sintaks berikut :

ALTER TABLE t MODIFY name CLOB

*

ERROR at line 1:

ORA-22858: invalid alteration of datatype

ada beberapa langkah yang perlu dilakukan untuk mengalter column dari varchar2 ke CLOB, antara lain :

  1. tambahkan kolom baru dengan tipe CLOB
  2. update data pada kolom baru dengan isi dari kolom lama
  3. drop kolom yang bertipe varchar2
  4. rename kolom baru dengan nama kolom yang di drop.

langkah-langkah diatas dapat dibuat ke bentuk skrip berikut :

SQL>ALTER TABLE <nama_tabel> ADD tmp_name CLOB;
Table altered.

SQL>UPDATE <nama_tabel> SET tmp_name=<nama_kolom>;
2 rows updated.

SQL>ALTER TABLE <nama_tabel> DROP COLUMN <nama_kolom>;
Table altered.

SQL>ALTER TABLE <nama_tabel> RENAME COLUMN tmp_name to <nama_kolom>;
Table altered.

SQL>desc <nama_tabel>;

ganti <nama_tabel> dan <nama_kolom> pada script diatas dengan nama tabel dan nama kolom pada database anda.

Tags: , ,

Group_Concat di Oracle

September 9, 2008 9 comments

fungsi group_concat (seringnya mysql c) biasanya digunakan untuk menggabungkan row hasil query dengan menggunakan format tertentu (di mysql). kalau anda ingin mengetahui lebih lanjut tentang penggunaan group concat ini, silahkan baca manual mysql.

lalu bagaimana group_concat di oracle ???. pertanyaan ini muncul ketika saya mau tidak mau harus menerapkan group_concat, sedangkan di oracle sendiri tidak ada fungsi bawaan group_concat seperti di mysql. setelah menimba ilmu dengan melakukan searching di uncle google, akhirnya ketemu juga bagaimana cara menerapkan group_concat di oracle.

untuk itu kita perlu membuat type dan function di oracle yang nantinya akan berfungsi layaknya group_concat di mysql.

berikut script yang perlu dituliskan :

create or replace type string_agg_type as object (
total varchar2(4000),

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
return number,

member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
return number,

member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
return number
is
begin
self.total := self.total || ‘|’ || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
return number
is
begin
returnValue := ltrim(self.total,’|’);
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/

create or replace function group_concat(input varchar2)
return varchar2
parallel_enable aggregate using string_agg_type;
/
setelah script diatas di compile, kita bisa menggunakannya dalam query. fungsi diatas akan membuat group concat dengan separato pipeline ‘|’. yang menghasilkan penggabungan array record dalam bentuk string dengan separator pipeline. bacaan lebih lanjut, ada disini  http://kb.yarmakconsulting.com/2008/06/oracle-analog-of-mysql-groupconcat.html

dump/restore di Oracle

September 5, 2008 1 comment

setelah bergelut sekian lama dengan oracle dan pusing memikirkan bagaimana mekanisme dump dan restore db di oracle layaknya pada mysql(mysqldump …. vs mysql …. ) akhirnya ketemu sudah solusi untuk dump/restore di oracle.

untuk dump di oracle menggunakan istilah export (EXP) yang binarynya bisa kita dapatka di $ORACLE_HOME/bin/exp dengan sintaks sebagai berikut :

exp oracle_user/oracle_password@sid file=nama_file

perintah diatas akan mengeksport data dari sid yang telah didefinisikan yang secara default mengarah ke oracle instance yang ada di local. lalu muncul pertanyaan, bagaimana melakukan dump/export dari oracle yang ada di server pada jaringan ???. caranya dengan mendefinisikan local naming parameter untuk sid mesin oracle server pada file tnsnames.ora. file ini secara fisik ada di $ORACLE_HOME/network/admin/tnsnames.ora.

format penulisan sid sebagai berikut :

net_service_name=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))

untuk contoh, bisa dilihat pada script berikut :

CHOIRUL_XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.22)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

nah untuk melakukan dump/exp tinggal jalankan exp dengan sid yang telah didefinisikan. untuk sample diatas, berikut script exportnya :

exp oracle_user/oracle_password@CHOIRUL_XE file=/home/choirul/oracle_dump.dmp

lebih jauhnya tentang local naming parameter, bisa dibaca di http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96581/tnsnames.htm#496765.

untuk merestore/import database oracle kita bisa menggunakan imp yang ada di $ORACLE_HOME/bin/imp dengan sintak sebagai berikut :

imp oracle_user/oracle_password@sid file=nama_file

hal-hal laiin yang terkait dengan restore/import memiliki mekanisme teknis yang sama dengan dump/export.

nb (nambah bicara) :

dump/restore di oracle tidak bisa dilakukan oleh dua user yang berbeda. hal ini karena ketika melakukan dump, file .dmp menyimpan informasi user yang melakukan dump, sehingga hanya user yang sama yang dapat melakukan restore dari file yang di dump tersebut. sebagai contoh misalnya, user rootoracle melakukan dump dan menyimpannya pada file tertentu. file tersebut hanya akan bisa direstore oleh user rootoracle dan tidak user yang lain.

ada teknik khusus agar dump/restore bisa dilakukan oleh user yang berbeda, dengan memanfaatkan klausa FROMUSER dan TOUSER. berikut sintaks untuk melakukan dump dengan multiple user :

exp \’sys/sys_password as SYSDBA\’ file=/home/choirul/oracle_dump.dmp OWNER=oracle_user

sedangkan sintaks berikut digunakan untuk melakukan restore dari file hasil dump diatas :

imp \’sys/sys_password as SYSDBA\’ file=/home/choirul/oracle_dump.dmp FROMUSER=oracle_user TOUSER=oracle_user_new

info lebih lanjut silahkan buka alamat ini http://forums.oracle.com/forums/thread.jspa?threadID=380132

Tags: ,

Limit di Oracle

August 12, 2008 Leave a comment

Seringkali ketika kita membuat suatu aplikasi, memerlukan adanya paging pada aplikasi tersebut. Di Mysql untuk menerapkan paging memerlukan klausa order by dan limit, misalnya seperti script berikut :

SELECT * FROM <nama_tabel> ORDER BY <ID> LIMIT 0, 20;

script diatas digunakan untuk mengambil data sebanyak 20 record dari record pertama. tetapi sebagaimana diketahui secara umum, tidak semua klausa yang disupport suatu dbms juga disupport oleh dbms yang lain. (dalam hal ini limit merupakan klausa yang ada dimysql).

pertanyaannya yang muncul kemudian bagaimana menerapkan klausa limit di oracle. Karena oracle tidak support klausa limit seperti mysql.

untuk melakukan query limit pada oracle kita perlu menggunakan ROWNUM. ROWNUM merupakan artificial field yang ada pada setiap query select untuk automatic numbering pada setiap record yang dihasilkan. berikut contoh query untuk menerapkan limit 0 sampai 10:

SELECT * FROM(
SELECT
UserName,
ROWNUM AS ID
FROM
gtfw_user
ORDER BY
UserName
) WHERE ID BETWEEN 0 AND  10

berikut contoh hasil dari query diatas :

USERNAME                                                     ID
—————————————————————————————————-
admin                                                          2
adminGT                                                       3
admuser                                                       5
csuser                                                          4
nobody                                                          1

Tags: ,
%d bloggers like this: