Oracle, Web, Script, SQLserver, Tips & Trick

September 6, 2016

How to convert full name to first, middle and last name on oracle

Filed under: oracle — sikathabis @ 8:57 am
Tags: ,

/* Sql to generate or convert fullname to firstname, middlename and lastname */

with name_list as
(select ‘1’ NO, ‘ACEP’ name from dual
union
select ‘2’ NO, ‘TAUFIK HIDAYAT’ name from dual
union
select ‘3’ NO, ‘DEPAN TENGAH BELAKANG’ name from dual
union
select ‘4’ NO, ‘DONO KASINO INDRO WARKOP’ name from dual)
select no, name
, case when length(name) – length(replace(name,’ ‘,null)) 0 then
substr(trim(name), 1, instr(trim(name), ‘ ‘) – 1) else name end AS FIRST_NAME
, case when length(name) – length(replace(name,’ ‘,null)) >1 then
rtrim(substr(trim(substr(name, instr(name, ‘ ‘) + 1)), 1, instr(substr(trim(substr(name, instr(name, ‘ ‘) + 1)), 1, length(name)), ‘ ‘)-1)) else ” end AS MIDDLE_NAME
, case when length(name) – length(replace(name,’ ‘,null)) >1 then
substr(trim(substr(name, instr(name,’ ‘,1,2) + 1)), 1, length(name))
when length(name) – length(replace(name,’ ‘,null)) >0 then
substr(trim(substr(name, instr(name, ‘ ‘) + 1)), 1, length(name)) else ” end AS LAST_NAME
from name_list order by 1;

–Output

NO   NAME                     FIRST_NAME    MIDDLE_NAME    LAST_NAME
1    ACEP                     ACEP        
2    TAUFIK HIDAYAT           TAUFIK        HIDAYAT
3    DEPAN TENGAH BELAKANG    DEPAN         TENGAH         BELAKANG
4    DONO KASINO INDRO WARKOP DONO          KASINO         INDRO WARKOP

Reference : http://stackoverflow.com/questions/371490

 

July 26, 2016

Contoh PL/SQL pengambilan nama dari separator koma (,)

Filed under: oracle — sikathabis @ 7:59 pm
Tags: ,

Contoh:

DECLARE
KALIMAT varchar2(100):=’DD_MASTER,LN_MASTER_AUDIT,TRAN_HIST,TASPEN’;
kata varchar2(100):=null;
c number:=0;
BEGIN
FOR i in 1..length(KALIMAT)
LOOP
if substr(kalimat,i,1)=’,’ then
c:=c+1;
end if;
END LOOP;
(more…)

February 17, 2016

How to create Script to generate a table structure in oracle

Filed under: oracle — sikathabis @ 9:13 am
Tags: ,

–Script

select  tc.table_name "TABLE",
        column_id ID,
        rtrim(column_name) COLUMN_NAME, 
        rtrim(data_type) ||
        rtrim(decode(data_type,'DATE',null,'LONG',null,
               'NUMBER',decode(to_char(data_precision),null,null,'('),
               '(')) ||
        rtrim(decode(data_type,
               'DATE',null,
               'CHAR',data_length,
               'VARCHAR2',data_length,
               'NUMBER',decode(to_char(data_precision),null,null,
                 to_char(data_precision) || ',' || to_char(data_scale)),
               'LONG',null,
               ''))||
        rtrim(decode(data_type,'DATE',null,'LONG',null,
               'NUMBER',decode(to_char(data_precision),null,null,')'),
               ')')) || ' ' || ' ' DATA_TYPE,
        rtrim(decode(nullable,'N','NOT NULL',null)) OTHERS
from    dba_tab_columns tc,
        dba_objects o
where   o.owner = tc.owner
and     o.object_name = tc.table_name
and     o.object_type = 'TABLE' 
and     o.owner = 'USERDATABASE';

–Example result

