Problem in Procedure if then else

[imi_swim]

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.

[LKBrwn_DBA]

Remove the DECLARE and change the EXECUTE IMMEDIATE as follows:

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.