java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversio

Question

I am trying to insert a record into the oracle database using a procedure. The record is inserted but an exception occurs.

public class Myproc  extends StoredProcedure {


public ThresholdOperationsSP(JdbcTemplate jdbcTemplate, String sql) {
        super(jdbcTemplate, sql);
        // Declare IN params
        declareParameter(new SqlParameter("i_unique_col", Types.VARCHAR));
        declareParameter(new SqlParameter("i_action", Types.VARCHAR));
        declareParameter(new SqlParameter("i_table", Types.VARCHAR));
        declareParameter(new SqlParameter("i_columns", Types.VARCHAR));
        declareParameter(new SqlParameter("i_values", Types.VARCHAR));
        declareParameter(new SqlParameter("i_where_col", Types.VARCHAR));
        declareParameter(new SqlParameter("i_where_values", Types.VARCHAR));
        declareParameter(new SqlParameter("i_connection", Types.VARCHAR));
        // Declare OUT params
        declareParameter(new SqlOutParameter("o_unique_id", Types.INTEGER));
        declareParameter(new SqlOutParameter("o_proc_msg", Types.VARCHAR));
        declareParameter(new SqlOutParameter("o_proc_code", Types.INTEGER));
        declareParameter(new SqlOutParameter("o_sql", Types.VARCHAR));


    compile();
}

@SuppressWarnings("unchecked")
public Map<String, Object> execute(String connectionName, String tableName, String action,
        String unqiueIdColumn, String colNames, String newValues, String whereColNames, String whereColValues)  {
    // Process input params & assign to map
    Map<String, Object> inParams = new HashMap<String, Object>();

    inParams.put("i_unique_col", unqiueIdColumn);
    inParams.put("i_action", action);
    inParams.put("i_table", tableName);
    inParams.put("i_columns", colNames);
    inParams.put("i_values", newValues);
    inParams.put("i_where_col", whereColNames);
    inParams.put("i_where_values", whereColValues);
    inParams.put("i_connection", connectionName);

    // Execute SP
    Map<String, Object> resultSummary = execute(inParams);
    System.out.println(resultSummary);
    return resultSummary;
}

calling the above code as:

Map<String, Object> resultsSummary = new Myproc(jdbcTemplate,"MYPROCEDURE").execute("CON1","table1","INSERT","COL1","col2","33",null,null);

The above code calls the following procedure;

     create or replace procedure          MYPROCEDURE
(
    i_unique_col      in          varchar2,
    i_action          in          varchar2,
    i_table           in          varchar2,
    i_columns         in          varchar2,
    i_values          in          varchar2,
    i_where_col       in          varchar2,
    i_where_values    in          varchar2,
    i_connection      in          varchar2,
    o_unique_id       out         number,
    o_sql             out         varchar2,
    o_proc_msg        out         varchar2,
    o_proc_code       out         number 
)
as

    p_c_proc_name constant varchar2(50):= 'MYPROCEDURE';  

    p_v_err_cd              number;
    p_v_err_msg             varchar2(500);
    p_v_result              varchar2(1);
    p_v_timestam            timestamp with time zone;
    p_v_values              varchar2(32767);
    p_v_sql                 varchar2(32767);
    p_v_where               varchar2(32767);

begin

    dbms_application_info.set_module (module_name=> p_c_proc_name, action_name=> 'PROCEDURE INSERT-EDIT ACTIONS');

    if upper(i_connection) = 'CON1' then

        p_v_values := ''''||replace(i_values,',',''',''')||'''';

        if upper(i_action) = 'INSERT' then

            case when i_unique_col is not null then
                p_v_sql := 'insert into uno_comp.'||i_table||' ('||i_columns||' ) values ('||p_v_values||') returning '||i_unique_col||' into :1' ;
                execute immediate p_v_sql using out o_unique_id ;
            else
                p_v_sql :='insert into uno_comp.'||i_table||' ('||i_columns||' ) values ('||p_v_values||')';
                execute immediate p_v_sql;
            end case;                                                     
            commit;
        end if;            
        p_v_result := uno_comp.ulog('INFO', p_c_proc_name, 'Successfully performed  insert-edit actions '|| p_v_sql, null, null, null );
        o_proc_msg  := 'procedure Edit Action Complete.';
        o_proc_code := 0;
   end if; 
end MYPROCEDURE;

the error occurs on execution of the statement

Map<String, Object> resultSummary = execute(inParams);

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call MYPROCEDURE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error\nORA-06512: at line 1\n"

although the record is successfully inserted into the database, error occurs after the statement is executed.

Pratik K Chatterjee Pratik K Chatterjee · Accepted Answer · 2017-08-23T12:01:12

You should use JDBC CallableStatements to call a procedure. How to Use Callable Statement

A working example will be as follows ---

import java.sql.*;  
public class Proc 
{  
    public static void main(String[] args) throws Exception
    {     
        Class.forName("oracle.jdbc.driver.OracleDriver");  
        Connection con=DriverManager.getConnection(  
        "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  

        CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");  
        stmt.setInt(1,1011);  
        stmt.setString(2,"Amit");  
        stmt.execute();  

        System.out.println("success");  
    }  
}

The structure of the procedure is as follows ---

create or replace procedure "INSERTR"  
(id IN NUMBER,  
name IN VARCHAR2)  
is  
begin  
insert into user420 values(id,name);  
end;  
/

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversio

1 Answers