By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees (first_name, last_name, salary) AS
SELECT 'First' || LEVEL, 'Last' || LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 15;
15 rows affected
CREATE FUNCTION f_sal
RETURN SYS.ODCIVARCHAR2LIST
IS
v_names SYS.ODCIVARCHAR2LIST;
BEGIN
SELECT first_name || ' ' || last_name
BULK COLLECT INTO v_names
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
return v_names;
END;
/
SELECT * FROM TABLE(f_sal);
| COLUMN_VALUE |
|---|
| First15 Last15 |
| First14 Last14 |
| First13 Last13 |
| First12 Last12 |
| First11 Last11 |
| First10 Last10 |
| First9 Last9 |
| First8 Last8 |
| First7 Last7 |
| First6 Last6 |
CREATE OR REPLACE FUNCTION f_sal
RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
BEGIN
FOR n IN (
SELECT first_name || ' ' || last_name AS name
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY
)
LOOP
PIPE ROW (n.name);
END LOOP;
END;
/
SELECT * FROM USER_ERRORS;
SELECT * FROM TABLE(f_sal);
| COLUMN_VALUE |
|---|
| First15 Last15 |
| First14 Last14 |
| First13 Last13 |
| First12 Last12 |
| First11 Last11 |
| First10 Last10 |
| First9 Last9 |
| First8 Last8 |
| First7 Last7 |
| First6 Last6 |
CREATE OR REPLACE FUNCTION f_sal
RETURN SYS_REFCURSOR
IS
v_names SYS_REFCURSOR;
BEGIN
OPEN v_names FOR
SELECT first_name || ' ' || last_name AS name
FROM employees
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;
return v_names;
END;
/
DECLARE
v_names SYS_REFCURSOR := f_sal();
v_name VARCHAR2(100);
BEGIN
LOOP
FETCH v_names INTO v_name;
EXIT WHEN v_names%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( v_name );
END LOOP;
END;
/
1 rows affected
dbms_output:
First15 Last15
First14 Last14
First13 Last13
First12 Last12
First11 Last11
First10 Last10
First9 Last9
First8 Last8
First7 Last7
First6 Last6