ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

Question

I'm using this basic code to call Oracle function.

public void basicTest() throws Exception
    {
        System.out.println("Basic Oracle Function call test");
        // initialize the driver and try to make a connection
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "admin", "qwerty");

        // prepareCall uses ANSI92 "call" syntax
        CallableStatement cstmt = conn.prepareCall("{? = call AGENT_STATISTICS_FUNCTION(?)}");

        // get those bind variables and parameters set up
        cstmt.registerOutParameter(1, Types.VARCHAR);
        cstmt.setString(2, "agent");

        // now we can do it, get it, close it, and print it
        cstmt.execute();
        String result = cstmt.getString(1);
        conn.close();
        System.out.println(result);
    }

Oracle function:

CREATE OR REPLACE FUNCTION AGENT_STATISTICS_FUNCTION(NAMEIN IN VARCHAR2

)
RETURN CLOB
AS
.................

    LINE CLOB;
...............

BEGIN

   LINE := EMPTY_CLOB();
   DBMS_LOB.CREATETEMPORARY(LINE,TRUE);
..............

    RETURN LINE;
END AGENT_STATISTICS_FUNCTION;
/

I get this error:

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:48)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
    at org.sql.engine.osgi.select.TestOracleFunction.extendedTest(TestOracleFunction.java:85)


Results :

Tests in error: 
  TestOracleFunction.extendedTest:85 » SQL ORA-06502: PL/SQL: numeric or value e...

I tried to add conn.prepareCall("begin dbms_output.enable(200000); ? := AGENT_STATISTICS_FUNCTION(?); end;"); but the result is the same. Can you propose some solution. In SQL Developer the code is working fine?

Just a wild guess: `cstmt.registerOutParameter(1, Types.CLOB);` — Erich Kitzmueller, Nov 25 '15 at 12:22
Not enough info. Somewhere you put a clob into a varchar2 and it overflows(it's over 32k). Or another thing like that. — Florin Ghita, Nov 25 '15 at 12:23
@PeterPenzov This was already pointed in this answer http://stackoverflow.com/a/33801480/319875 _Also, you have a conceptual error in your Java code; registering the return of the function as Types.VARCHAR is wrong. You should rather use the Oracle's dedicated CLOB type._ — Florin Ghita, Nov 25 '15 at 14:01

score 2 · Accepted Answer · answered Nov 25 '15 at 12:34

2

You need to register the correct output type:

cstmt.registerOutParameter(1, Types.CLOB);

answered Nov 25 '15 at 12:34

Erich Kitzmueller

36,381
5
80
102

ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1

1 Answers1