SQL> DECLARE
  2   TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30);
  3  FUNCTION getEmpArray (p_no IN NUMBER)
  4  RETURN EMPARRAY
  5  AS
  6  l_data EmpArray := EmpArray();
  7  CURSOR c_emp
  8  IS
  9  SELECT ename FROM scott.EMP
 10  where deptno=p_no;
 11  BEGIN
 12  FOR emp_rec IN c_emp LOOP
 13  l_data.extend;
 14  l_data(l_data.count) := emp_rec.ename;
 15  END LOOP;
 16  --RETURN (l_data);
 17  --dbms_output.put_line('v_tt');
 18  END;
 19  begin null; end;
 20  /

PL/SQL procedure successfully completed.

. . .

SELECT getEmpArray (10)
    INTO v_te
    FROM dual;
. . .

SQL> DECLARE
  2  TYPE v_te is VARRAY(20) OF VARCHAR2(30);
  3  TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30);
  4  FUNCTION getEmpArray (p_no IN NUMBER)
  5  RETURN EMPARRAY
  6  AS
  7  l_data EmpArray := EmpArray();
  8  CURSOR c_emp
  9  IS
 10  SELECT ename FROM scott.EMP
 11  where deptno=p_no;
 12  BEGIN
 13  FOR emp_rec IN c_emp LOOP
 14  l_data.extend;
 15  l_data(l_data.count) := emp_rec.ename;
 16  END LOOP;
 17  RETURN (l_data);
 18  END;
 19  begin
 20  SELECT getEmpArray (10)
 21  into v_te
 22  FROM dual;
 23  dbms_output.put_line(v_te);
 24  end;
 25  /
SELECT getEmpArray (10)
       *
ERROR at line 20:
ORA-06550: line 20, column 8:
PLS-00231: function 'GETEMPARRAY' may not be used in SQL
ORA-06550: line 20, column 8:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 20, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 22:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored

SQL> DROP TABLE emp;
 
Table dropped
SQL> CREATE TABLE emp(ename VARCHAR2(10), deptno NUMBER);
 
Table created
SQL> INSERT INTO emp VALUEs('lalit',10);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SET SERVEROUTPUT ON ;
SQL> DECLARE
  2     TYPE EMPARRAY IS TABLE OF EMP.ENAME%TYPE;
  3     L_DATA EMPARRAY:=EMPARRAY();
  4     V_TE   EMPARRAY:= EMPARRAY();
  5     FUNCTION GETEMPARRAY(P_NO IN NUMBER) RETURN EMPARRAY AS
  6  
  7     BEGIN
  8  
  9        FOR EMP_REC IN (SELECT ENAME FROM EMP WHERE DEPTNO = P_NO) LOOP
 10           L_DATA.EXTEND;
 11           L_DATA(L_DATA.COUNT) := EMP_REC.ENAME;
 12        END LOOP;
 13        RETURN(L_DATA);
 14     END;
 15  BEGIN
 16     V_TE := GETEMPARRAY(10);
 17     DBMS_OUTPUT.PUT_LINE(V_TE(1));
 18  END; 
 19  /
 
lalit
 
PL/SQL procedure successfully completed

SQL> DROP TABLE emp;
 
Table dropped
SQL> CREATE TABLE emp(ename VARCHAR2(10), deptno NUMBER);
 
Table created
SQL> INSERT INTO emp VALUEs('lalit',10);
 
1 row inserted
SQL> INSERT INTO emp VALUEs('OraFAQ',10);
 
1 row inserted
SQL> INSERT INTO emp VALUEs('mapps0999',10);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SET SERVEROUTPUT ON ;
SQL> DECLARE
  2     TYPE EMPARRAY IS TABLE OF EMP.ENAME%TYPE;
  3     L_DATA EMPARRAY := EMPARRAY();
  4     V_TE   EMPARRAY := EMPARRAY();
  5     FUNCTION GETEMPARRAY(P_NO IN NUMBER) RETURN EMPARRAY AS
  6  
  7     BEGIN
  8  
  9        FOR EMP_REC IN (SELECT ENAME FROM EMP WHERE DEPTNO = P_NO) LOOP
 10           L_DATA.EXTEND;
 11           L_DATA(L_DATA.COUNT) := EMP_REC.ENAME;
 12        END LOOP;
 13        RETURN(L_DATA);
 14     END;
 15  BEGIN
 16     V_TE := GETEMPARRAY(10);
 17     FOR I IN 1 .. V_TE.COUNT LOOP
 18        DBMS_OUTPUT.PUT_LINE(V_TE(I));
 19     END LOOP;
 20  END; 
 21  /
 
lalit
OraFAQ
mapps0999
 
PL/SQL procedure successfully completed

SQL> SELECT
  2  listagg(sal,'-') within group (order by empno)
  3  FROM
  4  scott.emp
  5  WHERE deptno=10;
LISTAGG(SAL,'-')WITHINGROUP(ORDERBYEMPNO)
------------------------------------------------------
2450-5000-1300

SQL> DROP TABLE EMP;
 
Table dropped
SQL> CREATE TABLE emp(ename VARCHAR2(10), deptno NUMBER);
 
Table created
SQL> INSERT INTO emp VALUEs('lalit',10);
 
1 row inserted
SQL> INSERT INTO emp VALUEs('OraFAQ',10);
 
1 row inserted
SQL> INSERT INTO emp VALUEs('mapps0999',10);
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT DEPTNO,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(ENAME, ' - '))
  3               KEEP(DENSE_RANK LAST ORDER BY CURR),
  4               ' - ') AS EMPLOYEES
  5    FROM (SELECT DEPTNO,
  6                 ENAME,
  7                 ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) AS CURR,
  8                 ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) - 1 AS PREV
  9            FROM EMP)
 10   GROUP BY DEPTNO
 11  CONNECT BY PREV = PRIOR CURR
 12         AND DEPTNO = PRIOR DEPTNO
 13   START WITH CURR = 1;
 
    DEPTNO EMPLOYEES
---------- --------------------------------------------------------------------------------
        10 OraFAQ - lalit - mapps0999