Sunday, August 21, 2005

Petals Around the Rose

A friend introduced this interesting game to me. It is more like an observation game plus a little bit of math. Apparently it took Dr. Richard Duke at the University of Michigan over a year to "see the solution". He claimed "the smarter you were, the longer it took to figure it out". Maybe smarter people tend to think too much, or loose themselves in the details.... (hint) Interestingly I got it right on the first try. Perhaps it's due to me checking the dictionary what petal means.... (hint)

posted by Wijaya Kusumo at 8/21/2005 09:30:00 AM 1 comments

Tuesday, August 16, 2005

Mac OS X on your PC

With Apple moves to x86 architecture, it is no wonder people start to do "what if" scenario. And now someone built a Mac for US $199! http://osx86project.org/ It is a shame why it took Apple so long to realize its cash cow is actually on x86 platform. Just imagine if you get a Dell box pre-installed with Mac OS!

posted by Wijaya Kusumo at 8/16/2005 09:56:00 AM 3 comments

Tuesday, August 02, 2005

Oracle XMLDB: Create XML data with ease

It is a fact that we, as software developer, will encounter more and more data in XML format. Either we receive xml data or actually need to produce one for information sharing with other system (web service, legacy system, .net vs Java, etc...). XMLDB of Oracle database (version 9i Release 2 and above) provides us with a convenience data type (XMLType) to store xml data, as well as the full set of functions for manipulating and accessing it. Action speak loader than words. So let's see how easy XMLDB actually is... Using the default SCOTT schema, let's assume that we need to create xml data showing all employee details. We shall create 2 tables: 1. dept_xml: a huge xml doc of all department and the employee within it. 2. emp_xml: all employee information in a xml snippet And here we go: 1. Create the tables with XMLType column for storing the xml data. 
scott> create table dept_xml(
  2    xml     XMLType);

Table created.

scott> create table emp_xml(
  2    xml     XMLType);

Table created.
2. Transform the relational data from DEPT and EMP table into xml document using SQL functions and PL/SQL 
 
-- insert a big xml doc to dept_xml  table

scott> insert into dept_xml values ((SELECT XMLRoot(XMLElement("ORG_CHART", XMLAgg(XMLElement("DEPARTMENT",
  2         XMLAttributes(d.deptno AS "DEPTNO",
  3                       d.dname AS "NAME",
  4                       d.loc AS "LOCATION"),
  5          XMLForest(
  6           (SELECT XMLAgg(
  7                     XMLElement(
  8                       "EMPLOYEE",
  9                       xmlforest(e.empno, 
 10                                 e.ename,
 11                                 e.job,
 12                                 e.mgr,
 13                                 e.hiredate,
 14                                 e.sal,
 15                                 e.comm,
 16                                 s.grade
 17                       )
 18                     )
 19                   )
 20             FROM emp e, salgrade s
 21             WHERE e.deptno = d.deptno
 22             AND e.sal BETWEEN s.losal and s.hisal
 23           ) "EMPLOYEES"
 24         )
 25       )))) "XML"
 26  FROM dept d
 27  ));
 
1 row created.

-- insert each employee data as a separate xml doc

scott> DECLARE
  2    empNo  NUMBER;
  3    mysql  VARCHAR2(512); 
  4    CURSOR empNoCursor IS
  5      select empno
  6      from emp;
  7  BEGIN
  8    OPEN empNoCursor;
  9    LOOP
 10      FETCH empNoCursor INTO empNo;
 11      EXIT WHEN empNoCursor %NOTFOUND;
 12      
 13      --DBMS_OUTPUT.put_line('empNo: ' || empNo);
 14  
 15      mysql := 'insert into emp_xml values ((SELECT XMLRoot( ' ||
 16               '  XMLElement( ' ||
 17               '  "EMPLOYEE", ' ||
 18               '  xmlforest(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno, s.grade))) ' ||
 19               ' )) ' ||
 20               ' FROM emp e, salgrade s ' ||
 21               ' WHERE e.empno = :1 ' ||                    
 22               ' AND e.sal BETWEEN s.losal and s.hisal ' || 
 23               ' )) ';                                      
 24                                                            
 25      EXECUTE IMMEDIATE mysql using empNo;                  
 26                                                            
 27    END LOOP;                                               
 28    CLOSE empNoCursor;                                      
 29    COMMIT;                                                 
 30  END;                                                      
 31  /       

