Records not showing results

CREATE TABLE manager (
      mgrid        INT PRIMARY KEY,
      fname        VARCHAR2(50),
      city         VARCHAR2(50)
);

The above is the manager table.

& Below is the code I wrote to get records.

DECLARE
      TYPE mgr_info IS RECORD (
            mgrid   manager.mgrid%TYPE,
            fname  manager.fname%TYPE,
            city   manager.city%TYPE
      );
        mgr    mgr_info;
        id   manager.mgrid%TYPE;
        name  manager.fname%TYPE;
        mcity   manager.city%TYPE;
BEGIN
      SELECT
            mgrid,
            fname,
            city
      INTO mgr
      FROM
            manager
      WHERE
            city = 'Mumbai';

      id := mgr.mgrid;
      name := mgr.fname;
      mcity := mgr.city;
      dbms_output.put_line('Manager ID = '
                           || id
                           || ' || Manager Name = '
                           || name
                           || ' || City = '
                           || mcity);

EXCEPTION
      WHEN no_data_found THEN
            dbms_output.put_line('No Matching Record Found');
      WHEN too_many_rows THEN
            dbms_output.put_line('More than one matching Record Found');
END;
/

The issue is I am not able to get proper input even though I have a row that is a manager who lives in Mumbai City.

What is I am not doing that I am not able to get proper output.

Below are the inputs for the table:

insert into manager values(101,'mohan','jaipur');
insert into manager values(102,'sohan','surat');
insert into manager values(103,'rohan', 'delhi');
insert into manager values(104,'jagmohan','mumbai');

Answer

The table has Mumbai, the select uses mumbai. The case is different. @Littlefoot was right to ask for the INSERT statements, we couldn’t have helped without them.

If you want the SELECT to work, you need to fold both table and query to the same case:

WHERE lower(city) = lower('Mumbai')

Advertisement

Answer