Geek asks and answers

How to build booleans and access them using java / oracle stored procedure

For the first time using Oracle, they usually use SQLServer and faced a curious problem. I tore my hair off with this problem and tried random things, and one of them worked.

So, initially I had only two parameters, and everything worked fine. Then I wanted to add a boolean to allow multiple paths in my saved proc. I am testing it in my db environment and there are no errors and I am returning the data that I expect. Then I add a few lines to my java code to pull this data, and suddenly I get an error " wrong number or types of parameters".

After several attempts, I just change the type in Oracle from Boolean to Int, leaving the Java code still as " setBoolean(3, true)", and everything works.

So my questions are:

1) what am I sending with this setBoolean () on the java side and what is Oracle getting?

2) what did he expect before when he was like, " Booleanand why is it not compatible with setBoolean()?"

3) Should I use the Oracle int type with java setBoolean () or some other combination like Oracle Varchar and setString () when you switch to speed?

Java:

String jobquery =   "{call PKG_TEST.GET_PLAN_DATA(?,?,?)}";  
            CallableStatement callStmt = con.prepareCall(jobquery);

            callStmt.setString(1,"15105");
            callStmt.setString(2, "");
            callStmt.setBoolean(3, true);

Oracle SP (previous):

PROCEDURE GET_PLAN_DATA(
        NAME_N_IN IN VARCHAR2,
        TYPE_C_IN IN VARCHAR2 DEFAULT NULL,
        ONLY_RESTRICTIONS_IN IN BOOLEAN DEFAULT FALSE)

Oracle has changed to:

 PROCEDURE GET_PLAN_DATA(
            NAME_N_IN IN VARCHAR2,
            TYPE_C_IN IN VARCHAR2 DEFAULT NULL,
            ONLY_RESTRICTIONS_IN IN INT DEFAULT 0)
+5
source share
2 answers

1) what am I sending with this setBoolean () on the java side and what is Oracle getting?

From Java docs :

Sets the designated parameter to the given Java boolean value. The driver converts this to an SQL BIT value when it sends it to the database.

So, the value is Booleanconverted to value BIT.

2), , Boolean, setBoolean()? "

, BIT, . Boolean oracle. , , , Oracle Boolean Int, Java "setBoolean (3, true)", ., , oracle . ? .

3) Oracle int java setBoolean() - , ​​ Oracle Varchar, setString(), ?

varchar setString(). , setString() varchar. , setBoolean() int, Boolean BIT int.

- , :

, , .

SP, , .

PROCEDURE sp_xxxx_map (
                ic_opt_number       IN  VARCHAR2,
                in_cast_number    IN  NUMBER,
                in_tyre_amount          IN  NUMBER,
                ob_is_valid             OUT BOOLEAN,
                ob_is_obgr_obgt_valid   OUT BOOLEAN,

--------------- ....

id , boolean OUT. ( , ), SQL java ( JDBC), "ob_is_valid", "ob_is_obgr_obgt_valid". . : SP (, SP1), SP CHAR "Y" true "N" false. , SP1.

5 2003 . - 9 . :

:

declare
   b1 boolean;
   b2 boolean;
   n1 number := 0;
   n2 number := 0; 
begin
   sp_xxxx_map( ?, ?, ?, b1, b2 );
   if (b1) then n1 := 1; end if;
   if (b2) then n2 := 1; end if;
   ? := n1;
   ? := n2;
end;

jdbc.

+2

More articles:


All Articles