TABLE    ID   COLUMN_NAME   DATA_TYPE     OTHERS
ACUAN    1    ID            NUMBER        NOT NULL
ACUAN    2    MIN_RATING    NUMBER(22,5)  NOT NULL
ACUAN    3    MAX_RATING    NUMBER(22,5)  NOT NULL
ACUAN    4    RISK_RATING   CHAR(10)      NOT NULL
KD_TRAN  1    ID            VARCHAR2(20)  NOT NULL
KD_TRAN  2    KD_TRAN       VARCHAR2(4)   NOT NULL

December 8, 2014

Function GET_FILENAME and GET_PATHNAME

Filed under: dev6i,oracle — sikathabis @ 8:47 am
Tags: , ,

FUNCTION GET_FILENAME(PKALIMAT VARCHAR2) RETURN VARCHAR2 IS
kata varchar2(100):=null;
KALIMAT VARCHAR2(1000):=PKALIMAT;
c number:=0;
KAR CHAR(1):=’/’;
BEGIN

IF INSTR(PKALIMAT,KAR)=0 THEN
KAR:=’\’;
END IF;

FOR i in 1..length(KALIMAT)
LOOP
if substr(kalimat,i,1)=KAR then
c:=c+1;
end if;
END LOOP;

FOR i in 1..c
LOOP
kata:= substr(KALIMAT,1,instr(KALIMAT,KAR)-1);
KALIMAT := replace(kalimat,kata||KAR,”);
END LOOP;

RETURN KALIMAT;

END;

——————————————————————— (more…)

October 9, 2014

How to change tablespace on indexes using script

Filed under: oracle — sikathabis @ 8:29 am
Tags: , ,

In this case:

  • I have tablespaces VBS_MASTER, VBS_MASTER_IDX, VBS_HIST, VBS_HIST_IDX
  • I want to move all indexes on user DBADMIN tablespace _IDX to without _IDX  (exp: VBS_HIST_IDX –> VBS_HIST)

Script running on user DBADMIN:

spool movetablespace.sql
 select 'ALTER INDEX ' ||INDEX_NAME||' REBUILD TABLESPACE '|| replace(TABLESPACE_NAME,'_IDX','')||';' as "--TITLE"
 from dba_indexes
 where owner='DBADMIN' and tablespace_name like '%IDX';
 spool off
 @movetablespace.sql

February 7, 2014

Decimal with comma (,) or dot (.) conversion to number Oracle

Filed under: oracle — sikathabis @ 9:17 am
Tags: ,

/** source code **/

Create or Replace FUNCTION RET_NUMBER (ntext VARCHAR2) RETURN number IS
vnum number;
c number(1);
s varchar2(1):=null;
begin
–cari karakter desimal dulu
select instr(ntext,’,’) into c    from dual;
if c = 0 then
select instr(ntext,’.’) into c from dual;
end if;
–jika ketemu ambil karakternya
if c > 0 then
s:= substr(ntext,c,1);
end if;
begin
select to_number(ntext) into vnum from dual;
exception when others then
begin
–jika error maka replace karakternya dari . menjadi , atau sebaliknya
select to_number(replace(ntext,s,decode(s,’,’,’.’,’,’))) into vnum from dual;
exception when others then
–jika masih salah maka munculkan error
RAISE_APPLICATION_ERROR(-20001, ‘Error konversi number ‘||ntext||’, cek BARIS KE atau kolom angka’);
end;
end;
return vnum;
end;

/** example **/

select ‘number with dot=’|| ret_number(‘500.1′) ||’, number with coma ‘ || ret_number(‘252,25’) from dual;

September 16, 2013

Query untuk melihat ukuran unique index pada table2 tertentu

Filed under: oracle — sikathabis @ 12:06 pm
Tags: ,

–Query
SELECT   segment_name “INDEX_NAME”, ROUND (bytes / 1024 / 1024, 2) “UKURAN (MB)”
FROM   user_segments
WHERE   segment_type = ‘INDEX’
AND segment_name IN
(SELECT   INDEX_NAME
FROM   dba_indexes
WHERE   owner = ‘DBADMIN’ AND uniqueness = ‘UNIQUE’
AND table_name IN
(‘GL_INTERFACE_HIST’,
‘PAJAK_GAB’,
‘TRAN_CIS_NASABAH’,
‘GL_KONSOLIDASI_SYARIAH’));

 

