Programming Resource
Database
Convert XMLTYPE to CLOB or Varchar
0Pada dokumentasi kali ini saya tulis konversi XMLTYPE ke cloB atau varchar2 di oracle.
Misal saya punya table T1 dengan kolom tipe data XMLTYPE
CREATE TABLE T1(COL1 XMLTYPE);
sekarang kebutuhan saya adalah menyimpan informasi COL1 dalam tipe data CLOB atau VARCHAR2.
DECLARE v_col CLOB; v_str VARCHAR2(200); v_xml XMLTYPE; BEGIN SELECT COL1 INTO v_xml FROM T1; v_col := v_xml.GETCLOBVAL (); v_str := v_xml.GETSTRINGVAL(); END;
cukup dulu dokumentasi kali ini kita lanjutkan lain kali.
Fungsi Terbilang
0Berikut adalah conoth fungsi tebilang menggunakan Oracle, cukup simple didalam oracle hanya dengan menggunakan fungsi TO_DATE dengan format Jsp.
FUNCTION terbilang (
v_number IN NUMBER
)
RETURN VARCHAR2
AS
TYPE myArray IS TABLE OF VARCHAR2 (255);
str_array myArray
:= myArray ('',
'thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
'septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
str_number VARCHAR2 (50);
l_return VARCHAR2 (4000);
trans1 VARCHAR2 (4000);
trans2 VARCHAR2 (4000);
BEGIN
str_number := TRUNC (ABS (v_number));
FOR i IN 1 .. str_array.COUNT
LOOP
EXIT WHEN str_number IS NULL;
IF (SUBSTR (str_number, LENGTH (str_number) - 2, 3) <> 0)
THEN
trans1 :=
TO_CHAR (TO_DATE (SUBSTR (str_number, LENGTH (str_number) - 2,
3),
'J'
),
'Jsp'
)
|| str_array (i)
|| trans1;
END IF;
str_number := SUBSTR (str_number, 1, LENGTH (str_number) - 3);
END LOOP;
l_return := trans1;
IF TO_CHAR (v_number) LIKE '%.%'
THEN
str_number :=
(ABS (v_number) - TRUNC (ABS (v_number)))
* (TO_NUMBER (RPAD ('1',
(LENGTH ((ABS (v_number) - TRUNC (ABS (v_number))
)
)
),
RPAD ('0', 50, '0')
)
)
);
FOR i IN 1 .. str_array.COUNT
LOOP
EXIT WHEN str_number IS NULL;
IF (SUBSTR (str_number, LENGTH (str_number) - 2, 3) <> 0)
THEN
trans2 :=
TO_CHAR (TO_DATE (SUBSTR (str_number,
LENGTH (str_number) - 2,
3
),
'J'
),
'Jsp'
)
|| str_array (i)
|| trans2;
END IF;
str_number := SUBSTR (str_number, 1, LENGTH (str_number) - 3);
END LOOP;
l_return := trans1 || ' point ' || trans2;
END IF;
RETURN l_return;
END terbilang;
untuk mencobanya
SQL> SELECT TERBILANG(100) as AA FROM DUAL; AA ----------------- One Hundred
Untuk fungsi terbilang dalam bahasa Indonesia
FUNCTION terbilang_indo(v_number IN NUMBER) RETURN VARCHAR2 AS
BEGIN
RETURN REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
REPLACE(REPLACE( REPLACE( REPLACE(
LOWER( TERBILANG(v_number))
, 'trillion' , 'Trilyun')
, 'billion' , 'Milyar')
, 'million' , 'Juta')
, 'onethousand' , 'Seribu')
, 'thousand' , 'Ribu')
, 'nine hundred' , 'Sembilan Ratus')
, 'eigth hundred' , 'Delapan Ratus')
, 'seven hundred' , 'Tujuh Ratus')
, 'six hundred' , 'Enam Ratus')
, 'five hundred' , 'Lima Ratus')
, 'four hundred' , 'Empat Ratus')
, 'three hundred' , 'Tiga Ratus')
, 'two hundred' , 'Dua Ratus')
, 'one hundred' , 'Seratus')
, 'hundred' , 'Ratus')
, 'ninety' , 'Sembilan Puluh')
, 'eighty' , 'Delapan Puluh')
, 'seventy' , 'Tujuh Puluh')
, 'sixty' , 'Enam Puluh')
, 'fifty' , 'Lima Puluh')
, 'forty' , 'Empat Puluh')
, 'thirty' , 'Tiga Puluh')
, 'twenty' , 'Dua Puluh')
, 'nineteen' , 'Sembilanbelas')
, 'eighteen' , 'Delapanbelas')
, 'seventeen' , 'Tujuhbelas')
, 'sixteen' , 'Enambelas')
, 'fifteen' , 'Limabelas')
, 'fourteen' , 'Empatbelas')
, 'thirteen' , 'Tigabelas')
, 'twelve' , 'Duabelas')
, 'eleven' , 'Sebelas')
, 'ten' , 'Sepuluh')
, 'nine' , 'Sembilan')
, 'eight' , 'Delapan')
, 'seven' , 'Tujuh')
, 'six' , 'Enam')
, 'five' , 'Lima')
, 'four' , 'Empat')
, 'three' , 'Tiga')
, 'two' , 'Dua')
, 'one' , 'Satu')
, 'point' , 'koma')
, '','');
END terbilang_indo;
untuk mencobanya
SQL> SELECT TERBILANG_INDO(100) as AA FROM DUAL; AA ----------------- Seratus
Connecting to Oracle using Java
0Tutorial kali ini adalah program dalam bahasa java untuk koneksi ke database oracle. Untuk dapat membangun koneksi ke database oracle kita memerlukan sebuah library java yang disebut JDBC (Java DataBase Connectivity). Tidak hanya ke oracle JDBC hampir bisa digunakan untuk semua RDBMS yang ada di pasaran. Untuk mensupport tipe data khusus yang ada di suatu database biasanya setiap vendor database menyediakan JDBC khusus untuk databasenya. Sebagai contoh Oracle, Oracle menyediakan library JDBC khusus oracle dengan tujuan untuk mensupport tipe data khusus yang hanya ada di oracle dan meningkatkan performansi. Kita tidak akan terlalu panjang membahas JDBC sekarang kita akan belajar langkah pertama dari JDBC membangun koneksi ke database. Urutan proses koneksi ke database adalah sebgai berikut :
- Load Driver
- Setup URL
- Membangun koneksi
Ok, sekarang coba kita buat sample java program untuk membuat koneksi ke database oracle. Pada contoh ini kita akan koneksi ke database local skema HR.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package oracleEx;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
*
* @author Massri
*/
public class OracleConn {
private String username;
private String password;
private String serviceId;
private String servername;
private int port;
private static String driver = "oracle.jdbc.driver.OracleDriver";
private Connection conn;
public OracleConn() {
}
public OracleConn(String username, String password, String serviceId, String servername, int port) {
this.username = username;
this.password = password;
this.serviceId = serviceId;
this.servername = servername;
this.port = port;
this.conn = null;
}
public void Connect(){
try {
System.out.println("Conneting ...");
Class.forName(OracleConn.driver);
String url = "jdbc:oracle:thin:@" + this.servername + ":" + this.port + ":" + this.serviceId;
System.out.println("URL = "+ url);
this.conn = DriverManager.getConnection(url, this.username, this.password);
} catch (SQLException ex) {
System.err.println(ex.getMessage());
} catch (ClassNotFoundException ex) {
System.err.println(ex.getMessage());
}
}
public Connection getConnection(){
return this.conn;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getPort() {
return port;
}
public void setPort(int port) {
this.port = port;
}
public String getServername() {
return servername;
}
public void setServername(String servername) {
this.servername = servername;
}
public String getServiceId() {
return serviceId;
}
public void setServiceId(String serviceId) {
this.serviceId = serviceId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public static void main(String[] args){
System.out.println("Testing Connecting to Database ");
OracleConn ora = new OracleConn();
ora.setServername("127.0.0.1");
ora.setUsername("HR");
ora.setPassword("HR");
ora.setPort(1521);
ora.setServiceId("ORACL");
ora.Connect();
System.out.println("Conn Resource" + ora.getConnection().toString());
}
}
Simpan program dengan nama file OracleConn.java dan compile kemudian setelah kita jalankan akan muncul hasil seperti berikut :
Testing Connecting to Database Conneting ... URL = jdbc:oracle:thin:@127.0.0.1:1521:ORCL Conn Resourceoracle.jdbc.driver.T4CConnection@12f6684 BUILD SUCCESSFUL (total time: 2 seconds)
Convert XML Document to Oracle Types
0Mulai versi 9 oracle mengenalkan tipe data XMLTYPE. Untuk penjelasan tentang XMLTYPE bisa dilihat di postingan sebelumnya, sekarang kita mencoba memanfaatkan XMLTYPE. Tujuan tulisan ini adalah mengubah XML document ke Oracle Type. Misal saya mempunyai dokumen XML seperti dibawah ini :
<?xml version="1.0"?> <SalesDoc> <salesDate>22/01/2011</salesDate> <terminalName>BDG01</terminalName> <Product> <ProductId>P001</ProductId> <ProductName>Product 1</ProductName> <ProductQty>10</ProductQty> <ProductPrice>200000<ProductPrice> </Product> </SalesDoc>
Kita ingin merubah dokumen XML diatas menjadi Oracle Types.Pertama yang harus kita lakukan adalah membuat Oracle Type dan langkah berikutnya adalah kita parsing XML dan kita assignment ke Tipe yang sudah kita buat.
DECLARE
TYPE T_SALES_DOC IS RECORD (
salesDate DATE,
brachName VARCHAR2 (20),
productId VARCHAR2 (10),
productName VARCHAR2 (40),
productQty INTEGER,
productPrice NUMBER (10)
);
TYPE T_SALES_LIST IS TABLE OF T_SALES_DOC;
v_xmltype XMLTYPE; -- variable untuk menyimpan document xml;
v_salesDoc T_SALES_LIST := T_SALES_LIST ();
v_dom DBMS_XMLDOM.DOMDocument;
v_domNodeList DBMS_XMLDOM.DOMNodeList;
v_length INTEGER;
v_node DBMS_XMLDOM.DOMNode;
v_xmlstr VARCHAR2 (400);
BEGIN
v_xmlstr :=
'<?xml version="1.0"?>
<SalesDoc>
<salesDate>22/01/2011</salesDate>
<terminalName>BDG01</terminalName>
<Product>
<ProductId>P001</ProductId>
<ProductName>Product 1</ProductName>
<ProductQty>10</ProductQty>
<ProductPrice>200000</ProductPrice>
</Product>
</SalesDoc>';
v_xmltype := XMLTYPE (v_xmlstr);
v_dom := DBMS_XMLDOM.NEWDOMDOCUMENT (xmldoc => v_xmltype);
v_domNodeList :=
DBMS_XSLPROCESSOR.selectNodes (DBMS_XMLDOM.makeNode (v_dom),
'/SalesDoc/Product'
);
v_length := DBMS_XMLDOM.getLength (v_domNodeList);
FOR i IN 0 .. (v_length - 1)
LOOP
v_node := DBMS_XMLDOM.item (v_domNodeList, i);
v_salesDoc.EXTEND;
DBMS_XSLPROCESSOR.valueOf (v_node,
'ProductId/text()',
v_salesDoc (v_salesDoc.LAST).ProductId
);
DBMS_XSLPROCESSOR.valueOf (v_node,
'ProductName/text()',
v_salesDoc (v_salesDoc.LAST).ProductName
);
DBMS_XSLPROCESSOR.valueOf (v_node,
'ProductQty/text()',
v_salesDoc (v_salesDoc.LAST).ProductQty
);
DBMS_XSLPROCESSOR.valueOf (v_node,
'ProductPrice/text()',
v_salesDoc (v_salesDoc.LAST).ProductPrice
);
END LOOP;
-- menambilkan hasil ke layar
FOR i IN v_salesDoc.FIRST .. v_salesDoc.LAST
LOOP
DBMS_OUTPUT.put_line ('product ID : ' || v_salesDoc (i).ProductId);
DBMS_OUTPUT.put_line ('ProductName : ' || v_salesDoc (i).ProductName);
DBMS_OUTPUT.put_line ('ProductQty : ' || v_salesDoc (i).ProductQty);
DBMS_OUTPUT.put_line ('ProductPrice : ' || v_salesDoc (i).ProductPrice);
END LOOP;
END;
berikut hasil contoh running dari program diatas
