Returning stored procedure results to query tool

I'm using several end-user query tools such as Q+E 5.0 and Microsoft Query (with Excel 5.0) to access data on our Oracle 7 database. We previously used Sybase and created a bunch of stored procedures in the database and then executed them from our end-user tools. We could even pass parameters. For example, if I wanted a list of all the people in a certain department, I could send the SQL command "empdept '6169'" where empdept was the procedure name and '6169' was the department. The procedure was simply a select * from dept where deptno='parameter passed'. This returned the results to my query tool in the form of a table.

I've discovered Oracle handles procedures quite a bit differently. It seems like Oracle procedures don't want to return results to my query tool. Even using PS/SQL, it was hard to get Oracle to return any results of a select statement but I finally created procedure that does a simple select and return results to the screen (after issuing a set serveroutput on command).

create or replace procedure test_proc
is

	a varchar2(6);
        b varchar2(2);
	cursor c1 is select * from test_table;
begin
	open c1;
	loop
		fetch c1 into a,b;	-- could also be a 'table' or 'record'
		exit when c1%notfound;
		dbms_output.put_line(a);  -- these 2 lines just display the
		dbms_output.put_line(b);  -- data retrieved
	end loop;

I can execute the above procedure from my end-user query tool but I don't know were the results go to. I just get a message about the procedure executed successfully.

What I'd really like to do is get Oracle to return results of a stored procedure like Sybase does. Does anyone have any tricks or suggestions.

Thanks,

         /            Steve Stansfield                ___/ 
         |     Martin Marietta Energy Systems      __/
        /              Oak Ridge, TN            __/ 
       /        s68_at_ornl.gov  (615)241-3693   _/
      |______________________________________/