CODEWITHSUNDEEP
oracle-apexoracle-apex-5oracle-apex-5.1
Abinnaya
Abinnaya

Reputation: 223

How to return multiple values through function

I have created the below function that will return workspace details which the loggedin user has access to.

But this function is returning only the first record from the select list.

I need all the records to be displayed as output.

Please modify it and let me know.

CREATE OR REPLACE FUNCTION "F_WORKSPACE_LOGIN_USERS" (
  p_email VARCHAR2
) RETURN VARCHAR2 IS

  l_error       VARCHAR2(1000);
  l_workspace   VARCHAR2(1000);
  l_teams       VARCHAR2(1000);
  l_team        VARCHAR2(1000);
BEGIN
  FOR i IN ( SELECT a.name workspace,
                    a.team_id id
             FROM slackdatawarehouse.teams a,
                  ( SELECT TRIM(workspaces) workspaces
                    FROM alluser_workspaces_fact
                    WHERE lower(email) = lower(p_email)
  ) b
             WHERE a.team_id IN ( SELECT c.team_id
                                  FROM slackdatawarehouse.team_tokens c
  )
                   OR   instr(', '
  || lower(b.workspaces),', '
  || lower(a.name) ) > 0
  ORDER BY 1 ) LOOP
    l_teams := l_team
    || ','
    || i.id;
    l_teams := ltrim(rtrim(l_teams,','),',');
    RETURN l_teams;
  END LOOP;
END;

Current output is :

T6HPQ5LF7,T6XBXVAA1,T905JLZ62,T7CN08JPQ,T9MV4732M,T5PGS72NA,T5A4YHMUH,TAAFTFS0P,T69BE9T2A,T85D2D8MT,T858U7SF4,T9D16DF5X,T9DHDV61G,T9D17RDT3,T5Y03HDQ8,T5F5QPRK7

Required output is : 

T6HPQ5LF7
T6XBXVAA1
T905JLZ62

i need output like above as one by one

Upvotes: 2

Views: 1785

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

I don't know what that code really does (can't test it), but this might be the culprit:

    ...
    RETURN l_teams;    
  END LOOP;

As soon as code reaches the RETURN statement, it exits the loop and ... well, returns what's currently in L_TEAMS variable. Therefore, move RETURN out of the loop:

     ...
  END LOOP;
  RETURN l_teams;

If it still doesn't work as expected (which might be the case), have a look at pipelined functions (for example, on Oracle-base site) as they are designed to return values you seem to be looking for.

A simple example:

SQL> create or replace type t_dp_row as object
  2    (deptno number,
  3     dname  varchar2(20));
  4  /

Type created.

SQL> create or replace type t_dp_tab is table of t_dp_row;
  2  /

Type created.

SQL> create or replace function f_depts
  2    return t_dp_tab pipelined
  3  is
  4  begin
  5    for cur_r in (select deptno, dname from dept)
  6    loop
  7      pipe row(t_dp_row(cur_r.deptno, cur_r.dname));
  8    end loop;
  9    return;
 10  end;
 11  /

Function created.

SQL> select * from table(f_depts);

    DEPTNO DNAME
---------- --------------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL>

Upvotes: 1

Related Questions