I can't seem to get cfprocresult to dump anything (it's undefined). The proc runs and returns data but for some reason it doesn't put the record set in it. Any ideas?
> 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:335325 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
