SYAMS-Info: funcntion

FUNCTION ZMID(CStr IN VARCHAR2, STR IN INTEGER, LEN IN NUMBER DEFAULT 32767) RETURN VARCHAR2 IS
     iMID VARCHAR2(32767);
BEGIN
     If ( STR > Length(CStr) ) Then
         RETURN( NULL );
     END IF;
     iMID := SUBSTR( CStr, STR, len );
     RETURN ( iMID );
END;
/

FUNCTION zright(iChar VARCHAR2,jumlah NUMBER) RETURN VARCHAR IS
Vpanjang NUMBER;
Vchar_Kanan VARCHAR2(100);
BEGIN
Vpanjang:=Length(iChar)+1-jumlah;
Vchar_Kanan:=SUBSTR(iChar,Vpanjang,jumlah);
RETURN Vchar_Kanan;
END;
/

function ORACLE_TERBILANG(angka number) return varchar2 is

strJmlHuruf     number;
terbilang       varchar2(32767);
intPecahan      varchar2(32767);
strPecahan      varchar2(32767);
urai            varchar2(32767);
sMinus          varchar2(32767);
strTot          varchar2(32767);
x               varchar2(32767);
y               integer;
z               integer;
Bil1            varchar2(32767);
Bil2            varchar2(32767);
pNumber         varchar2(32767);

begin

   /*Cek Panjang input Angka*/
    pNumber :=length(angka);

    if substr(angka,1,1) <> '-' then
       if 16 <= pNumber then terbilang := ''; return(terbilang); end if;
    Else
       if 17 <= pNumber then terbilang := ''; return(terbilang); end if;
    End If;

   /*Cek Awal NOL*/
   If angka is Null or angka =0 Then terbilang := 'NOL';
      return(terbilang);
   End If;

       strJmlHuruf := LTrim((angka)); intPecahan := 0;

/*Cek Awal Minus*/
    If InStr(strJmlHuruf, '-') > 0 Then
            sMinus := 'MINUS ';
            terbilang := ZRight(strJmlHuruf, length(strJmlHuruf) - 1);
            strTot := terbilang; strJmlHuruf := terbilang;
    End If;

    /*Sementara Tdak ada pecahan :D*/
    If (intPecahan = 0) Then strPecahan := ''; End If;

    X := 0;
    Y := 0;
    Urai := '';

    /*::::::::::::LOOP::::::::::::*/
    While (X < length(strJmlHuruf)) Loop
                    X := X + 1;
                    strTot := ZMID(strJmlHuruf, X, 1);
                    Y := Y + (strTot);
                    z := length(strJmlHuruf) - X + 1;

    /*::::::::::::::::::::::::::Select Case Val(strTot)::::::::::::::::::::::::::*/
            if (strTot) = 1 then

                /*:::::::::::::::::::::::::::*/
                If (z = 1 Or z = 7 Or z = 10 Or z = 13) Then Bil1 := 'SATU ';

                ElsIf (z = 4) Then
                     If (X = 1) Then Bil1 := 'SE';
                        Else Bil1 := 'SATU ';
                    End If;

                ElsIf (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14 ) Then
                    X := X + 1;
                    strTot := ZMid(strJmlHuruf, X, 1);
                    z := length(strJmlHuruf) - X + 1;
                    Bil2 := '';

                       if strTot = 0 then Bil1 := 'SEPULUH ';
                             ElsIf strTot = 1 then Bil1 := 'SEBELAS ';
                             ElsIf strTot = 2 then Bil1 := 'DUA BELAS ';
                             ElsIf strTot = 3 then Bil1 := 'TIGA BELAS ';
                             ElsIf strTot = 4 then Bil1 := 'EMPAT BELAS ';
                             ElsIf strTot = 5 then Bil1 := 'LIMA BELAS ';
                             ElsIf strTot = 6 then Bil1 := 'ENAM BELAS ';
                             ElsIf strTot = 7 then Bil1 := 'TUJUH BELAS ';
                             ElsIf strTot = 8 then Bil1 := 'DELAPAN BELAS ';
                             else Bil1 := 'SEMBILAN BELAS ';
                       end if;
                 else
                  Bil1 := 'SE';
              end if;
              /*:::::::::::::::::::::::::::*/

              elsif strTot = 2 then Bil1 := 'DUA ';
              elsif strTot = 3 then Bil1 := 'TIGA ';
              elsif strTot = 4 then Bil1 := 'EMPAT ';
              elsif strTot = 5 then Bil1 := 'LIMA ';
              elsif strTot = 6 then Bil1 := 'ENAM ';
              elsif strTot = 7 then Bil1 := 'TUJUH ';
              elsif strTot = 8 then Bil1 := 'DELAPAN ';
              elsif strTot = 9 then Bil1 := 'SEMBILAN ';
              else Bil1 := '';
            end if;
    /*::::::::::::::::::::::::::END Select Case Val(strTot)::::::::::::::::::::::::::*/

                If ((strTot) > 0) Then
                    If (z = 2 Or z = 5 Or z = 8 Or z = 11 Or z = 14) Then      Bil2 := 'PULUH ';
                      ElsIf (z = 3 Or z = 6 Or z = 9 Or z = 12 Or z = 15) Then Bil2 := 'RATUS ';
                      Else Bil2 := '';
                    End If;
                Else       Bil2 := '';
                End If;

                If (Y > 0) Then
                  /*::::::Select Case z:::::::*/
                   if z = 4 then        Bil2 := Bil2||'RIBU ';   Y := 0;
                      elsif z = 7 then Bil2 := Bil2||'JUTA ';   Y := 0;
                      elsif z = 10 then Bil2 := Bil2||'MILYAR '; Y := 0;
                      elsif z = 13 then Bil2 := Bil2||'TRILYUN ';Y := 0;
                   end if;
                End If;

     Urai := Urai||Bil1||Bil2;
    end loop;
    /*::::::::::::END LOOP::::::::::::*/

       Urai := sMinus||Urai||strPecahan;
        If (intPecahan = 0) Then
           Terbilang := Urai||'RUPIAH ';
        End If;
return(terbilang);

end ORACLE_TERBILANG;
/

Oracle Function : Conversion date to number (hour,min)

Use this function to get conversion date variable to number(hour,min) :

The function would generate date variable, (date1-date2) becomes number (hour,min)

FUNCTION GET_DURATION(d1 number) RETURN VARCHAR2 IS
--# d1 = tanggal awal
--# d2 = tanggal akhir
tmpvar VARCHAR2(100);
durdays NUMBER(20, 10); -- days between two dates
durhrs BINARY_INTEGER; -- completed hours
durmin BINARY_INTEGER; -- completed minutes
dursec BINARY_INTEGER; -- completed second
durhari PLS_INTEGER;
jhrs number;
jmin number;
thrs number;
BEGIN
durdays := d1;

durhari := TRUNC(d1);

durhrs := MOD(TRUNC(24 * durdays), 24);
durmin := MOD(TRUNC(durdays * 1440), 60);
dursec := MOD(TRUNC(durdays * 86400), 60);

jhrs :=(to_number(durhari)*24)+to_number(durhrs||'.'||lpad(durmin,2,'0'));
thrs :=jhrs;
RETURN(thrs);
END get_duration;

How to use :

select GET_DURATION(date_variable) from dual;

example :

select GET_DURATION(date1-date2) from dual;

select GET_DURATION((date1-date2)-(date3-date4)) from dual;

ORACLE TERBILANG Function

Oracle Function PLSQL: Conversion date to number (hour,min)

How to use :