Unable to get boolean value from oracle stored procedure

Question

I am trying from hours to find out how I can get the returned value as boolean from oracle stored procedure.

I created a simple SP which is returning a boolean as out but when I pass the parameter as OracleDbType.Boolean I am getting error.

This is my sp

create or replace procedure nbq_out_test (pi_bool_in IN BOOLEAN,
                                  pi_var_in  IN VARCHAR2,
                                  pi_num_in  IN NUMBER,
                                  po_bool_out OUT BOOLEAN,
                                  po_var_out  OUT VARCHAR2,
                                  po_num_out OUT NUMBER) AS
BEGIN
    po_bool_out := pi_bool_in ;
    po_var_out := pi_var_in;
    po_num_out := pi_num_in;

END   nbq_out_test;

And this is my c# code

OracleCommand command = new OracleCommand("NBQ_OUT_TEST", con);
            OracleParameter param;
            command.BindByName = false;
            command.CommandType = CommandType.StoredProcedure;

param = new OracleParameter("PI_BOOL_IN", OracleDbType.Boolean, ParameterDirection.Input); //Works fine as input
            param.Value = true;
            command.Parameters.Add(param);

param = new OracleParameter("PO_BOOL_OUT",OracleDbType.Boolean,ParameterDirection.Output); //This is the line causing error.

            command.Parameters.Add(param);
command.ExecuteNonQuery();

This is the error

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

I cannot modify the Stored Procedure to return 1 or 0 or anything similar.

The oracle version is 12.1.0.2.0

The Oracle.ManagedDataAccess.18.3.0

I have searched lot but most of the suggestion was to modify the SP which is not at my end.

Any suggestion will be great help.

@preciousbetine Oracle SQL does not support `BOOLEAN` but Oracle PL/SQL does have a `BOOLEAN` data type. — MT0, Jan 30 '19 at 11:04
Probably a duplicate of https://stackoverflow.com/questions/27919012/how-to-pass-boolean-parameter-to-oracle-procedure-c-sharp or https://stackoverflow.com/q/39152403/1509264 — MT0, Jan 30 '19 at 11:09
I have already checked those links, those were not what I am looking for. Any way thanks for the response. — Iftikhar Ali Ansari, Jan 30 '19 at 12:50

score 1 · Accepted Answer · answered Jan 30 '19 at 08:49

1

OracleDbType doesn't seem to support Boolean.

I think your question already has an answer in below page. Boolean in OracleDbType

answered Jan 30 '19 at 08:49

ChipLimo

71
1
6

I believe it is the possible reason, but couldn't come to conclusive result as it does have boolean as enum, and as a input it accept also. Only the problem is with out. – Iftikhar Ali Ansari Jan 30 '19 at 09:18
I assume that input didn't accept as well as output because the error occured at line 1 where pi_bool_in is located. I don't know why boolean exists as enum but Oracle doesn't record it in ODP manual.. so, maybe it is used only internally :) – ChipLimo Jan 30 '19 at 10:32
Finally I gave up on boolean as out parameter. I couldn't find. I have written a wrapper statement on top of the stored procedure which will return decimal value instead of boolean. and somehow make my work moving. – Iftikhar Ali Ansari Jan 30 '19 at 12:54

Unable to get boolean value from oracle stored procedure

1 Answers1