Oracle - return multiple tables

Question

I have 3 tables selected from

SELECT STUDENT_ID, STUDENT_CLASS FROM STUDENT;
SELECT EMP_NAME, EMP_SKILL FROM EMP;
SELECT PATIENT_NAME, DISEASE FROM PATIENT;

Note: there is no relation between these 3 tables but I need this in the function so I can pass it to the API.

I have a function GET_DATA. I need to return all 3 tables using single return, is that possible?

FUNCTION GET_DATA(PARA1 NUMBER DEFAULT 1,PARA2 NUMBER DEFAULT 1)RETURN SYS_REFCURSOR AS
  RESULT SYS_REFCURSOR;
BEGIN

 OPEN RESULT FOR SELECT STUDENT_ID, STUDENT_CLASS FROM STUDENT;
 OPEN RESULT FOR SELECT EMP_NAME, EMP_SKILL FROM EMP;
 OPEN RESULT FOR SELECT PATIENT_NAME, DISEASE FROM PATIENT;
  RETURN RESULT ;
END GET_DATA;

Answer 1

As proposed by Gordon, using UNION ALL is the solution

SELECT STUDENT_ID, STUDENT_CLASS FROM STUDENT
UNION ALL
SELECT EMP_NAME, EMP_SKILL FROM EMP
UNION ALL
SELECT PATIENT_NAME, DISEASE FROM PATIENT

However keep in mind that :

1. If you don't alias the columns, the column names of the resultset will be those of the first query

2. If you have mixed data types for your columns (CHAR/NUMBER/..) you'll end up with the following error :

ORA-01790: expression must have same datatype as corresponding expression

to circumvent this, cast your numeric columns (if any) to CHAR

3. It's better to add a column defining from which table the records comes from as it will probably be necessary in the programs using the API

You should thus end up with something like this:

SELECT 'STUDENT' AS ORIGTABLE,  TO_CHAR(STUDENT_ID) As COL1 , STUDENT_CLASS AS COL2 FROM STUDENT
UNION ALL
SELECT 'EMP' AS ORIGTABLE, EMP_NAME AS COL1, EMP_SKILL AS COL2 FROM EMP
UNION ALL
SELECT 'PATIENT' AS ORIGTABLE, PATIENT_NAME AS COL1, DISEASE AS COL2 FROM PATIENT