A tricky alternative to using the TYPE … IS RECORD statement in PL/SQL, by Zahar Hilkevich

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:

   v_rec vw_dept%ROWTYPE;

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s