How to return a boolen in refcursor?

Question

In the below procedure I need to return a boolean value by using refcursor

CREATE OR REPLACE PROCEDURE gmmt_proc.co_col_preference_details_sp (
   p_applcd         IN       VARCHAR2,
   p_rptname        IN       VARCHAR2,
   p_user           IN       VARCHAR2,
   p_out_col_pref   OUT      sys_refcursor,
   p_err_cd         OUT      NUMBER,
   p_err_msg        OUT      VARCHAR2
)
IS
   p_check   BOOLEAN := true;
   p_val     NUMBER;
BEGIN
   BEGIN
      SELECT p3.col_grp_id
        INTO p_val
        FROM co_page_t p1,
             co_page_col_grp_t p2,
             co_page_col_grp_user_t p3
       WHERE p1.page_id = p2.page_id AND p3.security_userid = p_user;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         p_check := false;
   END;

   OPEN p_out_col_pref FOR
      SELECT p1.page_id, p2.col_grp_nm, p3.col_grp_id,p_check
        FROM co_page_t p1,
             co_page_col_grp_t p2,
             co_page_col_grp_user_t p3
       WHERE p1.page_id = p2.page_id
         AND p2.col_grp_id = p3.col_grp_id
         AND p1.page_nm = p_rptname
         AND p1.appl_cd = p_applcd;
END;

p_check is a boolean value returning error.

PLS-00382: expression is of wrong type

Please suggest me how to correct this

A `Boolean` is a **PLSQL** datatype and not recognized in **SQL**, so you can't — A.B.Cade, Mar 06 '13 at 09:26
There is an answer [here](http://stackoverflow.com/questions/3726758/is-there-a-boolean-type-in-oracle-databases) and [here](http://stackoverflow.com/questions/5260238/function-returning-boolean-fails-on-expression-is-of-wrong-type) and [here](http://stackoverflow.com/questions/13491508/bool-support-oracle-sql) and [here](http://stackoverflow.com/questions/11848559/plsql-possible-to-use-boolean-datatype-in-sqlplus) and ... — A.B.Cade, Mar 06 '13 at 09:53

score 0 · Answer 1 · answered Mar 06 '13 at 11:00

0

convert your boolean to int and check on client side
for C++ 0 == false !0 == true
so you need just put right integer value into your sursor and it will be converted to bool automatically
good luck

answered Mar 06 '13 at 11:00

Galbarad

461
3
16

How to return a boolen in refcursor?

1 Answers1

Linked