REF Cursors in 9.2.0.1

--declarations
TYPE cur_type is REF CURSOR;
cur  cur_type;  -- cur is a REF CURSOR. 
user_query   VARCHAR2(30000); 

mrowafname vpn_user.fname%TYPE;
mrowalname vpn_user.lname%TYPE;
mrowuser_id vpn_user.vpn_user_id%TYPE;
mrowactive_flag vpn_user.active_flag%TYPE;
mrowcreation_date vpn_user.creation_date%TYPE;
mrowemail vpn_user.email%TYPE;
mrowinstallation vpn_user.installation%TYPE;
mrowarea_code vpn_user_phone.area_code%TYPE;
mrowphone_number vpn_user_phone.phone_number%TYPE; 
mrowvpn_exp_date vpn_user.vpn_exp_date%TYPE;
mrowtsacs_exp_date vpn_user.tsacs_exp_date%TYPE;


--Query string
user_query := 'SELECT a.fname afname, a.lname alname, a.vpn_user_id, a.email, b.area_code, b.phone_number, a.active_flag, a.vpn_exp_date, a.tsacs_exp_date, a.installation,  a.creation_date, b.phone_type_id cpid, 
b.vpn_user_id FROM vpn_user a, vpn_user_phone b WHERE a.vpn_user_id = b.vpn_user_id AND b.phone_type_id = 1001 AND active_flag != ''X''';


--These are so that the user can sort the columns

if ( ord is null ) then			 	      
  user_query := user_query;
elsif (ord = 2) then
  user_query := user_query ||' order by a.lname';
elsif (ord = 3) then
  user_query := user_query ||' order by a.vpn_user_id';
elsif (ord = 4) then
  user_query := user_query ||' order by a.email';
elsif (ord = 5) then
  user_query := user_query ||' order by b.area_code';
elsif (ord = 7) then
  user_query := user_query ||' order by a.active_flag';
elsif (ord = 8) then
  user_query := user_query ||' order by a.vpn_exp_date';
elsif (ord = 9) then
  user_query := user_query ||' order by a.tsacs_exp_date';
elsif (ord = 10) then
  user_query := user_query ||' order by a.installation';
elsif (ord = 11) then
  user_query := user_query ||' order by a.creation_date';
end if;


--OPEN the CUR so we can start displaying records
OPEN cur FOR user_query;
LOOP
  FETCH cur INTO mrowafname, mrowalname, mrowuser_id,   
    mrowemail, mrowarea_code, mrowphone_number, 
    mrowactive_flag, mrowvpn_exp_date, 
    mrowtsacs_exp_date, mrowinstallation,
    mrowcreation_date;		
EXIT WHEN cur%NOTFOUND;

-- start display tbl clr_count changes row color
IF ( clr_count = 0 ) THEN				  
   htp.TableRowOpen;
   htp.print('<td bgcolor="#DCEBEB"><a href="vpn_system.vpn_edit_user?p_user_id=' ||mrowuser_id||'">'|| mrowafname || ' ' || mrowalname ||'</a></td>');