CODEWITHSUNDEEP
plsqloracle10g
Awlad Liton
Awlad Liton

Reputation: 9351

how to return a cursor or a result set from a oracle stored function

I have a stored function

 CREATE OR REPLACE FUNCTION schedule(name in varchar2,pass in varchar2 )
    begin
    select t.name,s.starttime from traininfo t,schedule s, trainslot ts 
    where t.trainid in( select ts.trainid from trainslot 
    where ts.slotid in (select s.slotid from schedule s 
    where s.source='dhaka'
    and s.dest='bogra' ))
    end

I want to return this result set using a cursor.

Upvotes: 0

Views: 2440

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

I don't see where you are using either of the input parameters in your function. I'll assume that is either intentional or an oversight because you're simplifying the code. It also appears that your query is missing conditions to join between the traininfo, schedule, and trainslot tables in the outer query. It seems odd that your nested IN statements are turning around and querying the schedule and trainslot tables given this lack of join conditions. I don't know whether this is a result of copy-and-paste errors or something that was missed in posting the question or whether these are real problems. I'll make a guess at the query you're intending to write but if my guess is wrong, you'll have to tell us what your query is supposed to do (posting sample data and expected outputs would be exceptionally helpful for this).

CREATE OR REPLACE FUNCTION schedule(name in varchar2,pass in varchar2 )
  RETURN sys_refcursor
is
  l_rc sys_refcursor;
begin
  open l_rc
   for select t.name, s.starttime
         from traininfo t,
              schedule  s,
              trainslot ts
        where t.trainid = ts.trainid
          and s.slotid  = ts.slotid
          and s.source  = 'dhaka'
          and s.dest    = 'borga';
  return l_rc;
end;

Upvotes: 2

Related Questions