Need help filtering user input in PL/SQL by using IF statements

Question

I am doing a PL/SQL script that will get input from an user, and then it will find out through an if statement if it has to add data to 1 or 2 tables. I am using IF statements and a WHILE loop to accomplish this task, but it isn't working properly. Also this script it's part of a main menu. Any input is appreciated it.

-- q3.sql
-- Question name goes here!!

VARIABLE g_options VARCHAR2(1000);

PROMPT 'Vehicle Inventory Record'
ACCEPT p_Options PROMPT 'Does this car has any Optional Equipment or Accessories?(YES    or NO)';
-- Option table
ACCEPT p_OCode PROMPT 'Enter the code for the option picked (Ex. CD2):'
ACCEPT p_ODescription PROMPT 'Enter the description for the option picked:'
ACCEPT p_OPrice PROMPT 'Enter the price for the option picked:'
-- Car table
ACCEPT p_SerialNo PROMPT 'What is the serial number of the car?'
ACCEPT p_Make PROMPT 'What is the make of the car?'
ACCEPT p_Model PROMPT 'What is the model of the car?'
ACCEPT p_Year PROMPT 'What is the color of the car?'
ACCEPT p_Trim PROMPT 'What is the trim of the car?'
ACCEPT p_PurchFrom PROMPT 'Where was the car purchased from?'
ACCEPT p_PurchInvNo PROMPT 'What was the invoice number of the purcahse?'
ACCEPT p_PurchDate PROMPT 'When was the car purcahsed?(ex. 13-FEB-06)'
ACCEPT p_PurchCost PROMPT 'How much did he car cost?'
ACCEPT p_ListPrice PROMPT 'What was the list price of the car?'


DECLARE
  n_numb number := 1;

BEGIN
  --WHILE  n_numb < 2 LOOP

  IF '&p_Options' = 'YES' THEN
  :g_options := 'Input will be added into the option table AND car table';
  -- Insert statement goes here
  n_numb := 2;

  ELSIF '&p_Options' = 'NO' THEN
  :g_options := 'Input will only be added to the car table';
  -- Insert statement goes here
  n_numb := 2;

  ELSE 
  :g_options := ' The correct input for the first prompt was "YES" or "NO", you entered something else'
  --:g_options :=  :g_options || ' The script will now end, please run it again'
  n_numb := 0;

  END IF;
  --END LOOP;
END;
/

I commented the loop out, and the n_numb variable, and I get this error:

END IF;
  *
ERROR at line 22:
ORA-06550: line 22, column 7:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like
between || multiset member SUBMULTISET_
The symbol ";" was substituted for "END" to continue.

UPDATE 1: I added the semicolon at the end of the statement, but then I got an error saying that p_Options had to be declared.I just changed p_Options to '&p_Options', and it says that the script ran successfully, BUT I don't see any output. Like:' The script will now end, please run it again' .

This is all I see!

old   7:       IF '&p_Options' = 'YES' THEN
new   7:       IF '' = 'YES' THEN
old  12:       ELSIF '&p_Options' = 'NO' THEN
new  12:       ELSIF '' = 'NO' THEN

PL/SQL procedure successfully completed.

UPDATE 2: I got the if statement to work. BUT the loop will not work if I were to type something other than YES or NO as an input. I basically get no return, and I can keep on going pressing enter forever. :S

------------On a different note----------

How do I extract the information that the user/s entered and add it into a table using an INSERT statement?

