Problem in Procedure if then else

I have a procedure

CREATE OR REPLACE PROCEDURE insert_New_Applicant
(
str_FirstName NVARCHAR2, str_MiddleName NVARCHAR2, str_LastName NVARCHAR2, str_SSN NVARCHAR2, str_MailingAddress NVARCHAR2,
str_MailingCity NVARCHAR2, str_MailingState NVARCHAR2, str_MailingZip NVARCHAR2, str_PriPhone NVARCHAR2, dt_DateOfBirth DATE,
str_BirthCity NVARCHAR2, str_BirthState NVARCHAR2, str_BirthCoutry NVARCHAR2,str_Gender NVARCHAR2, str_PriRace NUMBER,
str_SecRace NUMBER, str_OtherRace NUMBER, str_CJCareer NVARCHAR2, str_EyeColor NVARCHAR2, str_HairColor NVARCHAR2,
str_Height NVARCHAR2, str_Weight NVARCHAR2, str_ArmedForces NVARCHAR2,str_ArmedBranch NVARCHAR2,str_CertLE NVARCHAR2 ,
str_lawEnfCity NVARCHAR2, str_LawEnfState NVARCHAR2, str_PIN NUMBER, str_PinQuestuion NUMBER, str_resetAnswer NVARCHAR2,
cjtc_ID NVARCHAR2, str_Email NVARCHAR2) IS
DECLARE
num_count NUMBER:=0;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from IPS_APPLICANT_PROFILE where SSN = ' || str_SSN INTO num_count;
if num_count = 0 then
INSERT INTO IPS_APPLICANT_PROFILE
(FIRST_NAME, MIDDLE_NAME, LAST_NAME,SSN, MAIL_ADDRESS, CITY, STATE_CODE, ZIP_CODE,
PRI_PHONE, DATE_OF_BIRTH, CITY_OF_BIRTH, STATE_OF_BIRTH,BIRTH_COUNTRY, GENDER,
PRI_RACE, SEC_RACE, OTHER_RACE, CJ_CAREER, EYE_COLOR, HAIR_COLOR, HEIGHT, WEIGHT, ARMED_FORCES, ARMED_FORCES_BRANCH,
CERTIFIED_LAW_ENFORCEMENT, LAW_ENFORCEMENT_CITY,LAW_ENFORCEMENT_STATE, PIN, PIN_RESET_QUESTION_ID, PIN_RESET_ANSWER, CJTC_ID, EMAIL_ID)
VALUES ( str_FirstName,str_MiddleName,str_LastName,str_SSN,str_MailingAddress,str_MailingCity,str_MailingStat e,str_MailingZip,
str_PriPhone,dt_DateOfBirth,str_BirthCity, str_BirthState,str_BirthCoutry,str_Gender,
str_PriRace,str_SecRace,str_OtherRace,str_CJCareer,str_EyeColor,str_HairColor,str_Height,str_Weight, str_ArmedForces,str_ArmedBranch,
str_CertLE,str_lawEnfCity,str_LawEnfState, str_PIN,str_PinQuestuion,str_resetAnswer,cjtc_ID,str_Email );
else
RAISE_APPLICATION_ERROR (-20734,'A student already exists with SSN:'|| str_SSN);
end if;
END insert_New_Applicant;


when i execute it it give me an error "09:09:05 Error: ORA-00928: missing SELECT keyword" but if i remove if , then else and execute it with only insert statement it execute fine can any one help me what was wrong with this procedure.

Remove the DECLARE and change the EXECUTE IMMEDIATE as follows:

Code:

---


CREATE OR REPLACE PROCEDURE insert_new_applicant (
  str_firstname        NVARCHAR2,
  str_middlename      NVARCHAR2,
  str_lastname        NVARCHAR2,
  str_ssn              NVARCHAR2,
  str_mailingaddress  NVARCHAR2,
  str_mailingcity      NVARCHAR2,
  str_mailingstate    NVARCHAR2,
  str_mailingzip      NVARCHAR2,
  str_priphone        NVARCHAR2,
  dt_dateofbirth      DATE,
  str_birthcity        NVARCHAR2,
  str_birthstate      NVARCHAR2,
  str_birthcoutry      NVARCHAR2,
  str_gender          NVARCHAR2,
  str_prirace          NUMBER,
  str_secrace          NUMBER,
  str_otherrace        NUMBER,
  str_cjcareer        NVARCHAR2,
  str_eyecolor        NVARCHAR2,
  str_haircolor        NVARCHAR2,
  str_height          NVARCHAR2,
  str_weight          NVARCHAR2,
  str_armedforces      NVARCHAR2,
  str_armedbranch      NVARCHAR2,
  str_certle          NVARCHAR2,
  str_lawenfcity      NVARCHAR2,
  str_lawenfstate      NVARCHAR2,
  str_pin              NUMBER,
  str_pinquestuion    NUMBER,
  str_resetanswer      NVARCHAR2,
  cjtc_id              NVARCHAR2,
  str_email            NVARCHAR2
)
IS
/* DECLARE  <== Remove this */
  num_count  NUMBER := 0;
BEGIN
  /* Change this:
  EXECUTE IMMEDIATE    'select count(*) from IPS_APPLICANT_PROFILE where SSN = '
                    || str_ssn
                INTO num_count;
  To this: */
  SELECT COUNT (*)
    INTO num_count
    FROM ips_applicant_profile
    WHERE ssn = str_ssn;

  IF num_count = 0
  THEN
      INSERT INTO ips_applicant_profile
                  (first_name, middle_name, last_name, ssn,
                  mail_address, city, state_code,
                  zip_code, pri_phone, date_of_birth,
                  city_of_birth, state_of_birth, birth_country,
                  gender, pri_race, sec_race, other_race,
                  cj_career, eye_color, hair_color, height,
                  weight, armed_forces, armed_forces_branch,
                  certified_law_enforcement, law_enforcement_city,
                  law_enforcement_state, pin, pin_reset_question_id,
                  pin_reset_answer, cjtc_id, email_id
                  )
          VALUES (str_firstname, str_middlename, str_lastname, str_ssn,
                  str_mailingaddress, str_mailingcity, str_mailingstate,
                  str_mailingzip, str_priphone, dt_dateofbirth,
                  str_birthcity, str_birthstate, str_birthcoutry,
                  str_gender, str_prirace, str_secrace, str_otherrace,
                  str_cjcareer, str_eyecolor, str_haircolor, str_height,
                  str_weight, str_armedforces, str_armedbranch,
                  str_certle, str_lawenfcity,
                  str_lawenfstate, str_pin, str_pinquestuion,
                  str_resetanswer, cjtc_id, str_email
                  );
  ELSE
      raise_application_error (-20734,
                              'A student already exists with SSN:' || str_ssn
                              );
  END IF;
END insert_new_applicant;
/

---

;)
PS: Use the "code, /code" wrappers to keep the format of your code.