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:330080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
