Selecting Data from Oracle

[J James]

Hi all,
Can someone please tell me what am I doing wrong here? What I'm trying to do is extract data from Oracle with functions and into XML
It keeps greeting me with NullPointerException error.

This page is excuted first;

   String p_error = req.getParameter("ERROR");
    	
    try {
      // Load the database driver
      Class.forName("oracle.jdbc.OracleDriver");
      // Get a Connection to the database
      connection = DriverManager.getConnection(connectionURL, "user", "pass");
      Data2 data = new Data2();
      //got list of event data
      HashMap<String, String> EventData = data.execXMLFunction("{ ? = call pa_dump_xml.fn_user_diary(p_musr_id_organiser)}");
              
      proc.execute();
      
      // show the inserted fields
      out.println("<table border= 1 width= 50%>");
      out.println("<tr><td>Event Date</td><td>'"+ p_musr_id_organiser +"'</td></tr>"); 

Then comes here to write XML

  public class Data2
{	
	Connection conn = null;
	CallableStatement callSp = null;
	String dbUrl = "jdbc:oracle:thin:@xxx.16.1.20:0000:xxxxx",
			dbUName = "user", dbPwd = "pass";
	ResultSet rs = null;
	DataSourceWrapper ds = null;
	Result result = null;
	
	private void dbSetup()
	{
		ds = new DataSourceWrapper();
		ds.setDriverClassName("oracle.jdbc.OracleDriver"); // ojdbc6 driver for ora 11gr2
		ds.setUrl(dbUrl);
		ds.setUser(dbUName);
		ds.setPassword(dbPwd);
		
	}public HashMap<String, String> execXMLFunction(String function)
	{		
		dbSetup();
		DOMParser domParser = new DOMParser();
		Integer p_musr_id_organiser = 0;
		//domParser.setValidationMode(false);
		//domParser.setPreserveWhitespace(true);
		HashMap<String, String> mapClientDetails = new HashMap<String, String>();
			
		try
		{
			DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
			Class.forName("oracle.jdbc.driver.OracleDriver"); // load oracle driver
			conn = ds.getConnection();// uses pooled conns in 'ConnectionPool' class.
			OracleCallableStatement oCallSp = (OracleCallableStatement) conn.prepareCall(function);
			oCallSp.registerOutParameter(1, OracleTypes.OPAQUE, "SYS.XMLTYPE");
			
			callSp.setInt(2, p_musr_id_organiser);
			oCallSp.execute();
			//ors = (OracleResultSet) callSp.executeQuery();
			XMLType xml = XMLType.createXML((OPAQUE) oCallSp.getOPAQUE(1));
			System.out.println("xml data = " + xml.getStringVal());
			domParser.parse(new StringReader(xml.getStringVal()));
			XMLDocument doc = domParser.getDocument();
			NodeList nL = doc.getElementsByTagName("*");
 
			for (int c = 0; c < nL.getLength(); c ++)
			{
				System.out.println("\n\nNode = " + nL.item(c).getNodeName());
				NamedNodeMap namedNodeMap = nL.item(c).getAttributes();
				
				for (int c2 = 0; c2 < namedNodeMap.getLength(); c2 ++)
				{
					mapClientDetails.put(namedNodeMap.item(c2).getNodeName(), 
							namedNodeMap.item(c2).getNodeValue());
					System.out.println("\natts = " + namedNodeMap.item(c2).getNodeValue());
				}
			}
			xml.close();
		}

		catch (SQLException e)
		{
			System.out.println("3. Exception in Data.exec : " + e);
			e.printStackTrace();
		}

		catch (Exception e)
		{
			System.out.println("4. Exception in Data.exec : " + e);
		}
		cleanUp();
		return mapClientDetails;
	}  

[Mark E Hansen]

So is the actual NullPointerException error you're getting a secret? At the very least, it will include the file and line where the error occurred, which will save folks a lot of time hunting through your code looking for what might be wrong.

[J James]

Thanks for your reply, sorry I couldn't post this before the Server went down.

  14-Feb-2010 04:03:55 org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jre6\bin;.;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:/Program Files/Java/jre6/bin/client;C:/Program Files/Java/jre6/bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files\CyberLink\Power2Go;C:\Program Files\TortoiseSVN\bin
14-Feb-2010 04:03:55 org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.j2ee.server:softoffice' did not find a matching property.
14-Feb-2010 04:03:55 org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
14-Feb-2010 04:03:55 org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 667 ms
14-Feb-2010 04:03:55 org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
14-Feb-2010 04:03:55 org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.24
14-Feb-2010 04:03:56 org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
14-Feb-2010 04:03:56 org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
14-Feb-2010 04:03:56 org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/32  config=null
14-Feb-2010 04:03:56 org.apache.catalina.startup.Catalina start
INFO: Server startup in 788 ms
4. Exception in Data.exec : java.lang.NullPointerException  

Mark E Hansen wrote:So is the actual NullPointerException error you're getting a secret? At the very least, it will include the file and line where the error occurred, which will save folks a lot of time hunting through your code looking for what might be wrong.

[Charles Owen]

Could the problem be line 36?

  
callSp.setInt(2, p_musr_id_organiser);  

I am not sure why you have a CallableStatement and an OracleCallableStatement, but your CallableStatement variable callSP is set to null at line 4.

[J James]

Thank you.
Indeed it was line 36 I've just commented it out and it's working also made the amendments what you suggested.
many thanks
JJ

Charles Owen wrote:Could the problem be line 36?

  
callSp.setInt(2, p_musr_id_organiser);  

I am not sure why you have a CallableStatement and an OracleCallableStatement, but your CallableStatement variable callSP is set to null at line 4.