Take the 2-minute tour ×

We are trying to display a record set in a webform using the cffunction in coldfusion. But we are unable to display the ouptut..We are just getting a single value(last value in the record set).The sql function is returning ref cursor.Please help us resolve this issue! thanks!

<cfset bare_drive_result = functionname('x','y')> 

<cffunction name="functionname" hint="Gets all customer from the database" returntype="query"> 
    <cfargument name="sPcwQua" type=any>
    <cfargument name="sPcwAcc" type=any>

    <cfquery name="getRkBareDrive" datasource= "#PCW_dsn#"> 
       select pacakagename.functionname('#sPcwQua#','#sPcwAcc#')  bare_drive_result
       from dual

    <cfreturn getRkBareDrive> 

share|improve this question
Does it return multiple records when executed in your db? Have you tried using a procedure/cfstoredproc kb2.adobe.com/cps/176/tn_17633.html * livedocs.adobe.com/coldfusion/8/htmldocs/… –  Leigh Jul 14 '11 at 18:24

4 Answers 4

You have a scoping issue. The name of the query inside your method is getRKBareDrive, however, you are setting the result of that function call to another variable name - bare_drive_result.

The code would actually be:

<cfoutput query="bare_drive_result">#bare_drive_result.bare_drive_result</cfoutput>

And inside your function, you need to add this line just after the cfargument tags:

<cfset var getRkBareDrive = "" />

However, that does not solve your immediate problem. Have you tried doing:

<cfdump var="#bare_drive_result#" /> 

to see what is actually returned to ColdFusion

share|improve this answer
Yes to what Scott said tho I'm not sure it's the whole issue here. Try isolating the CFFUNCTION to the top or to a CFC to perhaps make it clearer to yourself, but the result of the function call is stored in a var called bare_drive_result not getRKBareDrive (which is the var name used in the function itself). –  mujimu Jul 15 '11 at 16:17

Everything @Scott Stroz said in his answer is true - 1) you are confusing variable names between the code and the inline function, 2) use a var scope to define the getRKBareDrive variable inside your function (not directly part of the issue being an inline function, but good practice), and 3) try to CFDUMP the result instead of CFOUTPUT it. However I don't believe the core issue is addressed, which lies with this part of your question:

The sql function is returning ref cursor

So one glaring issue is that you are returning a REFCURSOR not a simple value, so you cannot just CFOUTPUT it but instead need to take certain steps so ColdFusion knows it is a query set. REFCURSOR can be returned in calls from Stored Procedures via CFPROCRESULT (or via CFPROCPARAM type="OUT" CFSQLType="CF_SQL_REFCURSOR" if it's a returned parameter not a result).

So... try coverting the CFQUERY call to a CFSTOREDPROC. Here's some sample code that assumes you can call your package/function directly as a stored proc vs through a query. I removed the inline function as it's adding too much complexity here (again see Scott's answer) -- just try bare code as a way to get the call working.

<cfstoredproc procedure="pacakagename.functionname" datasource= "#PCW_dsn#">
    <cfprocparam type="IN" CFSQLType="CF_SQL_VARCHAR", value="x">    
    <cfprocparam type="IN" CFSQLType="CF_SQL_VARCHAR", value="y">    
    <cfprocresult name="bare_drive_result" >
<cfdump var="#bare_drive_result#">

If you see results in the dump, you should be able replace the dump and output the fields within the ref cursor just like a normal CFQUERY result within a CFOUTPUT query="bare_drive_result" call.

From CF8 documentation on CFPROCRESULT:

CFML supports Oracle 8 and 9 Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way can be allocated and deallocated from memory within the execution of one application. To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes the ColdFusion JDBC database driver to put Oracle reference cursors into a result set. (You cannot use this method with Oracle's ThinClient JDBC drivers.)

share|improve this answer
+1. Good explanation. –  Leigh Jul 15 '11 at 18:58

Try this

<cfoutput query="getRkBareDrive">#getRkBareDrive.bare_drive_result#</cfoutput> 
share|improve this answer
we tried this..its not working..we are still getting one value/. –  beena Jul 14 '11 at 9:24

I suspect Scott's solution will help you out, though if you are still having issues you can add a result parameter to your query to help troubleshoot & see exactly what is being passed.

<cfquery name="getRkBareDrive" datasource= "#PCW_dsn#" result="qryname"> 

then dump that:

<cfdump var="#qryname#" />

The returned structure should show you the query passed in, number of results etc [i suspect it is actually returning only one], see the full description here: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

share|improve this answer
Dumping the actual query object should also include that information - SQL executed, parameters passed, etc. - assuming its a later version of ColdFusion. –  Scott Stroz Jul 14 '11 at 15:35

Your Answer


