CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
swet
swet

Reputation: 217

Oracle - remove Count query as the same query is already used in returning ref cursor

I am returning a ref cursor(P_CUR_SUMMARY) and then taking count of the same query to return a string (WARN# / ERROR). I want to eliminate the Count query.

This proc is using an existing framework so I cant return the string with the ref cursor.

I dont want to create a temp table and store data. This will remove the Count query but increase IO.

Any other option to eliminate the Count query? I just want to know if the ref cursor has ANY records. Not interested in actual count.

Procedure:
V_MAIN_TITLE and P_CUR_SUMMARY - OUT parameters

...
OPEN P_CUR_SUMMARY FOR
    select * from emp...
...
select count(1) into V_CNT from emp...
...
IF V_CNT > 0 then
    SELECT 'WARN#' INTO V_MAIN_TITLE FROM dual;
ELSE
    SELECT 'ERROR' INTO V_MAIN_TITLE FROM dual;
END IF;

Upvotes: 0

Views: 56

Answers (2)

Littlefoot
Littlefoot

Reputation: 142753

As you were told, you'll have to fetch from a cursor in order to find out what it contains (use %NOTFOUND or %FOUND cursor attributes for such a purpose). If there's something to be returned, re-open the cursor (otherwise you'd lose the first row, the one you used to check what's in there).

Here's an example:

SQL> create or replace procedure p_test
  2    (par_deptno in number, par_main_title out varchar2, par_cur_summary out sys_refcursor)
  3  is
  4    r_emp emp%rowtype;
  5  begin
  6    open par_cur_summary for
  7      select *
  8        from emp
  9        where deptno = par_deptno;
 10
 11    -- fetch in order to find out whether there's something returned by the cursor
 12    fetch par_cur_summary into r_emp;
 13    if par_cur_summary%notfound then
 14       par_main_title := 'ERROR';
 15    else
 16       par_main_title := 'WARN#';
 17
 18       -- now, re-open the cursor; otherwise, you'd lose the first row, the one
 19       -- that has been fetched for "testing" purpose
 20       open par_cur_summary for
 21         select *
 22           from emp
 23           where deptno = par_deptno;
 24    end if;
 25  end;
 26  /

Procedure created.

Let's test it:

SQL> var l_cur_sum refcursor
SQL> var l_mai_tit varchar2(30)
SQL> exec p_test(37, :l_mai_tit, :l_cur_sum);

PL/SQL procedure successfully completed.

SQL> print l_mai_tit

L_MAI_TIT
---------------------------------------------------------------------------------------

ERROR

SQL> print l_cur_sum
ERROR:
ORA-01002: fetch out of sequence



no rows selected

SQL> exec p_test(10, :l_mai_tit, :l_cur_sum);

PL/SQL procedure successfully completed.

SQL> print l_mai_tit

L_MAI_TIT
---------------------------------------------------------------------------------------

WARN#

SQL> print l_cur_sum

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.1981       2450                    10
      7839 KING       PRESIDENT            17.11.1981       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982       1300                    10

SQL>

Look what happens if you don't re-open the cursor (by commenting those lines out):

  if par_cur_summary%notfound then
     par_main_title := 'ERROR';
  else
     par_main_title := 'WARN#';
     /*
     open par_cur_summary for 
       select *
         from emp
         where deptno = par_deptno;
     */
  end if;

SQL> exec p_test(10, :l_mai_tit, :l_cur_sum);

PL/SQL procedure successfully completed.

SQL> print l_mai_tit

L_MAI_TIT
----------------------------------------------------------------------------------------

WARN#

SQL> print l_cur_sum

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.1981       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982       1300                    10

SQL>

See? CLARK is lost.

Upvotes: 2

William Robertson
William Robertson

Reputation: 16001

The number of rows that will be returned is not known until the query is executed (i.e. when rows are fetched). When the cursor has merely been opened that is still in the future.

Perhaps it makes more sense for the client to handle the condition where no rows are returned and raise its own errors.

Upvotes: 1

Related Questions