Hi Friends,
I am having problem with the above code snippet. Initially it woked fine. But now it is giving an error of java.sql.SQLException: ORA-01722: invalid number.
How can I resolve the exception?
Thanks.

try
	{  String ein=(String)session.getAttribute("EIN");
	   System.out.println("inside try block");
       Class.forName("oracle.jdbc.driver.OracleDriver");
    
	con=DriverManager.getConnection("jdbc:oracle:thin:@10.9.16.2:1521:TESTDB","HE","HE123");
	Statement st=con.createStatement();
	ResultSet rs=st.executeQuery("select distinct REV_OWNER from FBD_USER_AUTHENTICATION where user_ein="+ein+"") ; 
	System.out.println("after executing the query");
	 
	while (rs.next())
	{ j=j+1;%>
	<tr><td align="center"><font size="default" color="indigo">
	<%=rs.getInt("rev_owner")%></font></td>
	<td align="center"><input type="checkbox" name="check" value="<%=rs.getInt("rev_owner")%>"></tr> 
	<%} %> 
	</table>
	<table width="50%" align="center" border="1" rules=ALL>
	<tr><td align="center"><input type="submit" name="b1" value="Delete" class="btn"></td></tr>
	
	<input type="hidden" name="recordcount" value="<%=j%>">
	</table>
	</form>
	
	
	<%  if (!(con == null) || !con.isClosed())
		     {
	           con.close();
	         }
	       } catch (Exception e1) {
		    System.out.println("Error--->"+e1.toString());
	                 out.println("Error Detected----->"+e1.toString());
	
		    if (!(con == null) || !con.isClosed())
		     {
	                 con.close();
		     }
	
	        } %>

And your question is?

That exception usually comes when trying to insert to (or query in a where clause against) a number field using letters and not digits.

Edit: I.E. 'Z' instead of 0

This has happened to me, usually when the variable is null. It might think the value is "Null", which is not a number.

And that is still attempting to insert text into a number field.

You shouldn't be using scriptlets anyway. Those are maintenance nightmares. Break that stuff out into a bean.

Edit: And, if you were to use PreparedStatement (like you definately should be) and not cobbling together a statement like this (which is error-prone and an exterme security risk, it makes SQL Injection attacks just oh so easy), you could avoid this problem all together.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.