I also noticed that you've looped over and closed the cursor - if you do that, CF won't be able to read from it.
Just open it and let CF worry about fetching from it and closing it. mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ 2010/1/24 James Holmes <[email protected]>: > You need to have the ref cursor as an "out" parameter: > > create or replace > PROCEDURE EMP_REF_CURSOR (v_cur_emp OUT cur_emp) > AS... > > This means that your procedure will need to be in a package so you can > declare the ref cursor outside of the procedure. > > mxAjax / CFAjax docs and other useful articles: > http://www.bifrost.com.au/blog/ > > > > 2010/1/24 fun and learning <[email protected]>: >> >> Hi All - >> >> I was learning on oracle stored procedures and retrieving the results from >> Coldfusion. I am using ref cursor for reading a table in the database. The >> procedure is as follows: >> >> create or replace >> PROCEDURE EMP_REF_CURSOR >> >> AS >> >> TYPE cur_emp IS REF CURSOR RETURN EMPLOYEES%ROWTYPE; >> >> v_cur_emp cur_emp; >> v_employees employees%ROWTYPE; >> >> BEGIN >> OPEN v_cur_emp FOR >> SELECT * FROM EMPLOYEES; >> >> LOOP >> FETCH v_cur_emp >> INTO v_employees; >> >> EXIT WHEN v_cur_emp%NOTFOUND; >> DBMS_OUTPUT.PUT_LINE('p_employee_id = ' || v_employees.employee_id || ', >> p_first_name = ' || v_employees.first_name || ', p_last_name = ' || >> v_employees.last_name); >> END LOOP; >> CLOSE v_cur_emp; >> END EMP_REF_CURSOR; >> >> I am using the following code in coldfusion to access the stored proc: >> >> <cfstoredproc datasource="oraclelearn" procedure="emp_ref_cursor"> >> >> <cfprocresult name="returnEmployees" resultset="1"> >> >> </cfstoredproc> >> >> <cfdump var="#returnEmployees#"> >> >> Coldfusion is throwing an error that it does not find the returnEmployees. >> Can anyone please tell me what I am doing wrong. The stored procedure runs >> fine in SQL Developer. >> >> Thanks a lot.... >> >> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
