This discussion is archived
1 Reply Latest reply: Jul 22, 2013 3:59 PM by rp0428 RSS

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

scottjhn Newbie
Currently Being Moderated

Oracle 11g on SUSE Linux 11.

 

I have a sql statement:

  insert into USER( FIRST_NAME,LAST_NAME,SCREEN_NAME,SECURITY_ANSWER1,SECURITY_QUESTION1,USERNAME,USER_EMAIL,USER_PASSWORD,USER_TYPE) values ('a','a','d','a','Q','a','d@c.com','$2a$10$5PAXUbYD5GtnHa9IhfYwiesYlf1LD4ccOhn6RC9rTsHz.4Gho5Hvu','P' ) returning USER_ID into :USER_ID


If I execute it DIRECTLY on SQLPLUS, it works.


Now delete the row.  Instead, pass the sql via jave to stored procdure:


String sqlStatement = "insert into USER( FIRST_NAME,LAST_NAME,SCREEN_NAME,SECURITY_ANSWER1,SECURITY_QUESTION1,USERNAME,USER_EMAIL,USER_PASSWORD,USER_TYPE) values ('a','a','d','a','Q','a','d@c.com','$2a$10$5PAXUbYD5GtnHa9IhfYwiesYlf1LD4ccOhn6RC9rTsHz.4Gho5Hvu','P' ) returning USER_ID into :USER_ID"; 

           

                conn = ConnectionHelper.getConnection(connectionKey, profile);                 

                procedureCall = "begin pkg_utility.sp_save_test(?,?); end;";

                call = conn.prepareCall(procedureCall);   

                call.registerOutParameter(1, java.sql.Types.INTEGER);

                call.setString(2, sqlStatement);  

               

                call.execute();    

               

It gives an error: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

 

Oracle procedure:

 

procedure sp_save_test(p_id out number, p_sql varchar2)

begin

execute immediate p_sql returning into p_id;

 

end;

 

This is against the same table, why directly executed via SQLPlus, it worked; while via java --> oracle,   it raises error?

 

 

What could be wrong,  I checked all the column definitions, the size for each column is more than enough to hold the value.

  • 1. Re: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    rp0428 Guru
    Currently Being Moderated

    Works for me.

     

    create table a (user_id number)

     

    create or replace procedure sp_save_test(p_id out number, p_sql varchar2) is
    begin
    execute immediate p_sql returning into p_id;
    end;

     

                int sp_out;

                String sqlStatement = "insert into a( user_id) values (1) returning USER_ID into :USER_ID";

                String procedureCall = "begin sp_save_test(?,?); end;";

                CallableStatement call = conn.prepareCall(procedureCall);

                call.registerOutParameter(1, java.sql.Types.INTEGER);

                call.setString(2, sqlStatement);

                call.execute();

    //

                sp_out = call.getInt(1);

                System.out.println(sp_out);

     

    run:

    Getting first connection.

    1

    BUILD SUCCESSFUL (total time: 5 seconds)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points