Forum Stats

  • 3,816,994 Users
  • 2,259,266 Discussions
  • 7,893,618 Comments

Discussions

Problem when calling a return type BOOLEAN SQL Function in a package

714591
714591 Member Posts: 2
edited Jun 18, 2013 3:55AM in JDeveloper and ADF
Hi All,

I am having problem when trying to call a SQL function in a package with return type BOOLEAN
The SQL function signature is as follows
####

CREATE OR REPLACE PACKAGE RMSOWNER.ORDER_ATTRIB_SQL ****

FUNCTION GET_PO_TYPE_DESC(O_error_message IN OUT VARCHAR2,
I_PO_TYPE IN VARCHAR2,
O_PO_TYPE_DESC IN OUT VARCHAR2)
RETURN BOOLEAN;

####


Following is my java code

####
+CallableStatement cs3 = conn.prepareCall("{?=call ORDER_ATTRIB_SQL.GET_PO_TYPE_DESC(?,?,?)}");+
+cs3.registerOutParameter(1, java.sql.Types.BOOLEAN);+
+cs3.registerOutParameter(2, java.sql.Types.VARCHAR);+
+cs3.registerOutParameter(4, java.sql.Types.VARCHAR);+
+cs3.setString(2, "");+
+cs3.setString(3, "ST");+
+cs3.setString(4, "");+
+ResultSet rs3 = cs3.executeQuery();+
####

I get the following exception, i tried changing the sql type(registerOutParameter) from boolean to bit but i still getting this exception.
But when i call any other functions with return type other than boolean they work perfectly fine.

Please can anyone help me fix this issue, i am not sure if its anything to do with vendor JDBC classes?

#####
+java.sql.SQLException: ORA-06550: line 1, column 13:+
+PLS-00382: expression is of wrong type+
+ORA-06550: line 1, column 7:+
+PL/SQL: Statement ignored+

+ at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)+
+ at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)+
+ at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)+
+ at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)+
+ at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)+
+ at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:954)+
+ at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)+
+ at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)+
+ at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)+
+ at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)+

#####
Tagged:
k_prashantuser11342854b11fe55d-c932-4e47-a8ad-d62611ac1902

Best Answer

  • 507661
    507661 Member Posts: 49
    Looks like this is a Limitation of jdbc drivers. Please refer the following link:
    http://www-camden.rutgers.edu/HELP/Documentation/Oracle/java.815/a64684/typesup1.htm

    Here is the excerpt from above:
    ==========================================================
    Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

    Oracle JDBC drivers do not support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RECORD, or BOOLEAN.

    As a workaround, you can create wrapper procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

Answers

  • 507661
    507661 Member Posts: 49
    Looks like this is a Limitation of jdbc drivers. Please refer the following link:
    http://www-camden.rutgers.edu/HELP/Documentation/Oracle/java.815/a64684/typesup1.htm

    Here is the excerpt from above:
    ==========================================================
    Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

    Oracle JDBC drivers do not support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RECORD, or BOOLEAN.

    As a workaround, you can create wrapper procedures that handle the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.
  • 714591
    714591 Member Posts: 2
    Hi Makrand Pare,

    Thank you for the reply , we are planning to write wrappers functions on top of core functions which can be called through JDBC.
    Once again thanx for the reply :)
  • Hello People!

    There is another workaround!!

    See the example below:

    private String callBooleanAPi(String tableName,String apikey,String dtInicio,String dtFim,String comando) throws SQLException {
    CallableStatement cs = null;
    String call = "";
    String retorno = null;
    try {

    if(comando.equalsIgnoreCase("INSERT")){
    call = "declare x BOOLEAN; y varchar2(2);begin x :=PKG.INSERT(?,?,?,?,?); if x then y := 'S'; else y :='N'; end if; ? := y;end;";
    } else if(comando.equalsIgnoreCase("UPDATE")){
    call = "declare x BOOLEAN; y varchar2(2);begin x := PKG.UPDATE(?,?,?,?,?); if x then y := 'S'; else y :='N'; end if; ? := y;end;";
    } else if(comando.equalsIgnoreCase("DELETE")){
    call = "declare x BOOLEAN; y varchar2(2);begin x := PKG.DELETE(?,?,?,?,?); if x then y := 'S'; else y :='N'; end if; ? := y;end;";
    }
    cs = conn.prepareCall(call);

    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    SimpleDateFormat sdfToSqlDate = new SimpleDateFormat("yyyy-MM-dd");
    java.util.Date dataInicialVigencia =null;
    java.util.Date dataFinalVigencia = null;
    Date dtInicialFormatada =null;
    Date dtFinalFormatada = null;

    if(dtInicio != null && !dtInicio.equals("")){
    dataInicialVigencia = sdf.parse(dtInicio);
    dtInicio =sdfToSqlDate.format(dataInicialVigencia);
    dtInicialFormatada = Date.valueOf(dtInicio);
    }
    if(dtFim != null && !dtFim.equals("")){
    dataFinalVigencia = sdf.parse(dtFim);
    dtFim =sdfToSqlDate.format(dataFinalVigencia);
    dtFinalFormatada = Date.valueOf(dtFim);
    }
    cs.setString(1, tableName);
    cs.setString(2, apikey);
    cs.setDate(3, dtInicialFormatada );
    cs.setDate(4, dtFinalFormatada );
    cs.registerOutParameter(5, java.sql.Types.VARCHAR);
    cs.registerOutParameter(6, java.sql.Types.VARCHAR );

    cs.execute();
    retorno = cs.getString(6);

    System.out.println( cs.getString(5));
    } catch(SQLException e){
    throw new SQLException("An SQL error ocurred while calling the API COR_VIGENCIA: " + e);
    } catch(Exception e){
    Debug.logger.error( "Error calculating order: " + id, e );
    } finally {
    if (cs != null) {
    cs.close();
    cs = null;
    }
    }

    return retorno;
    }
    As you can see the CallableStatement class acepts PL/SQl blocks.

    Best Regards.
    k_prashantuser11342854
  • Thanks 746474

    Its really helpfull

    b11fe55d-c932-4e47-a8ad-d62611ac1902
This discussion has been closed.