Print out a resultset from pl/sql

  ....
  cursor mycur (mynum in number) is
  select * from mytable where mycol >= mynum;

  myrow mytable%rowtype;
  myvar number;
  ...
  begin
    ....

    print_out_resultset(query_string =>
      'select * from mytable where mycol >= :b1',
      col1 => 'SAM', col2 => 'FRED', col3 => 'ERIC',
      b1 => myvar,
      maxrows => 4);

    open mycur(myvar);
    fetch mycur into myrow;
    while mycur%found loop
    ....

SQL> set serverout on;
SQL> execute myproc;
SAM         FRED        ERIC       
-----------------------------------
32A         49B         15C
34A         11B         99C
11F         99A         887
77E	    88J         976

procedure print_out_resultset
(query_string in varchar2,
col1 in varchar2, len1 in varchar2 default 10,
col2 in varchar2 default null, len2 in varchar2 default 10,
col3 in varchar2 default null, len3 in varchar2 default 10,
col4 in varchar2 default null, len4 in varchar2 default 10,
col5 in varchar2 default null, len5 in varchar2 default 10,
b1 in varchar2 default null, b2 in varchar2 default null,
b3 in varchar2 default null, b4 in varchar2 default null,
b5 in varchar2 default null, b6 in varchar2 default null,
number_of_bind_variables number default 0,
maxrows in number default 50)
is

--b1 - b6 are bind variables 
--in query_string, use :b1, :b2, ... :b6 
--and supply values for each in b1 ... b6 
--then enter the number of bind variables provided 
--(this allows you to provide null values for bind variables) 
--col1 - col5 are column headers
--and l1 - l5 are the displaywidths of each of these columns
--ALL COLUMN TYPES ARE ASSUMED TO BE VARCHAR2.  Cast as needed
--in your query_string.

l1 number := len1;
l2 number := len2;
l3 number := len3;
l4 number := len4;
l5 number := len5;

r1 varchar2(500) := 0;
r2 varchar2(500) := 0;
r3 varchar2(500) := 0;
r4 varchar2(500) := 0;
r5 varchar2(500) := 0;

lcounter number := 0;
maxcount number := least(maxrows,500);

chandle binary_integer;
results integer;

divline varchar2(150);
i number := 0;

begin
	if col2 is null then l2 := 0; end if;
  if col3 is null then l3 := 0; end if;
  if col4 is null then l4 := 0; end if;
  if col5 is null then l5 := 0; end if;

  
  dbms_output.put_line ( rpad(col1,l1) || ' '
  	|| rpad (col2,l2) || ' ' 
    || rpad (col3,l3) || ' ' 
    || rpad (col4,l4) || ' ' 
    || rpad (col5,l5) || ' ' );
  
  while i < least(l1+l2+l3+l4+l5+5,150)
	loop
  	divline := divline || '-' ;  
    i := i + 1;
  end loop;
  
  dbms_output.put_line ( divline );
    
	begin <>
    chandle := dbms_sql.open_cursor ;
    dbms_sql.parse ( chandle, query_string, DBMS_SQL.NATIVE ) ;

		begin <>
  		if ( b1 is not null or number_of_bind_variables > 0) then
  			dbms_sql.bind_variable(chandle,'b1',b1);
  		end if;
  		if ( b2 is not null or number_of_bind_variables > 1) then
  			dbms_sql.bind_variable(chandle,'b2',b2);
  		end if;
  		if ( b3 is not null or number_of_bind_variables > 2) then
  			dbms_sql.bind_variable(chandle,'b3',b3);
  		end if;
  		if ( b4 is not null or number_of_bind_variables > 3) then
  			dbms_sql.bind_variable(chandle,'b4',b4);
  		end if;
  		if ( b5 is not null or number_of_bind_variables > 4) then
  			dbms_sql.bind_variable(chandle,'b5',b5);
  		end if;
  		if ( b6 is not null or number_of_bind_variables > 5) then
  			dbms_sql.bind_variable(chandle,'b6',b6);
  		end if;
		exception
    	when others then
      	dbms_output.put_line ( 'Error binding: ');
        dbms_output.put_line ( sqlerrm);
        raise;
		end bindblock;
    	
    dbms_sql.define_column_char (chandle, 1, r1, l1);
    if l2 > 0 then
    	dbms_sql.define_column_char (chandle, 2, r2, l2);
    end if;
    if l3 > 0 then
    	dbms_sql.define_column_char (chandle, 3, r3, l3);
    end if;
    if l4 > 0 then
    	dbms_sql.define_column_char (chandle, 4, r4, l4);
    end if;
    if l5 > 0 then
    	dbms_sql.define_column_char (chandle, 5, r5, l5);
    end if;

  	results := dbms_sql.execute(chandle);
    results := dbms_sql.fetch_rows ( chandle ) ;

		while results > 0 loop
      dbms_sql.column_value_char (chandle, 1, r1);
  		if l2 > 0 then
      	dbms_sql.column_value_char (chandle, 2, r2);
      end if;
  		if l3 > 0 then
      	dbms_sql.column_value_char (chandle, 3, r3);
      end if;
  		if l4 > 0 then
      	dbms_sql.column_value_char (chandle, 4, r4);
      end if;
  		if l5 > 0 then
      	dbms_sql.column_value_char (chandle, 5, r5);
      end if;

    dbms_output.put_line ( rpad(nvl(r1,' '),l1) || ' '
      	|| rpad (nvl(r2,' '),l2) || ' ' 
        || rpad (nvl(r3,' '),l3) || ' ' 
        || rpad (nvl(r4,' '),l4) || ' ' 
        || rpad (nvl(r5,' '),l5) || ' ' 
	);
      
	    results := dbms_sql.fetch_rows ( chandle ) ;

      lcounter := lcounter + 1;
      if lcounter >= maxcount then
      	results := 0;
      end if;
		end loop;

    dbms_sql.close_cursor ( chandle ) ;
	exception
  	when others then
    	dbms_output.put_line ( 'Error executing supplied query:');
      dbms_output.put_line ( sqlerrm);
			raise;
  end qblock;

  
   
  dbms_output.put_line ( ' ');
  
end print_out_resultset ;