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

/* 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

 

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

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;

Print using default printer on local computer (Oracle Developer 6i, windows)

this example, I want to show you how to automate print using default printer on local computer.

PROCEDURE GENERATE_VALIDATION (ID1 number, ID2 number, ID3 DATE) IS
    PL_ID PARAMLIST;
    filename varchar2(255);
    teks varchar2(255);
    IN_FILE TEXT_IO.FILE_TYPE;
BEGIN
    pl_id := Create_Parameter_List('TEMP'); 
    ADD_PARAMETER(PL_ID,'ID_1',TEXT_PARAMETER,ID1);
    ADD_PARAMETER(PL_ID,'ID_2',TEXT_PARAMETER,ID2);
    ADD_PARAMETER(PL_ID,'ID_3',TEXT_PARAMETER,ID3);

--this statement for get default printer and generate to text file

    host('CMD /C wmic printer where default=''TRUE'' get name > '||filename,NO_SCREEN);

--because wmic generate 3 lines, this statement for get only line where the 
--default printer located

    host('CMD /C Powershell "Get-Content '||filename||' | Select-Object -Index 1" > '||filename||'.txt', NO_SCREEN );
    begin
        IN_FILE  := TEXT_IO.FOPEN(filename||'.txt', 'r');   
        TEXT_IO.GET_LINE(IN_FILE,TEKS);
        --Trim TEKS, because there is spaces after default printer name
        TEKS := LTRIM(RTRIM(TEKS));
        TEXT_IO.FCLOSE(IN_FILE);
    exception when others then
        TEXT_IO.FCLOSE(IN_FILE);
    end;  

--erase temporary files

    host('CMD /R DEL '||filename||'.*',NO_SCREEN);    

--if there's default printer then automate print default printer on local computer

    if TEKS is null then
         ADD_PARAMETER(PL_ID,'DESTYPE',TEXT_PARAMETER,'PRINTER');
         ADD_PARAMETER(PL_ID,'DESNAME',TEXT_PARAMETER,TEKS);

--if there's no default printer then show your report

    else
         ADD_PARAMETER(PL_ID,'DESTYPE',TEXT_PARAMETER,'SCREEN');
    end if;     
    Add_Parameter(pl_id,'PARAMFORM', TEXT_PARAMETER, 'NO');
    Add_Parameter(pl_id,'PRINTJOB',  TEXT_PARAMETER,'YES'); 

    Run_Product(REPORTS,'VALIDATION', SYNCHRONOUS, RUNTIME,FILESYSTEM, pl_id, NULL);     
    Destroy_Parameter_List(pl_id);
END;

Reference: https://technet.microsoft.com/en-us/library/hh849895.aspx | https://adamstech.wordpress.com/2010/11/1 | http://stackoverflow.com/questions/17217476..

Notes: For windows XP/2003 you must install powershell, here’s the links:

1. Microsoft .NET Framework 2.0 Service Pack 1 (x86)
2. Update for Windows XP (KB968930): includes Windows PowerShell 2.0 and Windows Remote Management (WinRM) 2.0
3. Update for Windows 2003

 

How to know all list printer on network

@echo off

SETLOCAL ENABLEDELAYEDEXPANSION
for /f "tokens=2 delims==" %%a in ('wmic printer get name /value') do (
    set printer=%%a
    if defined printer (
      if "!printer:~0,2!"=="\\" (
        echo !printer! >> "C:\file.txt"
      )
    )
)
ENDLOCAL
echo on

reference: https://community.spiceworks.com/topic/365018-list-network-printers-via-batch-file

How to add license vnc server linux

Connect linux using putty or others application using root user

example:

vnclicense -add ABCD1-EFGH2-IJKL3-MNOP4-QRST5

reference: https://www.realvnc.com/products/vnc/documentation/5.0/installing-removing/redhat

How to create Script to generate a table structure in oracle

–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

Gelar Akademis

Gelar Akademis

Ulfiarahmi's Weblog

Gelar Vokasi
A.P            : Ahli Pratama
A.Ma        : Ahli Muda
A.Md        : Ahli Madya
A                : Profesional Ahli
A.Ma.Pd  : Ahli Muda Kependidikan
A.Md.Per: Ahli Madya Keperawatan

View original post 1,323 more words

Comparison external hard drive cable

I have two hard drives, have same specification, but the cable looks like same but when I see closely, were different. And when I copied data, the speed of copying is totally different. Can you guess, a cable which has a faster speed ?

 

This reference for detailed comparation cable types: http://knowledge.seagate.com/articles/en_US/FAQ/205479en?popup=true

How to hide password login to oracle in CMD

I have application for backup database, in the application I’ve created, can generate user password, without other user know what the password is. Other user use that application which can see the content of .bat file where my application whom creating that file, the trouble is, other user can see password from login “user/password@tnsnames” . There are many solution to hide the password from Mbah Google, but there’s one reference very usefull

http://stackoverflow.com/questions/17807188/how-to-authenticate-oracle-login-using-encrypted-password (first comment)

example: echo generatepassword | exp username@tnsnames file=backup.dmp owner=scott

“Echo generatepassword |” is created on the application, and “exp username@tnsnames file=backup.dmp owner=scott” is .bat file which can other user see or using it.

Troubleshoot Jabref with germany font

If you have trouble with your jabref (bibliography or daftar pustaka) there’s an Author name containt ö and the result is ö. To solve the problem change ö with \”{o} in the BibTeX source

reference: https://forums.zotero.org/discussion/3567/exporting-of-items-with-german-umlaut-ae-oe-ue/