I'm not completely clear if you're asking about how to write the proc or how to
call the proc. You call the proc via ColdFusion like so:
<cfstoredproc procedure=""
datasource=""
username=""
password="">
<cfprocparam dbvarname="in_varNameinProc"
cfsqltype="cf_sql_varchar" value="theValue" type="in">
<cfprocresult name =
"whatWoudldHaveBeenNameinCfquery">
</cfstoredproc>
Put in the procedure name, prefix the schema name and the package name if
applicable.
DSN, user, pass, all normal from your cfquery experience. cfprocresult is the
same as the name attribute in cfquery, i.e. the name you'll use to reference
your result set.
cfprocparam, you'll have one of these for each input variable, in order as they
appear in the stored proc; the dbvarname is the name in the stored proc, the
cfsqltype is the data type, then of course you reference the value you are
passing in.
If you're also needing to know how to write a stored proc in Oracle, that's
probably beyond the scope of this list, but suffice it to say you need to
ensure that the proc returns a cursor (result set). You mentioned an issue
whenever you have more than one row of data...shot in the dark, but maybe your
problem is related to the issue here:
http://www.dba-oracle.com/t_pl_sql_plsql_select_into_clause.htm That one was a
stickler for me when I moved from SQL Server, which is much more forgiving of
these things.
Best,
Shannon
> I have never used stored procedures before but have been working on
> some code where they might be helpful to use with CFSTOREPROC instead
> of CFQUERY since I am dealing with a ton of records. Can anyone
> provide an example of a simple stored procedure which will return a
> set of data with more than 1 row? I got one to work when I am just
> getting 1 row of data but am getting errors when my stored procedure
> has more than 1 row. Thanks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:335316
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
