0 Replies Latest reply: Sep 6, 2013 12:39 AM by 929871 RSS

    Extracting the data from database using Java

    929871

      Hi

       

      I am trying to extract the data from database using my Java code. But i am getting with the below exception

       

      Exception in thread "main" java.lang.NoClassDefFoundError: oracle/jdbc/oci8/OCIDBAccess

          at Reader.main(Reader.java:31)

      Caused by: java.lang.ClassNotFoundException: oracle.jdbc.oci8.OCIDBAccess

       

      I am using

       

      Oracle 10g

      java 1.6

       

      I have the below Jars under my class-path

       

      xdb.jar

      xmlparserv2.jar

      ojdbc6.jar

       

      Database file

       

      CREATE TABLE JDBC_XML
      (
      TESTFIELD XMLTYPE
      )
      INSERT INTO JDBC_XML(TESTFIELD) VALUES('<test><set><id>123</id><names>emp</names></set></test>');


      Java code :


      import java.io.*;

      import java.sql.*;

      import org.w3c.dom.*;

      import oracle.xdb.XMLType;

      import oracle.jdbc.*;

              public class Xml { 

              public static void main(String[] args) throws Exception{

                     

                      Class.forName ("oracle.jdbc.OracleDriver");

                      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "system", "ABCD123abcd");

                      OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("SELECT A.TESTFIELD FROM JDBC_XML A");

                      ResultSet rset = stmt.executeQuery();

                      OracleResultSet orset = (OracleResultSet) rset;

                      Document xml_column=null;

                      while (orset.next()) {

                                      XMLType poxml = XMLType.createXML(orset.getOPAQUE("TESTFIELD"));

                                      //print the full xmltype

                                      System.out.println(poxml.getStringVal());                              

                                      //below method is deprecated

                                      xml_column = (Document)poxml.getDOM();

                                    //  xml_column = (Document)poxml.getDocument();

                                      }

                      /* We have only one row in the XMLTYPE column, so the while loop will get executed once */

                      /* Let us now try to print the value of id tag */

                     

                      /* Print the root element */

                      System.out.println("Root element: " + xml_column.getDocumentElement().getNodeName());

                      /* Print ID tag. You should try and use XPath approaches rather */

                      NodeList nodeList = xml_column.getElementsByTagName("set");

                      /* Loop through the "set" node */

                      for (int i = 0; i < nodeList.getLength(); i++) {

                      Node node = nodeList.item(0);               

                      Element element = (Element) node;

                      NodeList nodeList_1 = element.getElementsByTagName("0").item(0).getChildNodes();

                      Node node_2 = nodeList_1.item(0);

                      System.out.println(node_2.getNodeValue());               

                      }               

                      /* Close all DB related objects */

                      orset.close();

                      stmt.close();

                      conn.close();

              }

      }

       

      Any help will be much appreciated.

       

      Thanks

      Karthick