Miguel, could you specify exactly what isn't working properly? Also, I can't see how the loop gets started since n_numb is set to 2 and the loop only runs if n_numb is less than 2. Surely the loop never starts. It's also worth suggesting you take baby steps. Why not just do one accept statement and comment out all the others. See if that works, then do the next one and so on. — DavidHyogo, Aug 13 '12 at 00:35
You forgot `;` at end of this line: `:g_options := ' The correct input for the first prompt was "YES" or "NO", you entered something else'` — Nagh, Aug 13 '12 at 00:46
Miguel, which database server are you working with? You've tagged this question as mysql, but then you talked about PL/SQL, which sounds like the Oracle procedural language, then the clincher is the ORA- error number. I assume this is Oracle. The error is talking about a misplaced END. So, simplify the whole script, commenting out all the IF clauses etc and just make sure you've got the right number of END statements. — DavidHyogo, Aug 13 '12 at 00:49
Thanks for that, I am still not getting the output I desire. :S — Miguel Leon Duque, Aug 13 '12 at 01:00
Miguel. You're new so I should explain some etiquette. Nagh has helped you get a bit further with your problem and now that this question is correctly tagged, an Oracle expert might notice and help you get the final solution. You should not now add a whole extra question. Get this stage solved, then post a new question about the insert statement. Make sure you show people what you tried and exactly what doesn't seem to be working. Give them a less cluttered example to work with. Since you're new, I'll give you a hint: look up dynamic SQL in the Oracle docs. — DavidHyogo, Aug 13 '12 at 02:16
I understand, I think I know why my loop wasn't working. Anyways I was reading about the INSERT statement in PL/SQL, and I don't see an example of what I want to do anywhere. :S I will keep trying. — Miguel Leon Duque, Aug 13 '12 at 02:32
@MiguelLeonDuque you get no output because you don't have a [`set serveroutput on` statement](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#sthref2166) - Nagh has that in his version but you may have missed its significance. The `old`/`new` lines are visible because you didn't `set verify off` (also in Nagh's answer). It loops forever because you prompt and accept once *before the PL/SQL block starts*, so the value of `&p_Option` never changes - you either exit the loop after the first iteration (if it's `YES` or `NO`), or you never exit. — Alex Poole, Aug 13 '12 at 12:01
I'm guessing the teacher of this class is very "seasoned" (ie old), and hasn't updated the syllabus in 20 years. — tbone, Aug 13 '12 at 16:05

score 0 · Answer 1 · answered Aug 13 '12 at 01:20

Alittle modified your script with a bit of debugging added. It seems to be working as is:

SET VERIFY OFF
SET FEED OFF
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
VARIABLE g_options VARCHAR2(1000);
PROMPT 'Vehicle Inventory Record'
ACCEPT p_Options PROMPT 'Does this car has any Optional Equipment or Accessories?(YES or NO): ';
/*
-- Option table
ACCEPT p_OCode PROMPT 'Enter the code for the option picked (Ex. CD2):'
ACCEPT p_ODescription PROMPT 'Enter the description for the option picked:'
ACCEPT p_OPrice PROMPT 'Enter the price for the option picked:'
-- Car table
ACCEPT p_SerialNo PROMPT 'What is the serial number of the car?'
ACCEPT p_Make PROMPT 'What is the make of the car?'
ACCEPT p_Model PROMPT 'What is the model of the car?'
ACCEPT p_Year PROMPT 'What is the color of the car?'
ACCEPT p_Trim PROMPT 'What is the trim of the car?'
ACCEPT p_PurchFrom PROMPT 'Where was the car purchased from?'
ACCEPT p_PurchInvNo PROMPT 'What was the invoice number of the purcahse?'
ACCEPT p_PurchDate PROMPT 'When was the car purcahsed?(ex. 13-FEB-06)'
ACCEPT p_PurchCost PROMPT 'How much did he car cost?'
ACCEPT p_ListPrice PROMPT 'What was the list price of the car?'
*/

DECLARE
  n_numb number := 1;

BEGIN
  --WHILE  n_numb < 2 LOOP
  IF '&p_Options' = 'YES' THEN
  :g_options := 'Input will be added into the option table AND car table';
  -- Insert statement goes here
  dbms_output.put_line('insert with YES');
  n_numb := 2;

  ELSIF '&p_Options' = 'NO' THEN
  :g_options := 'Input will only be added to the car table';
  -- Insert statement goes here
  dbms_output.put_line('insert with NO');
  n_numb := 2;

  ELSE
  :g_options := ' The correct input for the first prompt was "YES" or "NO", you entered something else';
  --:g_options :=  :g_options || ' The script will now end, please run it again'
  dbms_output.put_line('ELSE');
  n_numb := 0;

  END IF;
  --END LOOP;
END;

If you have further problems, update your question with more details.

I used your code, and the if statement works. BUT the loop will not work if I type something other than YES or NO as an input. I basically get no return, and I can keep going pressing enter forever. — Miguel Leon Duque, Aug 13 '12 at 01:44

Need help filtering user input in PL/SQL by using IF statements

1 Answers1