java.sql.SQLSyntaxErrorException: ORA-01722: Invalid numeric value. Error [Closed]

Asked 2 years ago, Updated 2 years ago, 146 views

Do you want to improve this question?Edit your post to clarify the issue you are trying to resolve by adding details.

Closed 4 years ago.

4 years ago

java.sql.SQLSyntaxErrorException: ORA-01722: Invalid numeric value. error.I wanted to create a system to load the CSV file and fill in the database, and I got the above error on the way.

CSV Write Controller

protected void doPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {
        // TODO Auto-generated method stub
        CSVReader csvReading;
        String fileName;
        T001ItemDao;
        String nextPage="";

        String name = request.getParameter("name");
        String code = request.getParameter("code");
        // // Get Amount
        String unitPrice=request.getParameter("unitPrice");
        // acquisition of quantities
        String count = request.getParameter("count");
        String isPR = request.getParameter("isPR");
        String img = request.getParameter("image");
        ServletContext con=getServletConfig().getServletContext();

        csvReading = new CSVReader();
        csvReading.csvAdd (request, con);

        List<List<String>>csvResult=csvReading.read();


            for(inti=4;i<csvResult.size();i++){
                if(null!=csvResult.get(i)){
                    for(int j=0;j<csvResult.get(i).size();j++){
                        System.out.println("csvResult" + csvResult.get(i).get(j));
                        if(0==(i%4)){

                            try{
                                itemDao = new T001 ItemDao();
                                itembean = itemDao.update(code, name, unitPrice, count);
                                request.setAttribute("itembean", itembean);
                                nextPage="/list.jsp";
                            } catch(ClassNotFoundExceptione){
                                // TODO Auto-Generated Catch Blocks
                                e.printStackTrace();
                            } catch(SQLExceptione){
                                // TODO Auto-Generated Catch Blocks
                                e.printStackTrace();
                            }

                        } else {
                            try{
                                itemDao = new T001 ItemDao();
                                int result = itemDao.addItem(name, unitPrice, count, isPR, img);
                                if(result==1){
                                    nextPage="/list.jsp";
                                } else{
                                    nextPage="/add.jsp";
                                }
                            } catch(ClassNotFoundExceptione){
                                // TODO Auto-Generated Catch Blocks
                                e.printStackTrace();
                            } catch(SQLExceptione){
                                // TODO Auto-Generated Catch Blocks
                                e.printStackTrace();
                            }
                        }
                    }
                }

            }

        System.out.println("csvResult"+csvResult);
    }

write to the CSV write class

public List<List<String>>read(){

        // Create Return List Box
        List<List<String>>ret=new ArrayList<List>String>>();
        if(fileName!=null){
            System.out.println("fileName" + fileName);
            String inputCsvFile="C:\\pleiades\\workspace\\hasuike\\jspServlet\\WebContent\\csv\\test1.csv";
            File csv = new File (inputCsvFile);
            System.out.println(csv);

            BufferedReader br=null;

            try{
                intlineCount = 0;
                // file opening
                 br = new BufferedReader (new FileReader(csv));

                // Load num lines (all lines if 0)
                String line="";
                int idx = 0;
                while((line=br.readLine())!=null){
                    lineCount++;

                    // Create a box containing a single line
                    List<String>tmpList=new ArrayList<String>();

                    // string index
                    int idxFrom = 0;
                    int idxTo = 0;
                    // string length
                    while(true){

                        // Finished after last item is retrieved
                        if(idxFrom>line.length()){
                            break;
                        }

                        // Obtain the next separator position
                        idxTo=line.indexOf(", ", idxFrom);

                        // Get last item if separator is not found
                        if(idxTo==-1){
                            idxTo=line.length();
                        }

                        // string acquisition
                        String word = line.substring(idxFrom,idxTo);

                        // Storing a String
                        tmpList.add(word);

                        // Update Search Start Location
                        idxFrom = idxTo+1;
                    }

                    // Store one line of data in return list
                    ret.add(tmpList);

                    // Load finished when num is exceeded.If num is 0, read the whole amount.
                    if(lineCount!=0&idx>lineCount){

                        break;
                    }
                }
            } catch(FileNotFoundExceptione){
                e.printStackTrace();
            } catch(IOExceptione){
                e.printStackTrace();
            } US>finally
                try{
                    if(br!=null){
                        br.close();
                    }
                } catch(IOExceptione){
                    e.printStackTrace();
                }
            }
        }
        return;
    }

I wrote.When I looked into the contents of the error, I found out that it was an error that occurred when the type conversion failed.
The first element of the array may be empty, for example, [,stabba, 400, 40, 1], [518, green, 444, 35, 0]], and so on.
I wonder if that caused an error, but I don't know how to fix it...
(It may not be that part in the first place)

How can I fix it?

java jsp servlet

2022-09-30 17:15

2 Answers

If there is an error, you should paste the error log.
Also, it is better to show the data you read.

If the number is invalid or empty in the array, it may be an empty String character when you read the empty text, and you are trying to insert it directly into the database, resulting in a type conversion error.
Check the table definition.
For non-empty cases, implicit type conversion is probably working.

どうやって直せば・・・については、ファイルを読み込んだときに空文字を変換するか、DBにINSERTするときに空文字を変換するか、くらいかと思います。
If it means, < code > / character string acquisition or something a blank character decision to the, if the school after < / code > I say from the displayed code for something you are to make of the letter, but Specifically I'm not sure how much we don't follow that all of the processing. < / >

"By the way, I get the impression that ""CSV write"" means writing data to a CSV file, but I think it means writing a CSV file to a DB."


2022-09-30 17:15

Please include an error message.

java.sql.SQLSyntaxErrorException: ORA-01722: Invalid number

occurs when an internal type conversion to an Oracle character type → numeric type fails.
Type conversion fails if the string to be converted contains characters other than numbers, decimal places, and non-signs.


2022-09-30 17:15

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.