2 Replies Latest reply on Sep 12, 2014 4:13 PM by 2744947

    Function taking ref cursor as input but throwing error at output

    2744947

      Hi Team,

                    I have a function which will take input a ref cursor and after some internal comparison it will give output a number. My SQL Developer version is 4.0.2.

      Below is the function and the output.

      Function:

       

      FUNCTION func1(input1 IN t_cursor
                                 ) RETURN NUMBER
        IS
          l_ret             NUMBER := 0;
          l_id             number;
          l_name            varchar2(300);
          l_status         number;
          l_step_cnt        NUMBER;
          l_templ_cnt       NUMBER;
          l_br_cnt          NUMBER;
        BEGIN
          --
         FETCH p_pf INTO l_id, l_name, l_status, l_step_cnt, l_templ_cnt, l_br_cnt;
          IF p_pf%NOTFOUND THEN
            l_ret := 0;
          END IF;
          IF l_status = 1 THEN
            l_ret := 0;
          ELSIF l_status = 2 and l_br_cnt <> 0 THEN
            l_ret := 0;
          ELSE
            l_ret := 1;
          END IF;

          RETURN l_ret;
        EXCEPTION
          WHEN OTHERS THEN
            log_message := 'SQLERRM: '||SQLERRM;
            pkglogging.log( a_applic => lv_procedure_name, a_pid => - 1, a_level => pkglogging.g_ERROR, a_msg => gv_log_message );
            RAISE;
        END changes_allowed;

       

      Error:

      Expected exception: [NONE], Received: [900: ORA-00900: invalid SQL statement ORA-06512: at line 4

        • 1. Re: Function taking ref cursor as input but throwing error at output

                        I have a function which will take input a ref cursor and after some internal comparison it will give output a number. My SQL Developer version is 4.0.2.

          Below is the function and the output.

          Function:

           

          FUNCTION func1(input1 IN t_cursor
                                     ) RETURN NUMBER
            IS
              l_ret             NUMBER := 0;
              l_id             number;
              l_name            varchar2(300);
              l_status         number;
              l_step_cnt        NUMBER;
              l_templ_cnt       NUMBER;
              l_br_cnt          NUMBER;
            BEGIN
              --
             FETCH p_pf INTO l_id, l_name, l_status, l_step_cnt, l_templ_cnt, l_br_cnt;

          You can NOT FETCH a cursor that doesn't even exist. The parameter name is 'input1' but you try to fetch something named 'p_pf' which isn't even defined anywhere.

           

          That can't possibly work.

          • 2. Re: Function taking ref cursor as input but throwing error at output
            2744947

            I have changed the cref cursor name, but still it is throwing the same error.