Results 1 to 15 of 15
  1. #1
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question Exception in thread "main" java.lang.NumberFormatException:input string: "060320

    Hi

    The signature of oracle function is as follows :-
    F_VLD_VALUE(schoolNO. NUMBER,year NUMBER,countyID number);

    i am calling this function from java , passing param values
    for schoolNO. 1202008
    year 2009
    countyID 06032010124457

    java program declares these variables has follows
    int schoolNO;
    int year;
    String countyID;

    but function needs countyID to be number type .

    Bec., of this i get this error .

    Anybody can help me , how to get the exact countyID into the function
    06032010124457


    Exception in thread "main" java.lang.NumberFormatException: For input string: "06032010124457"
    at java.lang.NumberFormatException.forInputString(Unk nown Source)
    at java.lang.Integer.parseInt(Unknown Source)
    at java.lang.Integer.valueOf(Unknown Source)
    at test.test.main(test.java:111)

  2. #2
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    5

    Default

    Integers max value is 2147483647, so 6032010124457 is bigger :)
    Use long instead of an int (Long.parseLong)

  3. #3
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    I have tried using

    Long.parseLong("06032010124457")
    but it returns 06032010124457

    I want the whole number 06032010124457 to be passed to the function including prefix 0 infront of the number

    any solution is appreciated .

  4. #4
    eRaaaa is offline Senior Member
    Join Date
    Oct 2010
    Location
    Germany
    Posts
    787
    Rep Power
    5

    Default

    Why do you need that? Thats not possible and makes no sense(or you have to use Strings!)

  5. #5
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    I cannot change the oracle function signature
    The signature of oracle function is as follows :-
    F_VLD_VALUE(schoolNO. NUMBER,year NUMBER,countyID number);

    In java , the countyID is declared as string type and it holds the value 06032010124457

    I have to pass the same value to the param countyID in the function , but it is number type
    proc_stmt.setLong(4,countyID);
    when i use setLong bec., setInt cannot take such a big number , it is removing the 0 and considers 6032010124457
    which is a wrong value to execute the function and it is giving oracle errors
    ORA-01403: no data found
    ORA-06512: at "F_VLD_VALUE", l

    How can i send a string value to a variable which is number datatype in an oracle function ?
    Is it possible or the only solution is to ask the DBA to change the function param to VARCHAR2 type for the java code to work ??

    anyhelp is appreciated..before i go to the DBA

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Yes, and that Oracle function will not take something with a leading zero.
    NUMBERs do not have leading zeros.
    So it is not a wrong value.

    So, it is the Oracle procedure that is incorrect somehow, or the way you are calling it.

  7. #7
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question

    Yes Sir, You are right . Oracle accepts the number without 0 in front of it .

    Pls look at my code and let me know , why i am getting this error .
    SQL Exception - Query Two
    ORA-01403: no data found
    ORA-06512: at "F_VLD_VALUE", line 179
    ORA-06512: at line 1


    Java Code:
    //global declaration sec 
    public static String countyID;
    :
    ://other declarations are here
    public static void main(String[] args) throws SQLException 
    { 
        //database url = is here
    
      Scanner scan = new Scanner(System.in);
      boolean flagID = f_cID(scan);
    while(!flagID)
    {
    	System.out.println("Invalid County Value");
    	scan = new Scanner(System.in);
    	flagID = f_cID(scan);
    }
      if(flagID) 
    {  
          System.out.println("year :"+ year); 
          System.out.println("countyID :"+ cID); 
       // Call a function with two IN parameters; the function returns a cursor
          CallableStatement proc_stmt = null;
          String abc = "BEGIN " +
          "? := CASE F_VLD_VALUE(?,?,?) " +
          "WHEN TRUE THEN 1 " +
          "ELSE 0 " +
          "END;" + 
          "END; " ;
         
    		try {
    			proc_stmt = connection.prepareCall(abc);			              
    			// Register the type of the return value
    			proc_stmt.registerOutParameter(1, java.sql.Types.INTEGER); 	   
    			proc_stmt.setString(2,schoolNO);
    			proc_stmt.setInt(3,year);
    			[COLOR="Red"]proc_stmt.setLong(4,Long.parseLong(cID));[/COLOR]		
    //Note:- How to send a string value cID to a function whose param is number type .
    since the cID value is large ..i tried to send it using setLong and its not working  ...				
    //			Execute and retrieve the returned value
    			proc_stmt.execute();
    			int funcRetValue = proc_stmt.getInt(1);
    			if (funcRetValue == 1) {
    			   retValue = true;
    			} else {
    			   retValue = false;
    			}			
    			//print the results	
    			System.out.println("the boolean value is :"+retValue);
    			System.out.println(" ");		
    			proc_stmt.close();         
    		}catch (SQLException sqlException1) {
    			System.out.println("SQL Exception - Query Two ");
    			System.out.println(sqlException1.getMessage());
    		}
    		if(retValue == false){
    			System.out.println("User entered Opcert Value is not present in the time frame requested.");
    		}
    }
    }//end of main
    
    public static boolean f_cID(Scanner scan) {
    	boolean loopUntilGoodNumber = false;
    	System.out.println("Enter the countyID #1:");
    	 String countyID = scan.next();
    	int count = countyID.length();
    	if(count == 8){
    		loopUntilGoodNumber = isIntNumber(countyID);
    		if(loopUntilGoodNumber)
    		{
    			cID = countyID; 
    			System.out.println("the value of countyID is : "+cID);
    		}
    	}
    	else if(count == 14){
    		loopUntilGoodNumber = isIntNumber(countyID);
    		if(loopUntilGoodNumber)
    		{
    			cID = countyID; 
    			System.out.println("The value of countyID is :"+cID);
    		}		
    	}else
    	{
    	   loopUntilGoodNumber = false;
    	}	
    	return loopUntilGoodNumber;
    }
    Anyhelp..i have been trying this from yesterday ..pls help me .
    Last edited by renu; 04-08-2011 at 03:31 PM. Reason: quote tags changed to code tags

  8. #8
    Fubarable's Avatar
    Fubarable is offline Moderator
    Join Date
    Jun 2008
    Posts
    19,316
    Blog Entries
    1
    Rep Power
    26

    Default

    Moderator edit: quote tags changed to code tags. To the OP, please use code tags with code if you want your code to be readable and increase your chances that others will read it.

  9. #9
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Quote Originally Posted by renu View Post
    Yes Sir, You are right . Oracle accepts the number without 0 in front of it .

    Java Code:
    Pls look at my code and let me know , why i am getting this error .
    SQL Exception - Query Two 
    ORA-01403: no data found
    ORA-06512: at "F_VLD_VALUE", line 179
    ORA-06512: at line 1
    This sounds like an issue with your PL/SQL procedure and not with your Java code. Oracle is telling you that the problem is in "F_VLD_VALUE" at line 179.

    Do you have access to the source code of this procedure? Can you see what the SQL is? If so, can you run that SQL from SQLPlus and get the results desired? Whatever the SQL is, it is not capturing the NO_DATA_FOUND Exception properly. If the values you pass the procedure return no results back, what should the procedure return? A FALSE?

  10. #10
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Question

    Hello Sir

    when i executed the function from sql Plus it works and returns true


    Java Code:
    set serveroutput on;
    declare
    
    vb_ans boolean;
    
    begin
    
    vb_ans := F_VLD_VALUE
    ('1401008',2009,06032010124457);
    
    if vb_ans = TRUE then
    dbms_output.put_line('TRUE');
    else
    dbms_output.put_line('FALSE');
    
    end if;
    end;


    TRUE

    PL/SQL procedure successfully completed.

    When i run the java code in debug mode..
    it comes to

    Java Code:
    try {
    			proc_stmt = connection.prepareCall(abc);			              
    			// Register the type of the return value
    			proc_stmt.registerOutParameter(1, java.sql.Types.INTEGER); 	   
    			proc_stmt.setString(2,schoolNO);
    			proc_stmt.setInt(3,year);
    			proc_stmt.setLong(4,Long.parseLong(cID));		
    //Note:- How to send a string value cID to a function whose param is number type .
    since the cID value is large ..i tried to send it using setLong and its not working  ...				
    //			Execute and retrieve the returned value
    			[COLOR="Purple"]proc_stmt.execute(); //it comes till this line and goes to the catch block [/COLOR]

    I am not sure..but i feel converting a string value to long and passing it to the function ...their is some problem .

  11. #11
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Your PL/SQL procedure signature does not match your parameter list

    You said:

    Java Code:
    F_VLD_VALUE(schoolNO. [B]NUMBER[/B],year NUMBER,countyID number);
    So, the first parameter is a NUMBER, but you have a Java String being set to the first parameter. SQLPlus maybe implicitly converting that String to a Number for you, but I am not sure if Java will do that.

    Please, also list the declaration of 'cID'. Is that a String?
    You can always parse the String to a long, then pass the long variable to the procedure if you feel there maybe an issue with this.

    Converting it can be done like this:

    Java Code:
    $ cat StringToLong.java
    import java.lang.*;
    
    public class StringToLong
    {
    
     public static void main(String[] args)
     {
        String inputStr = "06032010124457";
        long inputLong = 0;
        try {
           inputLong = Long.parseLong(inputStr);
        } catch (NumberFormatException ex) {
            ex.printStackTrace();
        }
    
        System.out.println("inputStr = " + inputStr);
        System.out.println("inputLong = " + inputLong);
    
     }
    
    }
    $ java StringToLong
    inputStr = 06032010124457
    inputLong = 6032010124457

  12. #12
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Unhappy

    The function signature is changed
    F_VLD_VALUE(schoolNO. VARCHAR2,year NUMBER,countyID number);

    tahts why i used ' ' for the first param value while passing .



    Java Code:
    //global declaration section
                                    public static String cID;
    
                   // with in main()
    
    try {        
          
                                 // calling the function and more code  
    			long countyValue = Long.parseLong(cID);
                                           proc_stmt.setLong(4,countyValue);
    these r the 3 statements and as said it is executing till

    proc_stmt.execute();

    and then going into the catch block and printing the error.

  13. #13
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    Sir

    Thank You ALL .. for supporting me.

    The code above works fine ....

    Thank Again .

  14. #14
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Here is a test I did to prove that PL/SQL can handle a Long parameter:

    Java Code:
    create or replace
    FUNCTION passalong (num_in IN NUMBER)
    RETURN BOOLEAN
    IS
    BEGIN
       IF num_in > 0 THEN
          RETURN TRUE;
       ELSE
          RETURN FALSE;
       END IF;
    
    END passalong;
    /
    
    
      1  declare
      2  vb_ans boolean;
      3  begin
      4  vb_ans := passalong(06032010124457);
      5  if vb_ans = TRUE then
      6  dbms_output.put_line('TRUE');
      7  else
      8  dbms_output.put_line('FALSE');
      9  end if;
     10* end;
    12:04:03 SQL> /
    TRUE
    
    PL/SQL procedure successfully completed.
    
    
      1  declare
      2  vb_ans boolean;
      3  begin
      4  vb_ans := passalong(-1);
      5  if vb_ans = TRUE then
      6  dbms_output.put_line('TRUE');
      7  else
      8  dbms_output.put_line('FALSE');
      9  end if;
     10* end;
    12:04:18 SQL> /
    FALSE
    
    PL/SQL procedure successfully completed.
    
    
         try {
          String inputStr = "06032010124457";
          long longVal = 0;
          try {
             longVal = Long.parseLong(inputStr);
          } catch (NumberFormatException ex) {
             ex.printStackTrace();
          }
    
          String sqlStmt  =
                "BEGIN  " +
                   " ? := CASE passalong(?)  " +
                        " WHEN TRUE THEN 1 " +
                        " ELSE 0 " +
                        " END; " +
                "END; ";
    
    
          // create the CallableStatement object
          CallableStatement cstmt = conn.prepareCall( sqlStmt );
    
          // set parameters
          cstmt.registerOutParameter(1, java.sql.Types.INTEGER);
          cstmt.setLong(2, longVal);
    
          // execute the query
          cstmt.execute();
          int retval = cstmt.getInt(1);
          System.out.println("Is " + longVal + " greater than zero? " + ( (retval == 1) ? true : false) );
         }
         catch (SQLException exc) {
          System.out.println("\nDatabase error: " + exc);
         }
    
    
    $ java PassALong
    Is 6032010124457 greater than zero? true
    I would suggest the following:

    1. Wrap your Long.parseLong in a try catch
    2. Print the value of your long variable after the parseLong and see what the value is before passing it to Oracle.
    3. Also, what version of Oracle are you running?

  15. #15
    renu is offline Senior Member
    Join Date
    May 2010
    Posts
    117
    Rep Power
    0

    Default

    Thank You So Much Sir

    I gained lot of knowledge today .Thank You for helping me understand , how to handle such errors and test them .

    Your Support is greatly appreciated .

Similar Threads

  1. Replies: 5
    Last Post: 06-22-2010, 03:51 PM
  2. Replies: 5
    Last Post: 04-12-2010, 03:39 PM
  3. jsp insert into database error(java.lang.NumberFormatException: For input string: "")
    By cypher_girl in forum JavaServer Pages (JSP) and JSTL
    Replies: 2
    Last Post: 12-22-2009, 03:14 AM
  4. Replies: 1
    Last Post: 11-20-2009, 07:58 PM
  5. Replies: 3
    Last Post: 06-07-2009, 12:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •