Answers for "oracle function return resultset"

oracle function return resultset

CREATE TYPE object_row_type as OBJECT (
  object_type VARCHAR(18),
  object_name VARCHAR(30)
);

CREATE TYPE object_table_type as TABLE OF object_row_type;

CREATE OR REPLACE FUNCTION get_all_objects 
  RETURN object_table_type PIPELINED AS
BEGIN
    FOR cur IN (SELECT * FROM all_objects)
    LOOP
      PIPE ROW(object_row_type(cur.object_type, cur.object_name));   
    END LOOP; 
    RETURN;
END;

SELECT * FROM TABLE(get_all_objects);

oracle function return

CREATE OR REPLACE FUNCTION to_date_check_null(dateString IN VARCHAR2, 
	dateFormat IN VARCHAR2) RETURN DATE IS
BEGIN 
    IF dateString IS NULL THEN
        return NULL;
    ELSE
        return to_date(dateString, dateFormat);
    END IF;
END;