Why is my IF being ignored in my PL?SQL script?

Question

When I run my script, I enter N when it asks "Will the payment method be cash (Y/N)?" but it still executes the statements under the IF...Why is this happening?

ACCEPT ExpNum NUMBER PROMPT 'Enter an expense number:  ';

SELECT *
FROM ExpByCC
WHERE ExpNum = &ExpNum;

SELECT IsCash,CashAmt
FROM EXPMAST
WHERE ExpNum = &ExpNum
AND IsCash = 'Y';

ACCEPT CashChoice PROMPT 'Will the payment method be cash (Y/N)?  ';

DECLARE
    ChoiceCash VARCHAR2(1);
    NumRowInExpByCC NUMBER;
BEGIN
    ChoiceCash := &CashChoice;

    IF ChoiceCash = 'Y'
    THEN
            UPDATE EXPMAST
            SET IsCash =  ChoiceCash,
                CashAmt = &CashAmount
            WHERE ExpNum = &ExpNum;
            COMMIT;
    ELSE
            &CType;
            &CCnum;
            &Amt;

    END IF;

END;
/

what is your output? code looks OK. You can invert if condition to avoid = vs == confusion, but it should not change a thing. — vav, Apr 22 '14 at 01:38
SQL> @addexpPay.sql Enter an expense number: 1 old 3: WHERE ExpNum = &ExpNum new 3: WHERE ExpNum = 1 no rows selected old 3: WHERE ExpNum = &ExpNum new 3: WHERE ExpNum = 1 I CASHAMT - ---------- Y 10.5 Will the payment method be cash (Y/N)? N Enter value for cashamount: — Stc5097, Apr 22 '14 at 01:39
might be hard to read, but i enter N and it still runs the first if — Stc5097, Apr 22 '14 at 01:39

score 1 · Answer 1 · answered Apr 22 '14 at 01:53

When you're using SQL*Plus substitution variables in PL/SQL, SQL*Plus is merely acting as a preprocessor. It has to provide substitution values for all the variables before it can send the PL/SQL block to the database to be executed. That means that even if you reference substitution variables in an ELSE block that isn't going to be reached, SQL*Plus still needs to determine the values to substitute. SQL*Plus can't execute PL/SQL code and the database can't prompt for human input.

Realistically, if you need to prompt for human input and you want those prompts to be driven by some sort of logic, SQL*Plus is generally not the tool you're looking for. There are any number of languages (shell scripts/ batch files among others) that have facilities for prompting for user input, executing simple conditional logic, and then invoking SQL*Plus (or using a built-in API) to interact with the database.

Why is my IF being ignored in my PL?SQL script?

1 Answers1