DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem in Procedure if then else

  1. #1
    Join Date
    Feb 2009
    Posts
    1

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Declare executes?

    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width