PL/SQL procedure successfully completed.
Here we use the following XML SQL Utility: XMLRoot: create a XML document of XMLType XMLAgg: aggregate a bunch of XML elements and group it as one forest XMLElement: create a XML element XMLAttributes: create a XML attribute XMLForest: take XMLAgg result and group them as a XML node Please note that for dept_xml entry, we also created our custom tag named: ORG_CHART, DEPARTMENT, EMPLOYEES, and EMPLOYEE Check Oracle documentation for syntax and more explanation. 3. Final result: 
XML
-------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<ORG_CHART>
  <DEPARTMENT DEPTNO="10" NAME="ACCOUNTING" LOCATION="NEW YORK">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7839</EMPNO>
        <ENAME>KING</ENAME>
        <JOB>PRESIDENT</JOB>
        <HIREDATE>1981-11-17</HIREDATE>
        <SAL>5000</SAL>
        <GRADE>5</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7782</EMPNO>
        <ENAME>CLARK</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-06-09</HIREDATE>
        <SAL>2450</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7934</EMPNO>
        <ENAME>MILLER</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7782</MGR>
        <HIREDATE>1982-01-23</HIREDATE>
        <SAL>1300</SAL>
        <GRADE>2</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="20" NAME="RESEARCH" LOCATION="DALLAS">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7788</EMPNO>
        <ENAME>SCOTT</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>1987-04-19</HIREDATE>
        <SAL>3000</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7902</EMPNO>
        <ENAME>FORD</ENAME>
        <JOB>ANALYST</JOB>
        <MGR>7566</MGR>
        <HIREDATE>1981-12-03</HIREDATE>
        <SAL>3000</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7566</EMPNO>
        <ENAME>JONES</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-04-02</HIREDATE>
        <SAL>2975</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7876</EMPNO>
        <ENAME>ADAMS</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7788</MGR>
        <HIREDATE>1987-05-23</HIREDATE>
        <SAL>1100</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7369</EMPNO>
        <ENAME>SMITH</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7902</MGR>
        <HIREDATE>1980-12-17</HIREDATE>
        <SAL>800</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="30" NAME="SALES" LOCATION="CHICAGO">
    <EMPLOYEES>
      <EMPLOYEE>
        <EMPNO>7698</EMPNO>
        <ENAME>BLAKE</ENAME>
        <JOB>MANAGER</JOB>
        <MGR>7839</MGR>
        <HIREDATE>1981-05-01</HIREDATE>
        <SAL>2850</SAL>
        <GRADE>4</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7499</EMPNO>
        <ENAME>ALLEN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-02-20</HIREDATE>
        <SAL>1600</SAL>
        <COMM>300</COMM>
        <GRADE>3</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7844</EMPNO>
        <ENAME>TURNER</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-09-08</HIREDATE>
        <SAL>1500</SAL>
        <COMM>0</COMM>
        <GRADE>3</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7521</EMPNO>
        <ENAME>WARD</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-02-22</HIREDATE>
        <SAL>1250</SAL>
        <COMM>500</COMM>
        <GRADE>2</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7654</EMPNO>
        <ENAME>MARTIN</ENAME>
        <JOB>SALESMAN</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-09-28</HIREDATE>
        <SAL>1250</SAL>
        <COMM>1400</COMM>
        <GRADE>2</GRADE>
      </EMPLOYEE>
      <EMPLOYEE>
        <EMPNO>7900</EMPNO>
        <ENAME>JAMES</ENAME>
        <JOB>CLERK</JOB>
        <MGR>7698</MGR>
        <HIREDATE>1981-12-03</HIREDATE>
        <SAL>950</SAL>
        <GRADE>1</GRADE>
      </EMPLOYEE>
    </EMPLOYEES>
  </DEPARTMENT>
  <DEPARTMENT DEPTNO="40" NAME="OPERATIONS" LOCATION="BOSTON"/>
</ORG_CHART>

scott> select * from emp_xml;

XML
-------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>1980-12-17</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-02-20</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>3</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-02-22</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-04-02</HIREDATE>
  <SAL>2975</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7654</EMPNO>
  <ENAME>MARTIN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-09-28</HIREDATE>
  <SAL>1250</SAL>
  <COMM>1400</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-05-01</HIREDATE>
  <SAL>2850</SAL>
  <DEPTNO>30</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>1981-06-09</HIREDATE>
  <SAL>2450</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7788</EMPNO>
  <ENAME>SCOTT</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>1987-04-19</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>1981-11-17</HIREDATE>
  <SAL>5000</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>5</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7844</EMPNO>
  <ENAME>TURNER</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-09-08</HIREDATE>
  <SAL>1500</SAL>
  <COMM>0</COMM>
  <DEPTNO>30</DEPTNO>
  <GRADE>3</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>1987-05-23</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>1981-12-03</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
  <GRADE>1</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>1981-12-03</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
  <GRADE>4</GRADE>
</EMPLOYEE>

<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEE>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>1982-01-23</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
  <GRADE>2</GRADE>
</EMPLOYEE>

14 rows selected.
As you can see, creating XML data from traditional relational table is a breeze with Oracle XML utilities. Instead of having to retrieve the table data, create a XML document in the middle tier, and storing back to the database, we achieve the goal in just a one SQL statement! Not to mention the performance gain and scalability of the system. In next blog, we shall see how we can access these XML info with ease, too. Stay tune...

posted by Wijaya Kusumo at 8/02/2005 07:06:00 PM 3 comments