drop tablespace patients  INCLUDING CONTENTS AND DATAFILES ;
drop user bpeluser CASCADE ;

Create tablespace patients DATAFILE '/data/app/oracle/oradata/orcl/patients.dbf' SIZE 5M ;

create user          bpeluser
identified by        Welcome1
default   tablespace patients
temporary tablespace temp
quota 10M ON patients ;

grant all on PATIENTS to BPELUSER;
grant CREATE SESSION to BPELUSER;
grant CREATE TABLE to BPELUSER;
grant ALTER TABLESPACE to BPELUSER;
GRANT CREATE PROCEDURE TO BPELUSER ;
GRANT CREATE TYPE TO BPELUSER ;
ALTER USER BPELUSER QUOTA 10M ON patients;

connect bpeluser/Welcome1 ;

drop table "PATIENTS" ;
drop type patient_t ;
drop type PHYSICALCHARACTERISTIC_TBL_T ;
drop type PHYSICALCHARACTERISTIC ;

  CREATE TABLE "PATIENTS"
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"FIRST_NAME" VARCHAR2(50 BYTE), 
	"LAST_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	"BIRTH_DATE" DATE, 
	"GENDER" VARCHAR2(1 BYTE), 
	 CONSTRAINT "PATIENTS_PK" PRIMARY KEY ("ID")
 )  TABLESPACE patients
 ;

REM INSERTING into PATIENTS
Insert into PATIENTS (ID,FIRST_NAME,LAST_NAME,BIRTH_DATE,GENDER) values (1,'John','Doe',to_date('11/12/1958','DD/MM/YYYY'),'M');
Insert into PATIENTS (ID,FIRST_NAME,LAST_NAME,BIRTH_DATE,GENDER) values (2,'Myriam','Mkeba',to_date('02/01/1957','DD/MM/YYYY'),'F');


  CREATE OR REPLACE TYPE "PHYSICALCHARACTERISTIC_TBL_T" AS TABLE OF PHYSICALCHARACTERISTIC;
/

  CREATE OR REPLACE TYPE "PHYSICALCHARACTERISTIC" AS OBJECT
( dateOfMeasurement date
, whatWasMeasured varchar2(200)
, measuredValue number(20,5)
, unitOfMeasurement varchar2(50)
);
/

  CREATE OR REPLACE TYPE "PATIENT_T" AS object
( first_name varchar2(50)
, last_name varchar2(100)
, birth_date date
, initials varchar2(20)
, gender varchar2(1)
, physical_characteristics PHYSICALCHARACTERISTIC_TBL_T
);
/

CREATE OR REPLACE PACKAGE PATIENT_DATA_SERVICES AS

function get_patient_id
( p_first_name in varchar2
, p_last_name  in varchar2
) return number
;

function get_patient_record
( p_patient_id in number
) return  patient_t
;

END PATIENT_DATA_SERVICES;
/

CREATE OR REPLACE PACKAGE BODY PATIENT_DATA_SERVICES AS

function get_patient_id
( p_first_name in varchar2
, p_last_name  in varchar2
) return number
 AS
  l_ptt_id number;
  BEGIN
    select ptt.id
    into   l_ptt_id
    from   patients ptt
    where  ptt.first_name = p_first_name
    and    ptt.last_name  = p_last_name
    ;
    RETURN l_ptt_id;
  END get_patient_id;

function get_patient_record
( p_patient_id in number
) return  patient_t
is   
  l_patient patient_t;
  l_physicalcharacteristic physicalcharacteristic;
  l_physicalcharacteristic_BMI physicalcharacteristic;
begin
  select patient_t(ptt.first_name, ptt.last_name, ptt.birth_date , null, ptt.gender , PHYSICALCHARACTERISTIC_TBL_T())
  into   l_patient
  from   patients ptt
  where  ptt.id = p_patient_id
  ;
  l_patient.initials := 'Q.E.D.';
  l_physicalcharacteristic := physicalcharacteristic( to_date('12-09-2010','DD-MM-YYYY'), 'Body Weight', '76','kg');
  l_physicalcharacteristic_bmi := physicalcharacteristic( to_date('11-07-2010','DD-MM-YYYY'), 'BMI', '32','kg/m2');
  l_patient.physical_characteristics := PHYSICALCHARACTERISTIC_TBL_T(l_physicalcharacteristic_bmi, l_physicalcharacteristic);
  return l_patient;
end get_patient_record;


END PATIENT_DATA_SERVICES;
/