Return multiple values in varchar2

I want to return the values of several columns (With a function) in a varchar2 but I get an error when I choose several columns in select.

FUNCTION FU_PRUEBAS (P_IDNUM MAC.IDNUM%TYPE) RETURN VARCHAR2 IS REGISTRO VARCHAR2(100);
     BEGIN
     select NOMBRES, FECHANACIMIENTO INTO REGISTRO
     from MAC WHERE IDNUM = P_IDNUM;
     RETURN REGISTRO;
     

Error:

Error(17,6): PL/SQL: SQL Statement ignored

Error(18,6): PL/SQL: ORA-00947: there are not enough values

Answer

Two relatively simple options – if they suit your needs. You didn’t really explain what you expect to get as a result so – this might, or might not be applicable to your situation.

Anyway, here you go:

First option uses listagg function which returns a concatenated string (as you wanted to return varchar2). Drawback is that it won’t work for the result longer than 4000 characters, but – in that case – you can use XMLAGG.

SQL> create or replace function fu_pruebas (par_deptno in number)
  2    return varchar2
  3  is
  4    retval varchar2(200);
  5  begin
  6    select listagg(ename, ', ') within group (order by ename)
  7      into retval
  8      from emp
  9      where deptno = par_deptno;
 10    return retval;
 11  end;
 12  /

Function created.

SQL> select fu_pruebas(10) result from dual;

RESULT
------------------------------------------------------------------------------
CLARK, KING, MILLER

SQL>

---

Another one concatenates different columns into a string; you might need to use e.g. to_char or to_date functions in certain cases, but – generally – it works as follows:

SQL> create or replace function fu_pruebas_2 (par_empno in number)
  2    return varchar2
  3  is
  4    retval varchar2(200);
  5  begin
  6    select ename ||' - '|| job ||' - '|| sal
  7      into retval
  8      from emp
  9      where empno = par_empno;
 10    return retval;
 11  end;
 12  /

Function created.

SQL> select fu_pruebas_2(7654) result from dual;

RESULT
------------------------------------------------------------------------------
MARTIN - SALESMAN - 1250

SQL>