This technique is handy when we get a refcursor from a stored procedure, so we cannot use cursor’s FOR LOOP.
We need to define a variable to be used for fetching the cursor.
TYPE dept_info_rt IS RECORD ( dept_name VARCHAR2(100), dept_number NUMBER, emp_count NUMBER, manager_name VARCHAR2(100), budget NUMBER, location VARCHAR2(100) ); v_rec dept_info_rt;
If we had a view (vw_dept) with all these fields, we could define the record variable using the %ROWTYPE attribute:
As a good alternative to a view in this case, we can use a custom cursor:
CURSOR c IS SELECT dname as dept_name, deptno AS dept_number, 1 AS emp_count, ename AS manager_name, sal as budget, loc AS location FROM emp JOIN dept USING (deptno) WHERE 1=2; v_rec c%ROWTYPE;
As we can see, the cursor perfectly substitutes the view for our fetching needs.
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.