How to return multiple values through function

Question

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

Littlefoot · Accepted Answer · 2018-05-14 20:27:19Z

1

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>

answered May 14, 2018 at 20:27

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Abinnaya Over a year ago

yes i can use this but could you please give some ex without using cursor but bit confusing, i also tried putting the retuen statement outside the loop then also doesnt works

Abinnaya Over a year ago

also i am getting error for using the pipeline [unsupported data type]

Abinnaya Over a year ago

create or replace FUNCTION "F_WORKSPACE_LOGIN_USERS" (p_email varchar2) return t_dp_tab pipelined is l_error varchar2(1000); l_workspace 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 pipe row(t_dp_row(i.id)); end loop; return ; end;

Abinnaya Over a year ago

now i am getting output asT6HPQ5LF7,T6XBXVAA1,T905JLZ62,T7CN08JPQ,T9MV4732M,T5PGS72NA,T5A4YHMUH,TAAFTFS0P,T69BE9T2A,T85D2D8MT,T858U7SF4,T9D16DF5X,T9DHDV61G,T9D17RDT3,T5Y03HDQ8,T5F5QPRK7 but i need output should be one by one

Littlefoot Over a year ago

Concatenate CHR(10) at the end of each line (if that's what you're asking).

|

Collectives™ on Stack Overflow

How to return multiple values through function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related