0

I have this function : 

CREATE OR REPLACE FUNCTION checkLiveSoccerOdd (p_FIXID    VARCHAR2,
                                               p_TYPE     VARCHAR2,
                                               p_RES      VARCHAR2)
   RETURN NUMBER
IS
   odd   NUMBER;
BEGIN
   SELECT O.Odd
     INTO odd
     FROM LIVE M, ODDS O
    WHERE     M.FIXID = O.FIXID(+)
          AND M.FIXID = p_FIXID
          AND O.TYPE = p_TYPE
          AND O.RES = p_RES;

   RETURN odd;
END;

Now i need to get more columns in query, for example: 

Select  M.*,  O.*

I tried with cursor but I don't get results. Can anyone help me with this?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
civesuas_sine
  • 119
  • 2
  • 15
  • Return a `sys_refcursor` inplace of just returning a number. – XING Apr 04 '18 at 12:27
  • You say "you want more columns in query" - do you mean exactly that? You want more values returned by the SQL statement but you still only need to return a single value as the result of the function? – Christian Palmer Apr 04 '18 at 12:29
  • I want all columns in these two tables to be returned. – civesuas_sine Apr 04 '18 at 12:43
  • @ChristianPalmer Yes, I would like to get all values returned from tables, when I pass these parameters.... With this select i get only O.Odd. – civesuas_sine Apr 04 '18 at 13:57
  • Use ANSI `JOIN` syntax for `LEFT JOIN`s. Get rid of the confusing `(+)` notation – Kaushik Nayak Apr 04 '18 at 14:15
  • 1
    @KaushikNayak wise words, especially since the presence of the `and o.type = p_type and o.res = p_res` predicates make it an inner join anyway! (To the OP: to make the query do a left outer join, you should add the `(+)`s to all references to the odds tables, e.g. `and o.type (+) = p_type and o.res (+) = p_res`) – Boneist Apr 04 '18 at 14:24

1 Answers1

1

Try this:

CREATE OR REPLACE FUNCTION checkLiveSoccerOdd (p_FIXID    VARCHAR2,
                                               p_TYPE     VARCHAR2,
                                               p_RES      VARCHAR2)
   RETURN SYS_REFCURSOR
IS
   COL   SYS_REFCURSOR;
BEGIN
OPEN COL FOR
   SELECT M.*,O.*    
     FROM LIVE M, ODDS O
    WHERE     M.FIXID = O.FIXID(+)
          AND M.FIXID = p_FIXID
          AND O.TYPE = p_TYPE
          AND O.RES = p_RES;

   RETURN COL;
END;
XING
  • 9,608
  • 4
  • 22
  • 38
  • That helps a bit but i get it all in one column, can i get all columns separated ? – civesuas_sine Apr 04 '18 at 12:44
  • If you execute it in a select statement you get it in a single column but if you execute in a anonymous block, you can display it separatly. – XING Apr 04 '18 at 12:46