Deanna:
Basically (which you probably already know, but I'll be redundant and go
over it anyway :-) to return multiple records from a query in ColdFusion,
you need to return a reference cursor from Oracle (that's the simplest way
at any rate, and CF 4.5x and above will support doing this in Oracle). So,
if you're writing a package and a procudure in it needs to return multiple
records:
1) Declare a data type of REF CURSOR;
2) Declare procedural specifications, with the IN parameters used by your
procedure and the OUT parameters consisting of the reference cursor for your
query and whatever other parameters you wish to have the stored procedure
return (e.g., error codes);
3) Write out the procedure body --- to open a reference cursor for a
query, use the "OPEN reference_cursor_name" syntax.
So your query could be rewritten as a stored procedure like the following:
PROCEDURE pMyTableResults (inVariableId IN VARCHAR2,
qMyTableResults OUT GenericCursorType,
outErrorNo OUT VARCHAR2) IS
BEGIN
OPEN qMyTableResults FOR
SELECT
column1,
column2
FROM
myTable
WHERE
id = inVariableId
outErrorNo := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
outErrorNo := 'FAIL';
END pMyTableResults;
(Oracle complains if you try to declare an OUT reference cursor as
qMyTableResults OUT REF CURSOR
so I've defined GenericCursorType elsewhere to use instead.)
The following is an Oracle package that I wrote that might help as an
example to draw upon. It has some simple exception-handling logic as well,
which you can ignore/adapt/whatever. It has two procedures which return
information from a table of states in the US (TESTSTATES, with ABBREV and
STATE as columns); one returns a state, the other returns all states within
TESTSTATES:
/** BEGIN PACKAGE */
CREATE OR REPLACE PACKAGE TestBed AS
TYPE GenericCursorType IS REF CURSOR;
PROCEDURE pSelectState (inStateAbbrev IN VARCHAR2,
outStateName OUT VARCHAR2,
outErrorNo OUT VARCHAR2);
PROCEDURE pSelectStates (inStateAbbrev IN VARCHAR2,
qSelectStatesList OUT GenericCursorType,
outErrorNo OUT VARCHAR2);
END TestBed;
/
CREATE OR REPLACE PACKAGE BODY TestBed AS
PROCEDURE pSelectState (inStateAbbrev IN VARCHAR2,
outStateName OUT VARCHAR2,
outErrorNo OUT VARCHAR2) IS
BEGIN
SELECT STATE INTO outStateName FROM TESTSTATES WHERE ABBREV =
inStateAbbrev;
outErrorNo := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
outErrorNo := 'FAIL';
END pSelectState;
PROCEDURE pSelectStates (inStateAbbrev IN VARCHAR2,
qSelectStatesList OUT GenericCursorType,
outErrorNo OUT VARCHAR2) IS
BEGIN
OPEN qSelectStatesList FOR
SELECT
ABBREV AS StateAbbreviation,
STATE AS StateName
FROM
TESTSTATES;
outErrorNo := 'SUCCESS';
EXCEPTION
WHEN OTHERS THEN
outErrorNo := 'FAIL';
END pSelectStates;
END TestBed;
/
/** END PACKAGE */
Hope this helps...
Regards,
Joel Parramore
> -----Original Message-----
> From: Deanna Schneider [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 13, 2001 9:53 AM
> To: CF-Talk
> Subject: simple oracle sp
>
>
> Hi Folks,
> Does anyone have a sample oracle stored procedure that accepts
> variables and
> returns multiple records? I.E. something to replace:
>
> <cfquery...>
> Select column1, column2
> FROM mytable
> WHERE id = #variableid#>
> That would return something like (note multiple rows with multiple values
> per row):
>
> column1 column2
> A B
> C F
> D U
>
> My big ol' oracle pl/sql book is just confusing me more.
>
> Thanks!
> -Deanna
>
>
>
> Deanna Schneider
> Interactive Media Developer
> [EMAIL PROTECTED]
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
