StoredProcedureQuery does map a Boolean to BIT (1 or 0) instead of true or false using Oracle (Dialect + Database)

Description

This does work using Hibernate and a StoredProcedureQuery:

final StoredProcedureQuery storedProcedureQuery = entityManager.get().createStoredProcedureQuery(
                    "dbms_stats.GATHER_TABLE_STATS");
            storedProcedureQuery.registerStoredProcedureParameter("ownname", String.class, ParameterMode.IN);
            storedProcedureQuery.registerStoredProcedureParameter("tabname", String.class, ParameterMode.IN);
            storedProcedureQuery.setParameter("ownname", ownerName);
            storedProcedureQuery.setParameter("tabname", tableName);
            storedProcedureQuery.executeUpdate();

Good so far - now i wanted to specify the cascade attribute which is a Boolean.

(http://www.toadworld.com/platforms/oracle/w/wiki/3403.dbms-stats-gather-table-stats-statt)

So i tried that:

final StoredProcedureQuery storedProcedureQuery = entityManager.get().createStoredProcedureQuery(
                    "dbms_stats.GATHER_TABLE_STATS");
            storedProcedureQuery.registerStoredProcedureParameter("ownname", String.class, ParameterMode.IN);
            storedProcedureQuery.registerStoredProcedureParameter("tabname", String.class, ParameterMode.IN);
            storedProcedureQuery.registerStoredProcedureParameter("cascade", Boolean.class, ParameterMode.IN);
            storedProcedureQuery.setParameter("ownname", ownerName);
            storedProcedureQuery.setParameter("tabname", tableName);
            storedProcedureQuery.setParameter("cascade", cascade);
            storedProcedureQuery.executeUpdate();

This fails with the message:

ORA-20001: 1 is an invalid identifier
ORA-06512: in "SYS.DBMS_STATS", Zeile 34634
ORA-06512: in Zeile 1

Looking at the trace logs Hibernate does this:

05:41.616 [main] DEBUG org.hibernate.SQL [SqlStatementLogger.java:109] - {call dbms_stats.GATHER_TABLE_STATS(?,?,?)}
05:41.617 [main] TRACE o.h.type.descriptor.sql.BasicBinder [BasicBinder.java:81] - binding parameter [1] as [VARCHAR] - [USER]
05:41.618 [main] TRACE o.h.type.descriptor.sql.BasicBinder [BasicBinder.java:81] - binding parameter [2] as [VARCHAR] - [TABLE]
05:41.618 [main] TRACE o.h.type.descriptor.sql.BasicBinder [BasicBinder.java:81] - binding parameter [3] as [BIT] - [true]

This does work normally for standard dml stuff where a boolean is something like 0 or 1 in a NUMBER(1) in oracle.
But using a StoredProcedureQuery 0 or 1 are wrong, it must be false or true.

Activity

Show:

Steve Ebersole October 4, 2016 at 3:27 PM

This is something Oracle's JDBC drivers (supposedly the OCI layer itself) cannot handle

Steve Ebersole October 4, 2016 at 3:26 PM

No problem. Yes, it is indeed frustrating.

Torsten Krah October 4, 2016 at 3:24 PM

@Steve Ebersole - yeah its not working. setBoolean does call setBooleanInternal which does something like that internally:

this.parameterInt[this.currentRank][var3] = var2 ? 1 : 0;

where var2 is the actual boolean argument.
So yes the driver itself is unable at all to do this which does match http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#34_05 which reads:

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.
Here is an example of a PL/SQL wrapper procedure MY_PROC for a stored procedure PROC that takes a BOOLEAN as input:
PROCEDURE MY_PROC (n NUMBER) IS BEGIN IF n=0 THEN proc(false); ELSE proc(true); END IF; END; PROCEDURE PROC (b BOOLEAN) IS BEGIN ... END;

sigh ... sorry to bother you at all with that.

Torsten Krah October 4, 2016 at 3:10 PM

@Steve Ebersole will try that with jdbc - this will going to be fast to check. If it fails there already Hibernate can't do anything about that, obviously. Would be kind of interesting if oracles jdbc drivers are still not capable of doing that in 2016 ... O_o.