CODEWITHSUNDEEP
sqloracle-databaseplsql
meligira
meligira

Reputation: 711

Identifier 'input' must be declared - PL SQL

Every time I run this and add as an input the words 'yellow' or 'red' or 'blue', the same error appears.

set serveroutput on
undefine color1
undefine color2
accept color1 prompt 'Type the 1st primary color: '
accept color2 prompt 'Type the 2nd primary color: '
begin
   if &color1 = &color2 then
      dbms_output.put_line(&color1||' plus '||&color2||' then '||&color1);
   elsif (&color1 = 'red' and &color2 = 'blue') or (&color2 = 'red' and &color1 = 'blue') then
      dbms_output.put_line(&color1||' plus '||&color2||' is purple');
   elsif (&color1 = 'red' and &color2 = 'yellow') or (&color2 = 'red' and &color1 = 'yellow') then
      dbms_output.put_line(&color1||' plus '||&color2||' is orange');
   else
      dbms_output.put_line(&color1||' plus '||&color2||' is green');
   end if;
end;
/

ERROR:

ORA-06550: line 2, column 7:
PLS-00201: identifier 'BLUE' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

Please Help! :)

Upvotes: 1

Views: 1423

Answers (3)

Kirill Leontev
Kirill Leontev

Reputation: 10941

&color1 is a substitution variable, sqlplus basically replaces &color1 with user input contents - which has to be a single-quoted literal here. As long as it is not, your blue input is treated as a variable name, which is not defined.

So, you have two options - either to input quoted 'blue', 'yellow' etc. in sqlplus, or to replace &color1 with '&color1' in your code.

Upvotes: 1

Dimuthu
Dimuthu

Reputation: 150

Try this 

 set serveroutput on
    undefine color1
    undefine color2
    accept color1 prompt 'Type the 1st primary color: '
    accept color2 prompt 'Type the 2nd primary color: '
    begin
       if '&color1' = '&color2' then
          dbms_output.put_line('&color1'||' plus '||'&color2'||' then '||'&color1');
       elsif ('&color1' like 'red' and '&color2' like 'blue') or ('&color2' like 'red' and '&color1' like 'blue') then
          dbms_output.put_line('&color1'||' plus '||'&color2'||' is purple');
       elsif ('&color1' like 'red' and '&color2' like 'yellow') or ('&color2' like 'red' and '&color1' like 'yellow') then
          dbms_output.put_line('&color1'||' plus '||'&color2'||' is orange');
       else
          dbms_output.put_line('&color1'||' plus '||'&color2'||' is green');
       end if;
    end;
    /

Upvotes: 0

Dba
Dba

Reputation: 6649

Try like this,

set serveroutput on
undefine color1
undefine color2
accept color1 prompt 'Type the 1st primary color: '
accept color2 prompt 'Type the 2nd primary color: '
BEGIN
   IF '&color1' = '&color2' THEN
      dbms_output.put_line('&color1'||' plus '||'&color2'||' then '||'&color1');
   elsif ('&color1' LIKE 'red' AND '&color2' LIKE 'blue') OR ('&color2' LIKE 'red' AND '&color1' LIKE 'blue') THEN
      dbms_output.put_line('&color1'||' plus '||'&color2'||' is purple');
   elsif ('&color1' LIKE 'red' AND '&color2' LIKE 'yellow') OR ('&color2' LIKE 'red' AND '&color1' LIKE 'yellow') THEN
      dbms_output.put_line('&color1'||' plus '||'&color2'||' is orange');
   ELSE
      dbms_output.put_line('&color1'||' plus '||'&color2'||' is green');
   end if;
END;
/

Or it would be be better to store the color1 & color2 in a local variable like this,

SET serveroutput ON
undefine color1
undefine color2
accept color1 prompt 'Type the 1st primary color: '
accept color2 prompt 'Type the 2nd primary color: '
DECLARE 
   color_1 VARCHAR2(10) := '&color1';
   color_2 VARCHAR2(10) := '&color2';
BEGIN
   IF color_1 = color_2 THEN
      dbms_output.put_line(color_1||' plus '||color_2||' then '||color_1);
   elsif (color_1 LIKE 'red' AND color_2 LIKE 'blue') OR (color_2 LIKE 'red' AND color_1 LIKE 'blue') THEN
      dbms_output.put_line(color_1||' plus '||color_2||' is purple');
   elsif (color_1 LIKE 'red' AND color_2 LIKE 'yellow') OR (color_2 LIKE 'red' AND color_1 LIKE 'yellow') THEN
      dbms_output.put_line(color_1||' plus '||color_2||' is orange');
   ELSE
      dbms_output.put_line(color_1||' plus '||color_2||' is green');
   END IF;
end;

Upvotes: 1

Related Questions