java - Oracle pl/SQL 数字或值错误

Question

您好，我在包中编写了一个程序来插入数据，如下所示:

create or replace package Proj2_student_reg_package is
    type data_cursor is ref cursor;

procedure add_students(sidIn IN students.sid%type, fnameIn IN students.firstname%type,
                        lnameIn IN students.lastname%type, statusIn IN students.status%type,
                        gpaIn IN students.gpa%type, emailIn IN students.gpa%type,
                        ret_message OUT varchar2);

end Proj2_student_reg_package;
/

create or replace package body Proj2_student_reg_package as

procedure add_students(sidIn IN students.sid%type, fnameIn IN students.firstname%type,
                        lnameIn IN students.lastname%type, statusIn IN students.status%type,
                        gpaIn IN students.gpa%type, emailIn IN students.gpa%type,
                        ret_message OUT varchar2)
is
begin
insert into students values(sidIn ,fnameIn,lnameIn,statusIn,gpaIn,emailIn);
COMMIT;
ret_message := 'Success';

EXCEPTION
WHEN OTHERS THEN
ret_message := 'Error';
end add_students;




end Proj2_student_reg_package;
​

我正在通过 java 程序调用:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;

public class AddStudent {
    public void addStudent(){
        Scanner line = new Scanner(System.in);
        try
        {       

            //Connection to Oracle server
            OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
            ds.setURL("jdbc:oracle:thin:@localhost:1521:xe");
            Connection conn = ds.getConnection("*****", "****");

            System.out.print("Enter the sid: ");
            String sid = line.next();
            System.out.print("Enter first name: ");
            String fname = line.next();
            System.out.print("Enter last name: ");
            String lname = line.next();
            System.out.print("Enter status: ");
            String status = line.next();
            System.out.print("Enter gpa: ");
            String gpa = line.next();
            System.out.print("Enter email: ");
            String email = line.next();


            CallableStatement cs = conn.prepareCall("{call Proj2_student_reg_package.add_students(?,?,?,?,?,?,?)}");

            cs.setString(1, sid);
            cs.setString(2, fname);
            cs.setString(3, lname);
            cs.setString(4, status);
            cs.setString(5, gpa);
            cs.setString(6, email);
            cs.registerOutParameter(7, Types.VARCHAR);

            cs.executeQuery();
            System.out.println(cs.getString(7));
            cs.close();
            //line.close();
        }
        catch (SQLException e) { System.out.println("SQLException " + e.getMessage());}
        catch (Exception e) {System.out.println(e);}
    }

}

我的学生表是:

create table students (sid char(4) primary key check (sid like 'B%'),
firstname varchar2(15) not null, lastname varchar2(15) not null, status varchar2(10) 
check (status in ('freshman', 'sophomore', 'junior', 'senior', 'graduate')), 
gpa number(3,2) check (gpa between 0 and 4.0), email varchar2(20) unique);

我将 java 程序中的值作为 sid 给出:B987, fname : akki, lname : aror, satus : junior , gpa : 3,email : xyz@gmail.com..

但这给了我一个错误:

SQLException ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

我尝试查找太多信息，但无法弄清楚错误出在哪里。谁能告诉我错误并帮助我修复错误。

Answer 1

在您的 CallableStatement cs 对象中，您需要从 cs.setString(5, gpa) 更改为 cs.setDouble (5, gpa); 因为gpa是数据库中的number类型。