–Hasil

INDEX_NAME    UKURAN (MB)
GL_INT_HIS_IDX1    21181.06
TRAN_CIS_NASABAH_UNIQ_IDX    535.31
PAJAK_GAB1_IDX    496
GL_KONS_SYARIAH_UNIQ_IDX    445.44

July 14, 2013

How to generate dynamic trigger INSERT, UPDATE & DELETE in oracle

Filed under: oracle — sikathabis @ 6:50 am
Tags: ,
Step 1 : User must have access to create TRIGGER, for example GRANT CREATE ANY TRIGGER TO DBADMIN;

Step 2 : Create Procedure GEN_TGR which default to generate INSERT, UPDATE, DELETE trigger automaticly

CREATE OR REPLACE procedure DBADMIN.gen_tgr (tname varchar2,dblink varchar2) is
   F1 VARCHAR2(500);
   F2 VARCHAR2(32625);
   F3 VARCHAR2(32625);
   F4 VARCHAR2(32625);
   F5 VARCHAR2(1000);
   F6 VARCHAR2(500);
begin
--Generate Header 
F1 := 'create or replace trigger '||tname ||'_TRG_IUD'||chr(10)||
                            ' after update or delete or insert on '||tname ||chr(10)||
                            ' for each row ' ||chr(10)||
                            ' begin ' ||chr(10) ||
                            ' if inserting then ' ||chr(10) ||
--Generate Insert                             
                            ' Insert into '||tname||dblink||chr(10)||'( ';
for I_do in
 (more...)

June 14, 2013

How to add sequence on trigger

Filed under: oracle — sikathabis @ 9:18 am
Tags: ,

links : http://www.razorsql.com/features/oracle_add_sequence_trigger.html

example

CREATE OR REPLACE TRIGGER TEST_SEQ_TRIGGER BEFORE INSERT ON TESTUSER.EMPLOYEE FOR EACH ROW BEGIN

IF :new.SSN IS NULL THEN

SELECT TEST_SEQUENCE.nextval INTO :new.SSN FROM DUAL;

END IF;

END;

June 4, 2013

Tips menghapus record yang double dgn data yang sama persis pada oracle

Filed under: oracle — sikathabis @ 3:32 am
Tags: ,

Dulu pernah memposting tentang ini di multiply, tapi sekarang sudah almarhum. Alhamdulillah ada orang yang copy dari multiply tsb

DECLARE
CURSOR c1 IS
SELECT NAMA_FIELD FROM NAMA_TABLE
WHERE NAMA_FIELD IN
–Dibawah ini adalah proses pengecek-an record yg double
(SELECT NAMA_FIELD
FROM NAMA_TABLE
HAVING COUNT(1)>1
GROUP BY NAMA_FIELD)
–Diatas ini adalah proses pengecek-an record yg double
ORDER BY NAMA_FIELD
–Dibawah ini adalah penting utk di deklarasikan
FOR UPDATE OF NAMA_FIELD;
c1rec c1%ROWTYPE;
c NUMBER:=0;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1rec;
EXIT WHEN c1%NOTFOUND;
c:=c+1;
— Cek apakah yg akan didelete data pertama atau kedua?
IF c MOD 2=0 THEN
— Jika posisi record yg didelete data pertama, masukan disini perintah deletenya
NULL;
ELSE
— Jika posisi record yg didelete data kedua, masukan disini perintah deletenya
DELETE FROM NAMA_TABLE
WHERE CURRENT OF C1;
END IF;
END LOOP;
CLOSE c1;
COMMIT;
END;

Next Page »

Create a free website or blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

arihdyacaesar

"A distance is getting far if your heart is impatient" -ean-

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

Angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

Don Charisma

because anything is possible with Charisma