Re: Returning stored procedure results to query tool

Steve Stansfield (s68_at_ornl.gov) wrote:
: 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;
: end;
 

: 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.

That's easy. They go to the buffer in the dbms_output package. You could retrieve them from there with dbms.output.get_line(). You would need to use dbms_output.enable() to create the buffer in the first place.

: 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.

That's not so easy. I don't know what/how Sybase does it (although I suppose I should)! How does it cope with the potentially ~infinite size of a cursor result? How does it negotiate with the client about what a row looks like?

From an Oracle >3GL client I would expect to be able to pass a record as an OUT or IN OUT parameter or maybe even to return a record from a stored function (although I haven't tested either). That should take care of your singleton row special case. Whether a 3rd party client tool could construct a structure that Oracle recognised as a record, though? I wouldn't have a clue!

graeme

--
Disclaimer:	The author's opinions are his own, and not necessarily
		those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
      -m------- Graeme Sargent                 Voice: +44 (0)252 373035
    ---mmm----- Senior Database Consultant     Fax  : +44 (0)252 373135
  -----mmmmm--- Pyramid Technology Ltd.        Telex: Tell who???
-------mmmmmmm- Farnborough, Hants  GU14 7PL   Email: graeme_at_pyra.co.uk
---------------------------------------------------------------------------
    We have the technology.  The tricky bit is learning how to use it.