Shannon is right on. However, I tend to use a weak ref cursor like
PROCEDURE procName (
(input params)
p_cursor OUT sys_refcursor)
Makes thing much easier.
Your example is not returning cursors. All you are doing is looping over a
cursor without outputting anything except to the put_line.
Try something like:
PROCEDURE GETALL_SP (
my_data OUT sys_refcursor
) IS
BEGIN
OPEN my_data FOR
select distinct a, b, c
from mytable
where d not in (select d from getDeleted_V);
END GETALL_SP;
Also, make sure you have the latest Datadirect Drivers from Adobe -- the
older ones did not support ref cursors from stored procs. I think they have
to be at least the version 3.5
Stored procedures are very powerful, especially when you want to do
complicated things. I can't remember the last time I used a CFQUERY. I
strongly recommend you look at the Oracle 10g documentation. It has
excellent information and examples about stored procedures, ref cursors,
and everything else. A good editor like TOAD makes like much easier -- the
pl/sql debugging features in TOAD are something I can't do without.
Warren
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:335389
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
