Oracle BULK COLLECT INTO using EXECUTE IMMEDIATE

SET SERVEROUTPUT ON;

--Create a sample table
CREATE TABLE EMP(EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/

--Insert a few records
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (100, 'Regina Hall', 25)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (101, 'Morris Chestnut', 35)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (102, 'Terrence Howard', 37)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (103, 'Sanaa Lathan', 25)
/

--Create User defined Objects to use in BULK COLLECT
CREATE TYPE EMP_OBJ IS OBJECT (EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/
CREATE TYPE EMP_TAB IS TABLE OF EMP_OBJ
/

--Anonymous block demonstrate the use of EXECUTE IMMEDIATE and BULK COLLECT INTO
DECLARE
    c_emp EMP_TAB;
    age_v NUMBER(3) :=25;
    query VARCHAR2(4000) := 'SELECT EMP_OBJ(EMP_ID, EMP_NAME, AGE) FROM EMP WHERE AGE=';
BEGIN
    query := query||age_v;
    EXECUTE IMMEDIATE query BULK COLLECT INTO c_emp;
    DBMS_OUTPUT.PUT_LINE('The number of employees in the Collection->'||c_emp.COUNT);
    FOR i IN c_emp.FIRST..c_emp.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(c_emp(i).emp_id||','||c_emp(i).emp_name||','||c_emp(i).age);
    END LOOP;
END;
/

Here is the output:
The number of employees in the Collection->2
100,Regina Hall,25
103,Sanaa Lathan,25

PL/SQL procedure successfully completed.

How to use JAXB with Spring - Marshalling & Unmarshalling

Using JAXB with Spring - Marshalling / Unmarshalling XML:

Using JAXB with Spring we can easily Marshall / Unmarshall the XML. Let us see how with an simple example.

Before starting the tutorial here are the definitions for a few technical words:
Spring's Object/XML (In short O/X) Mapping - Is the act of converting an XML document to and from an object.
Marshaller - Responsible for serializing an object (graph) to XML.
Unmarshaller - Deserializes the XML to an object graph.

Getting the JAXB Eclipse plug-in:
1. Download JAXB Eclipse Plug-In (jaxbbuilder.2.0.1581.zip) from
JAXB Eclipse Plug-in

2. Unzip the plug-in and copy the com.viewstreet.java.eclipse.jaxbplugin_0.1.0 folder into your eclipse plug-in folder (../eclipse/plugins). Restart eclipse if already open.

3. You also need to get the following jar files and set in your Eclipse classpath
jaxb-xjc-2.2.jar
jaxb-impl-2.2.jar
jaxb-api-2.2.jar

4. Create new project (Name: xmlBindingProj). Choose “Java XML Binding Project” in the new project creation wizard.



5. Create a XML Schema (.xsd) file customer.xsd: Example

<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="shiporder">
 <xs:complexType>
  <xs:sequence>
   <xs:element name="orderperson" type="person" />
   <xs:element name="shipto" type="person" />
   <xs:element name="item" type="itemDetail" maxOccurs="unbounded" />
  </xs:sequence>
  <xs:attribute name="orderid" type="xs:string" use="required" />
 </xs:complexType>
</xs:element>
<xs:complexType name="person">
 <xs:sequence>
  <xs:element name="first-name" type="xs:string" />
  <xs:element name="last-name" type="xs:string" />
  <xs:element name="address" type="xs:string" />
  <xs:element name="city" type="xs:string" />
  <xs:element name="state" type="xs:string" />
  <xs:element name="zip" type="xs:string" />
  <xs:element name="phone" type="xs:string" />
 </xs:sequence>
</xs:complexType>
<xs:complexType name="itemDetail">
 <xs:sequence>
  <xs:element name="title" type="xs:string" />
  <xs:element name="note" type="xs:string" minOccurs="0" />
  <xs:element name="quantity" type="xs:positiveInteger" />
  <xs:element name="price" type="xs:decimal" />
 </xs:sequence>
</xs:complexType>
</xs:schema>

6. Right click on the customer.xsd file on the project explorer and choose “Generate-> JAXB Classes”

7. Give the package name in the following dialog box as "com.mycomp.jaxbexample.order"


You can see the following classes created in the package with JAXB annotations.
ItemDetail.java
ObjectFactory.java
Person.java
Shiporder.java

8. Create the applicationContext.xml file under src directory as given below:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

 <bean id="jaxbConverter" class="com.mycomp.jaxbexample.client.JaxbConverter">
  <property name="marshaller" ref="jaxb2Marshaller"></property>
  <property name="unmarshaller" ref="jaxb2Marshaller"></property>
 </bean>

 <bean id="jaxb2Marshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
  <property name="classesToBeBound">
   <list>
    <value>com.mycomp.jaxbexamples.order.ItemDetail</value>
    <value>com.mycomp.jaxbexamples.order.ObjectFactory</value>
    <value>com.mycomp.jaxbexamples.order.Person</value>
    <value>com.mycomp.jaxbexamples.order.Shiporder</value>
   </list>
  </property>
 </bean>
</beans>

9. Create the following two java files and execute the ShipOrderClient.java
ShipOrderClient.java

package com.mycomp.jaxbexample.client;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.mycomp.jaxbexamples.order.ItemDetail;
import com.mycomp.jaxbexamples.order.Person;
import com.mycomp.jaxbexamples.order.Shiporder;

public class ShipOrderClient {

 /**
  * @param args
  */
 public static void main(String[] args) {
  ApplicationContext appContext = null;
  JaxbConverter converter = null;
  Shiporder shiporder = null;
  Shiporder shipOrderFrmXml = null;
  String fileName = "shipOrder.xml";
  
  appContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  converter = (JaxbConverter)appContext.getBean("jaxbConverter");
  shiporder = getShiporder(); 
  //Write the Java object as XML
  converter.marshallShipOrder(fileName, shiporder);
  //Read the XML file and load to the Java object
  shipOrderFrmXml = converter.unMarshallShipOrder(fileName);
  printShipOrder(shipOrderFrmXml);
  System.out.println("XML Created");
 }

 private static void printShipOrder(Shiporder shipOrderFrmXml) {
  String orderId = null;
  Person orderPerson = null;
  Person shipToPerson = null;
  List itemDetailList = null;
  
  orderId = shipOrderFrmXml.getOrderid();
  orderPerson = shipOrderFrmXml.getOrderperson();
  shipToPerson = shipOrderFrmXml.getShipto();
  itemDetailList = shipOrderFrmXml.getItem();
  
  StringBuffer sb = new StringBuffer();
  sb.append("The Order ID : ").append(orderId).append("\n");
  sb.append("The order peroson :").append("\n");
  sb.append("\tFirst Name: ").append(orderPerson.getFirstName()).append("\n");
  sb.append("\tLast Name: ").append(orderPerson.getLastName()).append("\n");
  sb.append("\tAddress: ").append(orderPerson.getAddress()).append("\n");
  sb.append("\tCity: ").append(orderPerson.getCity()).append("\n");
  sb.append("\tState: ").append(orderPerson.getState()).append("\n");
  sb.append("\tPhone: ").append(orderPerson.getPhone()).append("\n");
  
  sb.append("The ship to peroson :").append("\n");
  sb.append("\tFirst Name: ").append(shipToPerson.getFirstName()).append("\n");
  sb.append("\tLast Name: ").append(shipToPerson.getLastName()).append("\n");
  sb.append("\tAddress: ").append(shipToPerson.getAddress()).append("\n");
  sb.append("\tCity: ").append(shipToPerson.getCity()).append("\n");
  sb.append("\tState: ").append(shipToPerson.getState()).append("\n");
  sb.append("\tPhone: ").append(shipToPerson.getPhone()).append("\n");
  
  for(ItemDetail itemDetail : itemDetailList){
   sb.append("Items: ").append("\n");
  }
  
 }

 private static Shiporder getShiporder(){
  Shiporder so = null;
  Person orderPerson = null;
  Person shiptoPerson = null;
  List itemDetailList = null;
  ItemDetail itemDetailTv = null;
  ItemDetail itemDetailGame = null;
  String orderId = null;
  
  orderPerson = new Person();
  orderPerson.setFirstName("Munnaibai");
  orderPerson.setLastName("MBBS");
  orderPerson.setAddress("4415 Crosshaven Dr.");
  orderPerson.setCity("Durham");
  orderPerson.setState("NC");
  orderPerson.setZip("12345");
  orderPerson.setPhone("123-456-4321");
  
  shiptoPerson = new Person();
  shiptoPerson.setFirstName("Mr Clean");
  shiptoPerson.setLastName("Patient");
  shiptoPerson.setAddress("3344 Hell Blvd");
  shiptoPerson.setCity("Greensboro");
  shiptoPerson.setState("NC");
  shiptoPerson.setZip("34786");
  shiptoPerson.setPhone("666-555-9999");
  
  itemDetailTv  = new ItemDetail();
  itemDetailTv.setTitle("LG Television");
  itemDetailTv.setNote("1080 Px HDTV");
  itemDetailTv.setQuantity(new BigInteger("2"));
  itemDetailTv.setPrice(new BigDecimal(600));
  
  itemDetailGame = new ItemDetail();
  itemDetailGame  = new ItemDetail();
  itemDetailGame.setTitle("Sony");
  itemDetailGame.setNote("PS III");
  itemDetailGame.setQuantity(new BigInteger("3"));
  itemDetailGame.setPrice(new BigDecimal(800));
  
  itemDetailList = new ArrayList();
  itemDetailList.add(itemDetailTv);
  itemDetailList.add(itemDetailGame);
  
  orderId = "100001";
  
  so = new Shiporder();
  so.setOrderperson(orderPerson);
  so.setShipto(shiptoPerson);
  so.setItem(itemDetailList);
  so.setOrderid(orderId);
  
  return so;
 }
}

JaxbConverter.java

package com.mycomp.jaxbexample.client;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.xml.bind.JAXBException;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.springframework.oxm.Marshaller;
import org.springframework.oxm.Unmarshaller;
import org.springframework.oxm.XmlMappingException;

import com.mycomp.jaxbexamples.order.Shiporder;

public class JaxbConverter {

 private Marshaller marshaller;
    private Unmarshaller unmarshaller;

    public void setMarshaller(Marshaller marshaller) {
        this.marshaller = marshaller;
    }

    public void setUnmarshaller(Unmarshaller unmarshaller) {
        this.unmarshaller = unmarshaller;
    }

    public void marshallShipOrder(String fileName, Shiporder shiporder) {
        FileOutputStream os = null;
        try {
            os = new FileOutputStream(fileName);
            this.marshaller.marshal(shiporder, new StreamResult(os));
        } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (XmlMappingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
            if (os != null) {
                try {
     os.close();
    } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
            }
        }
    }

    public Shiporder unMarshallShipOrder(String fileName) {
     Shiporder shiporder = null;
     FileInputStream is = null;
        try {
            is = new FileInputStream(fileName);
            shiporder = (Shiporder) this.unmarshaller.unmarshal(new StreamSource(is));
            
        } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (XmlMappingException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
            if (is != null) {
                try {
     is.close();
    } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
            }
        }
        return shiporder;
    }

}

Multiple columns in Oracle IN clause

How to use more than one column in Oracle IN clause:

CREATE TABLE t1 (id VARCHAR2(10), version NUMBER(2,1), name VARCHAR2(20));

INSERT INTO t1 VALUES ('1001', 1.1 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.2 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.3 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.4 ,'FIRST ELEMENT');

INSERT INTO t1 VALUES ('1002', 1.1 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.2 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.3 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.4 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.5 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.6 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.7 ,'SECOND ELEMENT');

SELECT id, version, name FROM t1 WHERE (id, version) IN ( (1001,1.1), (1002,1.6) );