DECLARE
   v_pass_word    VARCHAR2 (10);
   v_conf_paswd   VARCHAR2 (10);
   a              NUMBER;
BEGIN
   v_pass_word := :online_reg1.pass_word;
   v_conf_paswd := :online_reg1.confirm_pswd;

   IF :online_reg1.student_name IS NULL
   THEN
      MESSAGE ('enter the student name');
   ELSIF :online_reg1.user_name IS NULL
   THEN
      MESSAGE ('enter the User name');
   ELSIF :online_reg1.pass_word IS NULL
   THEN
      MESSAGE ('enter the password');
   ELSIF :online_reg1.confirm_pswd IS NULL
   THEN
      MESSAGE ('confirm password');
   ELSIF :online_reg1.dob IS NULL
   THEN
      MESSAGE ('enter the DOB');
   ELSIF :online_reg1.email_id IS NULL
   THEN
      MESSAGE ('enter the Email Id');
   ELSIF :online_reg1.phone IS NULL
   THEN
      MESSAGE ('enter the Phone No');
   END IF;

   IF v_pass_word != v_conf_paswd
   THEN
      a := SHOW_ALERT ('ALERT_INVALID');
   END IF;

   INSERT INTO online_reg1
               (student_name, user_name,
                pass_word, confirm_pswd,
                dob, email_id, phone
               )
        VALUES (:online_reg1.student_name, :online_reg1.user_name,
                :online_reg1.pass_word, :online_reg1.confirm_pswd,
                :online_reg1.dob, :online_reg1.email_id, :online_reg1.phone
               );

   COMMIT;
END;

SELECT count(*)
				INTO v_count
				FROM online_reg1
				WHERE lower(user_name) = :online_reg1.user_name
				AND lower(pass_word) = :online_reg1.pass_word;

IF v_count=1 THEN
		call_form('online_enroll');
		ROLLBACK;
		ELSE
			a := show_alert('ALERT_INVALID');
		END IF;

WHERE lower(user_name) = :online_reg1.user_name

WHERE lower(user_name) = lower(:online_reg1.user_name)

SELECT exam_paper
  FROM online_course1
 WHERE course_id IN (SELECT course_id
                       FROM online_course1
                      WHERE course = :course)

CREATE TABLE ONLINE_ENROLL1
(
  EXAM_ID          NUMBER,
  USER_NAME        VARCHAR2(10 BYTE),
  COURSE_ID        NUMBER,
  EXAM_DATE        DATE,
  ENROLLMENT_DATE  DATE,
  STATUS           CHAR(1 BYTE)                 DEFAULT 'N'
)


ALTER TABLE ONLINE_ENROLL1 ADD (
  CONSTRAINT CHECK_USER_NAME1
 CHECK (LENGTH(user_name) BETWEEN 5 AND 12));


ALTER TABLE ONLINE_ENROLL1 ADD (
  CONSTRAINT ENROLL_COURSEID_FK 
 FOREIGN KEY (COURSE_ID) 
 REFERENCES ONLINE_COURSE (COURSE_ID));

DECLARE
   v_courseid   NUMBER;
   v_status     CHAR (1)      := 'N';
BEGIN
   IF :online_enroll1.exam_date IS NULL
   THEN
      MESSAGE ('enter the Exam date');
   ELSIF :online_enroll1.enrollment_date IS NULL
   THEN
      MESSAGE ('enter the enrollment date');
   ELSIF :course IS NULL
   THEN
      MESSAGE ('select the coursename');
   ELSIF :exam_paper IS NULL
   THEN
      MESSAGE ('select the exam paper');
   END IF;

   SELECT course_id
     INTO v_courseid
     FROM online_course1
    WHERE exam_paper = :exam_paper;

   INSERT INTO online_enroll1
               (exam_id, course_id, status
               )
        VALUES (examid_seq.NEXTVAL, v_courseid, v_status
               );

   COMMIT;
END;

CREATE TABLE ONLINE_COURSE1
(
  COURSE_ID   NUMBER,
  COURSE      VARCHAR2(10 BYTE),
  EXAM_PAPER  VARCHAR2(10 BYTE),
constraint courseid_pk primary key(course_id)

)

DECLARE
   v_courseid   NUMBER;
   v_status     CHAR (1) := 'N';
BEGIN
   IF :onlin_enroll.exam_date IS NULL
   THEN
      MESSAGE ('enter the Exam date');
   ELSIF :onlin_enroll.enrollment_date IS NULL
   THEN
      MESSAGE ('enter the enrollment date');
   ELSIF :course IS NULL
   THEN
      MESSAGE ('select the coursename');
   ELSIF :exam_paper IS NULL
   THEN
      MESSAGE ('select the exam paper');
   END IF;

   SELECT course_id
     INTO v_courseid
     FROM online_course
    WHERE course = :block4.course AND exam_paper = :block4.exam_paper;

   INSERT INTO onlin_enroll
               (exam_id, user_name, course_id, exam_date,
                enrollment_date, status
               )
        VALUES (48, 'sonykk', v_courseid, :onlin_enroll.exam_date,
                :onlin_enroll.enrollment_date, v_status
               );

   COMMIT;
END;