Oracle result sets from SP

[gasparrelli]

I need to have CF display the results of an Oracle stored proc which selects multiple rows from an Oracle table. Right now I have an Oracle SP which returns results by using a ref cursor. I'm not sure however how to have CF loop through the result set. Do I use CFStoredProc and some other related tag to output the cursor contents? I'm lost on this and need help!

Thanks in advance for any advice on how to output this data. I can't just write a spiffy query or view because my stored proc also de-encrypts data...

Kim

 

[iza]

yes, to display the results of an oracle stored procedure you HAVE TO use cfstoredproc
when you call the stored procedure with cfstoredproc you specify which variables are in, out, inout --> you can use them as usual after the procedure is called
the cfsoredproc result returns the result of the function (if your stored procedure was actually a function)

 

[gasparrelli]

Thanks so much for helping - can I possibly get a little more detail from you? If I have a multi row recordset returned from Oracle what datatype do I give it as the out param? What type if object is returned for me to loop through? An array, or do I somehow reference the returned rows as a query? ...I can get single fields out with no problem using procparam and specifying the datatype, but I'm not sure how to reference a cursor full of data...

Also (and perhaps more importantly) you mentioned I can call a function using CFStoredProc which returns rows. How do I do this? Using named notation? Any chance I can ask for a short example of calling a function which returns many rows and what datatypes/tags you use to loop through the results?

Thanks again, your expertise in this is extremely helpful.

Kim

 

[iza]

ouah ! i didn't get the point. I actually never tried to play with a whole row or cursor returned from the db !
but ... looking at the doc it seems that you can do that, and that the way to do that is to use <cfprocresult> that apparently stores a whole row and is used as a query result
the example given is

<cfstored proc procedure=&quot;foo_proc&quot; ....>
<cfprocresult name=res1>
...
<cfoutput query=&quot;res1&quot;>#name#, #date_col#<br></cfoutput>
<cfoutput>
number of records : #res1.recordcount#
which colums are returned : #res1.columlist#
</cfoutput>

please let me know if it does the trick. I think it does, i hope it does !!!

 

[gasparrelli]

Iza, that's the first thing I was looking at too, but the part I can't figure out is that I think I need a cfprocparam OUT but can't figure out the datatype. Maybe I'm wrong on that though. Maybe it's just the strange nature of the de-encryption SP that's hanging me up. I did however find a good article on the Allaire website that describes a method I think I can use to solve this problem. It's

http://www.allaire.com/Handlers/index.cfm?ID=8353&Method=Full

Instead of using a ref cursor to output rows they suggest using a char_array in the SP output to a cfprocparam dt varchar, creating a CF array, and looping through the array using CFQuery. I think I can get this to work, I'll let you know...

 

[iza]

ok, let me know !!!
but have you tried returning a whole row (cursor) (from the stored proc) and accessing it thru cfprocresult ?? i think it should work ... if i have some time i might test that tomorrow ...