-- create base table
create table PRESIDENTS(
ID NUMBER(10) not null,
NAME VARCHAR(32) not null,
BIRTHDATE DATE not null,
PARTY char(1) not null,
primary key (ID)
)

-- insert statements
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (1, 'George W. Bush', to_date('07/06/1946','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (2, 'Bill Clinton', to_date('08/19/1946','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (3, 'George H. W. Bush', to_date('06/12/1924','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (4, 'Ronald W. Reagan', to_date('02/06/1911','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (5, 'Jimmy Carter', to_date('10/01/1924','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (6, 'Gerald R. Ford', to_date('07/14/1913','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (7, 'Richard M. Nixon', to_date('01/09/1913','MM/DD/YYYY'),'R');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (8, 'Lyndon B. Johnson', to_date('08/27/1908','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (9, 'John F. Kennedy', to_date('05/29/1917','MM/DD/YYYY'),'D');
insert into PRESIDENTS (ID, NAME, BIRTHDATE, PARTY) values (10, 'Dwight D. Eisenhower', to_date('10/14/1890','MM/DD/YYYY'),'R');

-- create temp table to hold data between stored procedure and stored function
CREATE GLOBAL TEMPORARY TABLE "TEMP_PRESIDENTS"(
	ID NUMBER(10) not null,
	NAME VARCHAR(32) not null,
	BIRTHDATE DATE not null,
	PARTY char(1) not null
) ON COMMIT PRESERVE ROWS;

-- create stored procedure
CREATE PROCEDURE "LOAD_TEMP_PRESIDENTS" (
	partyParam CHAR
)
as
begin
	EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_PRESIDENTS';
	COMMIT;
	
	INSERT INTO TEMP_PRESIDENTS
	SELECT ID, NAME, BIRTHDATE, PARTY FROM PRESIDENTS WHERE PARTY = partyParam;
	COMMIT;
end;
/

-- sql to test stored procedure
call LOAD_TEMP_PRESIDENTS('D');
select * from TEMP_PRESIDENTS;

-- create type needed to return data from stored function
CREATE OR REPLACE TYPE "PRESIDENT_TYPE" AS OBJECT (
	ID NUMBER(10),
	NAME VARCHAR2(32),
	BIRTHDATE DATE,
	PARTY CHAR(1)
);

-- create table of PRESIDENT_TYPEs
CREATE OR REPLACE TYPE "PRESIDENT_TYPE_TABLE" AS TABLE OF "PRESIDENT_TYPE";

-- create stored function
CREATE OR REPLACE FUNCTION "PRESIDENTS_FUNC" (
 partyParam CHAR
)
return PRESIDENT_TYPE_TABLE pipelined
is
  PRAGMA AUTONOMOUS_TRANSACTION;

  TYPE ref0 is REF CURSOR;
  myCursor ref0;
  out_rec PRESIDENT_TYPE := PRESIDENT_TYPE(0, null, null, null);
  
BEGIN
	LOAD_TEMP_PRESIDENTS(partyParam);

	open myCursor for
		select
		id,
		name,
		birthdate,
		party
		from TEMP_PRESIDENTS;
	
	LOOP FETCH myCursor into
		out_rec.ID,
		out_rec.NAME,
		out_rec.BIRTHDATE,
		out_rec.PARTY;
	
	EXIT WHEN myCursor%NOTFOUND;
	PIPE ROW(out_rec);
	END LOOP;
	CLOSE myCursor;
	
	RETURN;
END;
/

-- sql to test stored function
select * from table(PRESIDENTS_FUNC('M'))