Writing a simple SELECT Stored Procedure in Oracle PL/SQL
You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter.

Quick procedure to test with:

CREATE OR REPLACE PROCEDURE passenger_details
(p_passenger_details OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN p_passenger_details FOR
SELECT 'test test' as Full_Name, 10 as Age, 'alien' as Nationality, 'foo' as Category, 
'Name' as Airline_Name, 'wobble' as Class_Type
FROM dual;
END passenger_details;
/


Test in SQL*Plus:


SQL> variable mycursor refcursor;
SQL> exec passenger_details ( :mycursor );

PL/SQL procedure successfully completed.

SQL> print mycursor;

FULL_NAME    AGE NATIO CAT AIRL CLASS_
--------- ---------- ----- --- ---- ------
test test     10 alien foo Name wobble

SQL>