0

Calling plsql from java when you need to pass boolean parameter is tricky and as Oracle suggests you can:

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.

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#34_05

Ok it works, but what if I have to use http://docs.spring.io/spring/docs/1.2.9/api/org/springframework/jdbc/object/StoredProcedure.html object? Passing just Types.BOOLEAN throws java.sql.SQLException and claims wrong type. Do I still need wrapper function?

Łukasz Rzeszotarski
  • 5,791
  • 6
  • 37
  • 68
  • 1
    AFAIK Oracle's `BOOLEAN` isn't a type for procedure users. Your parameter should be `NUMBER` and from app it would be very simple to convert `boolean` to `0/1` before procedure call. And don't break your mind on the matter. – Artem Bilan May 27 '14 at 08:34
  • just pass a 0/1 through, there is no boolean in oracle – Jaiwo99 May 27 '14 at 08:59
  • @Jaiwo99 there is of course a BOOLEAN type in Oracle DB http://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes004.htm – Łukasz Rzeszotarski May 27 '14 at 10:00
  • @ArtemBilan the procedure has input parameter of type http://docs.oracle.com/cd/B19306_01/olap.102/b14346/dml_datatypes004.htm and its developed by another developer, so I cannot simply modify the interface. We have a wrapper function for it but my question is if there is maybe some workaround solution for it when you use StoredProcedure implementation. – Łukasz Rzeszotarski May 27 '14 at 10:02
  • No, you can't populate PL/SQL types to the SQL expression. – Artem Bilan May 27 '14 at 10:09
  • http://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases – Jaiwo99 May 27 '14 at 10:11

0 Answers0