You Asked

The syntax of implicit cursor is so much nicer than explicit one! However, in the function below implicit cursor doesn't seem to be working:

CREATE or replace FUNCTION CONCAT_LIST( cur SYS_REFCURSOR )
RETURN VARCHAR2 IS
ret VARCHAR2(32000);
tmp VARCHAR2(4000);
BEGIN
FOR c In cur LOOP
ret := ret || ',' || c.text_field;
END LOOP;
/*loop
fetch cur into tmp;
exit when cur%NOTFOUND;
ret := ret || ',' || tmp;
end loop;*/
RETURN ret;
END;
/

Am I missing something obvious?

and Tom said...

Implicit cursors are not only much "nicer", they are faster as well...

In any case, a REF CURSOR is the very definition of an EXPLICIT cursor. A ref cursor implies you are being explicit by definition.

An implicit cursor takes care of the OPEN, FETCH and CLOSE.

A ref cursor must be opened by you. (its a pointer to a query). Each time you open it, it can point to a result set that is a different "shape" (number and types of columns). Therefore PLSQL cannot automate the FETCH component as it does not know at compile time WHAT the result set looks like.

Since PLSQL cannot do the open (you must), fetch (it doesn't have enough info), you must do this. Hence, it is explicit.

The ref cursor gives you great flexibility -- at the cost of some extra code.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Well, maybe SYS_REFCURSOR is a little different?

Sean M. Dillon, December 10, 2001 - 10:01 pm UTC

Well, w/ SYS_REFCURSOR we don't have to OPEN the REF CURSOR but we do have to close it (according to the DOC):

SQL> create or replace function concat_list( p_cur sys_refcursor )
  2    return  varchar2
  3  is
  4    l_ename  varchar2(20);
  5    l_return varchar2(32767);
  6  begin
  7    loop
  8      fetch p_cur into l_ename;
  9      exit when p_cur%notfound;
 10      l_return := l_return || ',' || l_ename;
 11    end loop;
 12    close p_cur;
 13    return l_return;
 14  end;
 15  /

Function created.

SQL> show error
No errors.
SQL> 
SQL> col dname format a14
SQL> col names format a60
SQL> select d.dname,
  2         concat_list(CURSOR(select e.ename from emp e where e.deptno = d.deptno)) names
  3    from dept d
  4  /
ACCOUNTING     ,CLARK,KING,MILLER
RESEARCH       ,SMITH,JONES,SCOTT,ADAMS,FORD
SALES          ,ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
OPERATIONS

SQL> 
SQL> select distinct e1.job title,
  2         concat_list(CURSOR(select e2.ename from emp e2 where e2.job = e1.job)) names
  3    from emp e1
  4  /
ANALYST        ,SCOTT,FORD
CLERK          ,SMITH,ADAMS,JAMES,MILLER
MANAGER        ,JONES,BLAKE,CLARK
PRESIDENT      ,KING
SALESMAN       ,ALLEN,WARD,MARTIN,TURNER

Can Explicit curson Rec type have extra columns

Gunjan, December 20, 2012 - 11:56 am UTC

I have a PL/SQL code which must return a colection of data that gets values from more than one cursor/queries. I know I can do it by declaring seperate variables and populate their values for each loop in cursor. Is there a more elegant way of doing? like adding more fields to record type declared for cursor.

DECLARE
CURSOR emp_cur IS

SELECT first_name, last_name, salary FROM emp;
emp_rec emp_cur%rowtype;
BEGIN
IF NOT emp_cur%ISOPEN THEN
OPEN emp_cur;
END IF;
LOOP

FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;

dbms_output.put_line(emp_rec.first_name || ' ' || emp_rec.last_name || ' ' || emp_rec.salary);
-- I have to add emp_rec.designation, emp_rec.deptno, emp_rec.XYZ which would come from another table

END LOOP;
END;
/

create table emp(
first_name varchar2(100),
last_name varchar2(100),
salary number);

insert into emp values ('P','M', '0');

December 20, 2012 - 5:34 pm UTC

Please just use implicit cursors in the future for fetching data (most of the time, unless you need to array fetch so you can FORALL update/insert in the next step)

just join

declare
   cursor C is select .... from A, B, C where ....;
begin
   for x in C
   loop
      dbms_output.put_line( x...., x....., x.... );
   end loop;
end;
/

just join, implicit cursor fetching will set up the record AND array fetch 100 rows at a time for you!!!

or if you need explicit fetching:

declare
    cursor c is  select .... from A, B, C where ....;

    type myarray is table of c%rowtype index by binary_integer;
    l_data myarray;
begin
    open c;
    loop
        fetch c bulk collect into l_data limit 100;
        /* process.... */
        exit when c%notfound;
    end loop;
end;
/

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Well, maybe SYS_REFCURSOR is a little different?

Can Explicit curson Rec type have extra columns

More to Explore

PL/SQL demos

PL/SQL docs

explicit versus implicit cursor

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Well, maybe SYS_REFCURSOR is a little different?

Can Explicit curson Rec type have extra columns

More to Explore

PL/SQL demos

PL/SQL docs