Identifier must be declared? PL/SQL error

Question

This is the program I wrote:

set serveroutput on;
declare
  b empl.name1%type;
  r varchar;   --can i change this to r empl.designation%type; ?
begin
  r:=&designation;                      --getting input for the designation
  dbms_output.put_line('hello');            --random output to check for errors
  select name1 into b from empl where designation=r;   --i want all the names from the table
  dbms_output.put_line('name'||b);                   --employee where designation is as entered
  dbms_output.put_line(' closed');                 --by user,should i loop this statement?
end;

When I enter designation as 'a' (which is entered in the table already) I get an error identifier 'a' is not declared. What does that mean? Does the select statement take one row at a time? So if I loop it will I get all the rows? Or should i use a cursor? Why does SQL Developer not accept %rowtype?

I changed my program to this:

set serveroutput on;
declare
  cursor cempl is select name1,designation from empl;
  b empl.name1%type;
  des empl.designation%type;
  r empl.designation%type;
begin
  r:='meow';
  dbms_output.put_line('hello');
  open cempl;
  if cempl%ISOPEN then
    loop
      fetch cempl into b,des;
      if des=r then
        dbms_output.put_line('name'||b);
      end if;
      exit when cempl%notfound;
    end loop;
    close cempl;
    dbms_output.put_line(' closed');
  end if;
end;

Whenever I get an input like r:=&r and imagine I enter 'a' it says identifier 'a' must be declared, but its a value in the table! Why should it be declared, but if its given in the program like above it doesn't give an error. Instead it repeats the last row twice!

Answer 1

There are a few questions to answer here:

• 'Identifier not found' errors: &designation is a SQL*Plus substitution variable. When you enter a value for &designation, SQL*Plus replaces &designation with the text of what you entered. So, if you enter the vaue a, the line

  r:=&designation;
  

  becomes

  r:=a;
  

  The error arises because Oracle doesn't know of anything called a. You haven't declared a variable called a, and there isn't a procedure or function or anything else it could find with the name a. If you want the end result to be r:='a';, you would need to write r:='&designation';

• SELECT ... INTO ... only works if the query returns exactly one row. If no rows are returned, you will get a no data found error, and if more than one row is returned, you will get a too many rows error. You should only use SELECT ... INTO ... if you know there will only be one result. If there may be more than one result, you should use a cursor.

• 'Why does SQL Developer not accept %rowtype'? It should do - could you come up with an example that causes a problem for you?

• In your second example, you're getting the last row repeated because you're not exiting the loop immediately after the cursor fails to find any more rows. You should move the exit when line to immediately below the fetch line.