2 Replies Latest reply: Dec 7, 2012 10:21 AM by 6363 RSS

    expression is of wrong type

    user10647455
      Hello
      I have the this function
      FUNCTION MY_F_SUB_EXISTS (
            p_email_address   IN cnsmr.email_addr%TYPE,
            p_question_cd     IN ref_cd.quest_num%TYPE,
            p_language        IN ref_cd.language%TYPE)
            RETURN BOOLEAN
         AS
            CURSOR cur_exists (
               l_email_address   IN cnsmr.email_addr%TYPE,
               l_question_cd     IN ref_cd.quest_num%TYPE)
            IS
               SELECT 1
                 FROM queue_consumers qc, queue_consumer_subsc qcs
                WHERE     qcs.queue_consumer_id = qc.queue_consumer_id
                      AND qcs.question_cd = l_question_cd
                      AND LOWER (qc.email_address) = LOWER (l_email_address);
      
            --
            CURSOR cur_rtr_exists (
               l_email_address   IN cnsmr.email_addr%TYPE,
               l_question_cd     IN ref_cd.quest_num%TYPE,
               l_language        IN ref_cd.language%TYPE)
            IS
               SELECT 1
                 FROM cnsmr idv,
                      cnsmr_opt idv_sbc,
                      ref_cd sbsc
                WHERE     idv_sbc.indiv_id = idv.indiv_id
                      AND idv_sbc.subsc_cd = sbsc.subsc_cd
                      AND idv_sbc.subsc_prod_cd = sbsc.subsc_prod_cd
                      AND idv_sbc.subsc_chnl_cd = sbsc.subsc_chnl_cd
                      AND sbsc.quest_num = l_question_cd
                      AND sbsc.language = l_language
                      AND LOWER (idv.email_addr) = LOWER (l_email_address);
      
            --
            v_exist   NUMBER DEFAULT NULL;
         BEGIN
            --
            OPEN cur_exists (p_email_address, p_question_cd);
      
            FETCH cur_exists INTO v_exist;
      
            --
            IF cur_exists%FOUND
            THEN
               CLOSE cur_exists;
      
               RETURN TRUE;
            ELSE
               --
               CLOSE cur_exists;
      
               OPEN cur_rtr_exists (p_email_address, p_question_cd, p_language);
      
               FETCH cur_rtr_exists INTO v_exist;
      
               --
               IF cur_rtr_exists%FOUND
               THEN
                  CLOSE cur_rtr_exists;
      
                  RETURN TRUE;
               ELSE
                  CLOSE cur_rtr_exists;
      
                  RETURN FALSE;
               END IF;
            END IF;
         --
         EXCEPTION
            WHEN OTHERS
            THEN
               raise_application_error (
                  -20001,
                     'error in MY_F_SUB_EXISTS:'
                  || SQLCODE
                  || '-'
                  || SQLERRM);
         END MY_F_SUB_EXISTS;
      Please ignore any syntax error if there is any it is modified , i am more concern over its parameter input and call to this function

      My question is
      while i am calling like this
      select MY_F_SUB_EXISTS('mytest@mail.com','SUB-F','EN') 
       from dual;
      I receive this error
      ORA-06552: PL/SQL: Statement ignored
      ORA-06553: PLS-382: expression is of wrong type
      what i am missing here ?
        • 1. Re: expression is of wrong type
          6363
          There is no boolean data type in SQL so you won't be able to use that function in queries or DML.

          You could return characters such as 'Y' and 'N' or 'TRUE' and 'FALSE' or a numeric 0 or 1.

          You should probably delete that exception section as all it achieves is losing useful information about any errors within the function including the line number that the caused the error.
          • 2. Re: expression is of wrong type
            Hoek
            BOOLEAN cannot be used in SQL:
            SQL> create or replace function bla
              2  return boolean
              3  as
              4  begin
              5   return true;
              6  end;
              7  /
            
            Function created.
            
            SQL> select bla from dual;
            select bla from dual
                   *
            ERROR at line 1:
            ORA-06552: PL/SQL: Statement ignored
            ORA-06553: PLS-382: expression is of wrong type
            Only in PL/SQL.