Can a Function return more than one value ?

This is a popular interview question that I have been asked ( & I have asked in turn ) from time immemorial.

A Function can return more than one value in PL/SQL.

We can do it in two ways :-

1. Use OUT Parameters in the Function :-

CREATE OR REPLACE FUNCTION FN_BAD ( RETVAL OUT NUMBER )
RETURN INTEGER
AS
BEGIN
RETVAL:= 200;
RETURN 100;
END;
/

SELECT FN_BAD FROM DUAL;

ORA-06553: PLS-306: wrong number or types of arguments in call to 'FN_BAD'

SET SERVEROUTPUT ON;

DECLARE
ln_BAD NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(FN_BAD(ln_BAD));
DBMS_OUTPUT.PUT_LINE(ln_BAD);
END;
/


100
200

PL/SQL procedure successfully completed.

Well, this is bad coding....but it works ! I just tried it for fun :)

2. Use a PipleLined Function. A Piplined Function needs to have an assosicated Collection Type.