-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|