stored procedure howto???

Hi all!

Feels like an FAQ, but anyhow... (BTW, is there an FAQ for this group?)

I'm building an ASP application connecting to an Oracle 7.3 DB. I'd like to put my queries in stored procedures for both preformance and other (read: Hiding the database specifics in the db.) reasons. What I want to be returned when I call the procedure with some parameters like e.g. FROM_DATE and TO_DATE in the example below, is a rowset that I process and print to the users browser. Now, I did think that constructing the procedures would be an easy task, somewhat like (There's maybe some syntax errors below, but from the top of my head.):

CREATE OR REPLACE PROCEDURE GET_CASES_1         (FROM_DATE IN TABLE1.DATE%TYPE, TO_DATE IN TABLE1.DATE%TYPE) BEGIN

	SELECT 
		TABLE1.NAME, TABLE2.ADDRESS, TABLE2.CITY
	FROM 
		TABLE1, TABLE2
	WHERE
		TABLE1.ID = TABLE2.ID (+) AND
		TABLE1.DATE_CREATED >= FROM_DATE AND
		TABLE1.DATE_CREATED <= TO_DATE;

which is roughly the way I thought it should be done. But from the examples I've seen about PL/SQL the above example is far from what it really should look like. Rather it seems like I'm supposed to use a cursor and loop through that. What confuses me in these examples is that they are all SQL-Plus orientated and all use DBMS_OUTPUT.PUT_LINE('Name= ' || NAME || ' Address= ' || ADDRESS || ' City= ' || CITY);
which, as far as I remember is a concatenated textstring, and not the recordset that I'm looking for.

Does someone have any examples/receipies on how queries like the above (and also more complicated ones) are "transformed" into procedures that returns recordsets as per usual? (With IN parameters in this case, but that is not really the point.)

Any help much appreciated.

Björn Received on Mon Feb 15 1999 - 10:06:56 CST