/* 
	Copyright 2009 apps2fusion.com 
		       Kishore Ryali
	Oracle Application Express Tutorials   
*/

/* Table Script */
CREATE TABLE xx_person_details
(
 person_id        NUMBER NOT NULL
,first_name       VARCHAR2(50)
,last_name        VARCHAR2(50)
,email            VARCHAR2(100)
,student_flag     VARCHAR2(1)
,name_of_university VARCHAR2(100)
,last_update_date DATE
,last_update_login INTEGER
,last_updated_by INTEGER
,creation_date DATE
,created_by INTEGER
);

/* Sequence Script */
CREATE SEQUENCE   "XX_PERSON_ID_S"
MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 1
CACHE 20 NOORDER  NOCYCLE;

/* API to insert person record */
CREATE OR REPLACE PROCEDURE xx_insert_person_prc (
   p_person_rec   IN OUT   xx_person_details%ROWTYPE
)
IS
BEGIN
   SELECT xx_person_id_s.NEXTVAL
     INTO p_person_rec.person_id
     FROM DUAL;
   p_person_rec.last_update_date := SYSDATE;
   p_person_rec.creation_date := SYSDATE;
   p_person_rec.last_update_login := -1;
   p_person_rec.last_updated_by := 71690;
   p_person_rec.created_by := 71690;
   INSERT INTO xx_person_details
        VALUES p_person_rec;
END xx_insert_person_prc;
/

/* Script to populate sample data */
DECLARE
   l_person_rec         xx_person_details%ROWTYPE;
   l_person_empty_rec   xx_person_details%ROWTYPE;
BEGIN
   for i in 1..40 loop
       l_person_rec := l_person_empty_rec;
       l_person_rec.first_name := 'FN' || i;
       l_person_rec.last_name := 'LN' || i ;
       l_person_rec.email := 'f.l.' || i || '@gmail.com';
       l_person_rec.student_flag := case mod(i,2) when 0 then 'N' when 1 then 'Y' end;
       xx_insert_person_prc (l_person_rec);
   end loop;
   COMMIT;
END;
/