Oracle function with select all from tables

Question

SELECT DISTINCT L.* FROM LABALES L , MATCHES M 
WHERE M.LIST LIKE '%ENG' 
ORDER BY L.ID

I need to create function with this select, I tried this but it doesn't work.

CREATE OR REPLACE FUNCTION getSoccerLists
RETURN varchar2  IS 
list varchar2(2000);
BEGIN
   SELECT DISTINCT L.* FROM LABALES L , MATCHES M 
   WHERE M.LIST LIKE '%ENG' 
   ORDER BY L.ID
   return list;
END;

How will I create function that returns all from table L.

Thanks

Answer 1

You may use implicit result using DBMS_SQL.RETURN_RESULT(Oracle12c and above) in a procedure using a cursor to your query.

CREATE OR REPLACE PROCEDURE getSoccerLists 
AS
x SYS_REFCURSOR;
BEGIN
  OPEN x FOR SELECT DISTINCT L.* FROM LABALES L 
   JOIN  MATCHES M  ON ( 1=1 ) -- join condition
       WHERE M.LIST LIKE '%ENG' 
       ORDER BY L.ID; 
   DBMS_SQL.RETURN_RESULT(x);
END;
/

then simply call the procedure

EXEC getSoccerLists;

For lower versions(Oracle 11g) , you may use a print command to display the cursor's o/p passing ref cursor as out parameter.

CREATE OR REPLACE PROCEDURE getSoccerLists (x OUT SYS_REFCURSOR) 
AS
BEGIN
  OPEN x FOR SELECT DISTINCT L.* FROM LABALES L 
   JOIN  MATCHES M  ON ( 1=1 ) -- join condition
       WHERE M.LIST LIKE '%ENG' 
       ORDER BY L.ID; 
END;
/

Then, in SQL* Plus or running as script in SQL developer and Toad, you may get the results using this.

VARIABLE r REFCURSOR;
EXEC  getSoccerLists (:r);

PRINT r;

Another option is to use TABLE function by defining a collection of the record type of the result within a package.

Refer Create an Oracle function that returns a table

Answer 2

I guess this questions is a repetition of the your previously asked question, where you wanted to get all the columns of tables but into separate column. I already answered in stating this you cannot do if you call your function via a SELECT statement. If you call your function in a Anoymous block you can display it in separate columns.

Here Oracle function returning all columns from tables

Alternatively, you can get the results separated by a comma(,) or pipe (|) as below:

CREATE OR REPLACE
  FUNCTION getSoccerLists
    RETURN VARCHAR2
  IS
    list VARCHAR2(2000);
  BEGIN
    SELECT col1
      ||','
      ||col2
      ||','
      ||col2
    INTO LIST
    FROM SOCCER_PREMATCH_LISTS L ,
      SOCCER_PREMATCH_MATCHES M
    WHERE M.LIST LIKE '%' || (L.SUB_LIST)  || '%'
    AND (TO_TIMESTAMP((M.M_DATE      || ' '      || M.M_TIME), 'DD.MM.YYYY HH24:MI') >
      (SELECT SYSTIMESTAMP AT TIME ZONE 'CET' FROM DUAL
      ))
    ORDER BY L.ID");
  Return list;
  End;

Note here if the column size increased 2000 chars then again you will lose the data.

Edit:

From your comments

I want it to return a table set of results.

You then need to create a table of varchar and then return it from the function. See below:

CREATE TYPE var IS  TABLE OF VARCHAR2(2000);
/

CREATE OR REPLACE
FUNCTION getSoccerLists
  RETURN var
IS
  --Initialization
  list VAR :=var();
BEGIN
  SELECT NSO ||',' ||NAME BULK COLLECT INTO LIST FROM TEST;
  RETURN list;
END;

Execution:

select * from table(getSoccerLists);

Note: Here in the function i have used a table called test and its column. You replace your table with its columnname.

Edit 2:

--Create a object with columns same as your select statement
CREATE   TYPE v_var IS  OBJECT
(
col1 NUMBER,
col2 VARCHAR2(10)
)
/
--Create a table of your object
CREATE OR REPLACE TYPE var IS TABLE OF v_var;
/

CREATE OR REPLACE FUNCTION getSoccerLists
  RETURN var
IS
  --Initialization
  list VAR :=var();
BEGIN
   --You above object should have same columns with same data type as you are selecting here 
  SELECT v_var( NSO ,NAME) BULK COLLECT INTO LIST FROM TEST;
  RETURN list;
END;

Execution:

select * from table(getSoccerLists);

Answer 3

This is not an answer on how to build a function for this, as I'd recommend to make this a view instead:

CREATE OR REPLACE VIEW view_soccer_list AS
  SELECT * 
  FROM soccer_prematch_lists l
  WHERE EXISTS
  (
    SELECT *
    FROM soccer_prematch_matches m 
    WHERE m.list LIKE '%' || (l.sub_list) || '%' 
      AND TO_TIMESTAMP((m.m_date || ' ' || m.m_time), 'DD.MM.YYYY HH24:MI') > 
            (SELECT SYSTIMESTAMP AT TIME ZONE 'CET' FROM DUAL)
  );

Then call it in a query:

SELECT * FROM view_soccer_list ORDER BY id;

(It makes no sense to put an ORDER BY clause in a view, because you access the view like a table, and table data is considered unordered, so you could not rely on that order. The same is true for a pipelined function youd access with FROM TABLE (getSoccerLists). Always put the ORDER BY clause in your final queries instead.)