In my previous post, Oracle PL/SQL: SELECT INTO, I explained how to correctly use the Oracle PLSQL SELECT INTO statement. Here, I will expand on that further by describing common error messages you may see when using SELECT INTO, what you should do about them and most importantly, how you should prepare to handle them in your PLSQL logic.

For avoidance of doubt, in this blog post I will continue to use the same widely known “hr schema”. The entity-relationship diagram is as below.

ORA-01422: TOO_MANY_ROWS

As mentioned in my previous post, the SELECT INTO statement is used to fetch a single row of data from a table into variables. This means that if your SQL query returns more than one row of data, you are going to run into ORA-01422: TOO_MANY_ROWS error. Using the following example code I can trigger the TOO_MANY_ROWS error (note, this is a similar example I used in the previous post to return an entire row of data into a record). The reason this error will occur is that I have changed the WHERE clause to try and return records where the employee ID is either 100 and 101. My database table contains both and therefore 2 records would be returned.

DECLARE
 
  rec_employee employees%ROWTYPE;
 
BEGIN
 
  -- get the data for employee ID 100 and 101
  SELECT * 
  INTO rec_employee
  FROM employees
  WHERE employee_id IN (100,101);
 
  -- write employee details
  dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name );
  dbms_output.put_line('Email: ' || rec_employee.email);
  dbms_output.put_line('Number: '|| rec_employee.phone_number);
 
END;
/

Top tip to try and avoid this error: Databases have primary keys for a reason…. use them! It isn’t possible for more than 1 row in a database table to have the same value in primary key column(s), therefore, restricting queries to filter by the primary key of a record will return only one row, as we need.

Whilst ideally you won’t be using a list of IDs in your SQL query (I did this simply for illustrative purposes), it’s possible that you might have a query that does this by name or by status or by any other query which has the ability (rightly or wrongly.. e.g, database table incorrectly having duplicate records or simply that there are 2 employees with the same name 🙂 ) to return more than one row. If you don’t want to come across unexpected errors, you ought to add some exception handling. Using the same example, I can add some exception handling as follows:

DECLARE
  
  rec_employee employees%ROWTYPE;

BEGIN

  -- get the data for employee ID 100
  SELECT * 
  INTO rec_employee
  FROM employees
  WHERE employee_id IN (100,101);

  -- write employee details
  dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name );
  dbms_output.put_line('Email: ' || rec_employee.email);
  dbms_output.put_line('Number: '|| rec_employee.phone_number);

EXCEPTION

    WHEN TOO_MANY_ROWS THEN

         dbms_output.put_line('More than one record returned!!!');

END;
/

The exception handling added here simply catches the thrown error and writes a message using the standard dbms_output.put_line procedure describing that there is an issue. In the “real” world ( 🙂 ) you may wish to write the error to an output file or log it to your error logging framework so that the error is flagged, however, you will see in the image below that the PLSQL block no longer returns an error message:

ORA-01403: NO_DATA_FOUND

Similar to the above, as mentioned in my previous post, the SELECT INTO statement is used to fetch a single row of data from a table into variables. This means that is your SQL query returns less than one row of data, you are going to run into ORA-01403: NO_DATA_FOUND error. Using the following example code, I can trigger the NO_DATA_FOUND error (note, this is a similar example I used in the previous post to return an entire row of data into a record). The reason this error will occur is that I have changed the WHERE clause to try and return records where the employee ID is 1. My database table does not contain a record where the employee ID is 1 and therefore 0 records would be returned.

DECLARE
 
  rec_employee employees%ROWTYPE;
 
BEGIN
 
  -- get the data for employee ID 1
  SELECT * 
  INTO rec_employee
  FROM employees
  WHERE employee_id = 1;
 
  -- write employee details
  dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name );
  dbms_output.put_line('Email: ' || rec_employee.email);
  dbms_output.put_line('Number: '|| rec_employee.phone_number);
 
END;
/

Top tip to try and avoid this error: aggregate functions such as COUNT, MIN, MAX, AVERAGE, etc by default return a single row of data which is what we want when using SELECT INTO. Even if the query itself does not return any data results, the aggregate function would return the count of “nothing” and therefore would return a COUNT = 0 result. This approach could be used like follows:

DECLARE
 
  rec_employee employees%ROWTYPE;
  v_count INT(3);
 
BEGIN

  -- get the count of records where employee ID is 1
  SELECT COUNT(1) 
  INTO v_count
  FROM employees
  WHERE employee_id = 1;
 
  IF v_count > 0 THEN 
    -- get the data for employee ID 1
    SELECT * 
    INTO rec_employee
    FROM employees
    WHERE employee_id = 1;

    -- write employee details
    dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name );
    dbms_output.put_line('Email: ' || rec_employee.email);
    dbms_output.put_line('Number: '|| rec_employee.phone_number);
  
  ELSE
   
    dbms_output.put_line('No records returned!!!');
  
  END IF;
 
END;
/

An explanation of this example is as follows:

• I have declared a record named rec_employee with a datatype which matches the data row in the employees table. This record will therefore be able to hold an entire row of data which matches the database. In addition, I have declared a variable named v_count which will simply be used to hold an integer value relating to the number of records found based on the WHERE clause within my SQL query.
• I used the SELECT INTO statement in order to select the count of data rows from the employees table and assign it to the v_count variable that I declared above.
• I used the IF logic to define that if the count of the records from my query above is more than 0 then use the SELECT INTO statement in order to select the data row from the employees table and assign it to the rec_employee record that I declared at the beginning.
• I used the standard dbms_output.put_line procedure to show some of the employee’s data or a generic message describing that no records were returned. The output for this PLSQL block was as follows:

Note that this approach could also work for TOO_MANY_RECORDS we saw earlier in this post.

If this doesn’t work for you and you don’t want to come across unexpected errors, you ought to add some exception handling. Using the same example, I can add some exception handling as follows:

DECLARE

    rec_employee employees%ROWTYPE;

BEGIN

    -- get the data for employee ID 1
    SELECT *
    INTO   rec_employee
    FROM   employees
    WHERE  employee_id = 1;

    -- write employee details
    dbms_output.put_line('Name: '  || rec_employee.first_name || ' ' || rec_employee.last_name);
    dbms_output.put_line('Email: ' || rec_employee.email);
    dbms_output.put_line('Number: '|| rec_employee.phone_number);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

        dbms_output.put_line('No records returned!!!');

END;
/

The exception handling added here simply catches the thrown error and writes a message using the standard dbms_output.put_line procedure describing that there is an issue. In the “real” world ( 🙂 ) you may wish to write the error to an output file or log it to your error logging framework so that the error is flagged, however, you will see in the image below that the PLSQL block no longer returns an error message:

ORA-00947: NOT_ENOUGH_VALUES

As mentioned in my previous post, when using the SELECT INTO statement it is important to note that the number of columns in the list of selected columns must be the same as the number of variables in the list of variables (caveat on if you are using a record). This means that if you declare (and use) too many variables into your INTO clause, you are going to run into ORA-00947: NOT_ENOUGH_VALUES error. I can trigger the NOT_ENOUGH_VALUES error (note, this is a similar example I used n the previous post to return multiple columns of data). The reason that this error will is occur is that I am selecting 2 columns (first_name and last_name) however, I am trying put both pieces of data into only 1 variable – I have omitted v_last_name in my INTO clause.

DECLARE
 
     v_first_name employees.first_name%TYPE;
     v_last_name  employees.last_name%TYPE;
     v_email      employees.email%TYPE;
 
BEGIN
 
     -- get the employee details of employee ID 100 and assign it to declared variables
     SELECT first_name, 
            last_name
     INTO   v_first_name
     FROM   employees  
     WHERE  employee_id = '100';
 
     -- write employee details
     dbms_output.put_line('Name: ' || v_first_name || ' ' || v_last_name );
 
END;

You will notice that this error message looks a little different to the others we have seen. This is because the PL/SQL block above is actually invalid and failed to compile, hence ORA-06550. However, you can also see the “real” reason for failure, ORA-00947: NOT_ENOUGH_VALUES. Note – this is an error message you might also come across when inserting data into a table.

Given this error causes a compilation error, in this example, you will not see a run-time error in your logic and therefore there is no out of the box exception to handle this error. To test this theory, I add added the above code into a procedure within a package body, when I attempted to compile the package body, I received the below error message as expected.

ORA-00913: TOO_MANY_VALUES

Similar to the above, as mentioned in my previous post, when using the SELECT INTO statement it is important to note that the number of columns in the list of selected columns must be the same as the number of variables in the list of variables (caveat on if you are using a record). This means that if you declare and don’t use enough variables in your INTO clause, you are going to run into ORA-00913: TOO_MANY_VALUES. I can trigger the TOO_MANY_VALUES error )note, this is a similar example I used in the previous post to return multiple columns of data). The reason that this error will occur is that I am selecting 2 columns (first_name and last_name) however, I am trying to put the data into 3 variables – I have added an additional unused variable v_email in my INTO clause.

DECLARE
 
     v_first_name employees.first_name%TYPE;
     v_last_name  employees.last_name%TYPE;
     v_email      employees.email%TYPE;
 
BEGIN
 
     -- get the employee details of employee ID 100 and assign it to declared variables
     SELECT first_name, 
            last_name
     INTO   v_first_name,
            v_last_name,
            v_email
     FROM   employees  
     WHERE  employee_id = '100';
 
     -- write employee details
     dbms_output.put_line('Name: ' || v_first_name || ' ' || v_last_name );
 
END;
/

Again, you will notice that this error message looks a little different to the others we have seen for the same reason as we saw with the TOO_MANY_VALUES error – the PL/SQL block above is actually invalid and failed to compile, hence ORA-06550. However, you can also see the “real” reason for failure, ORA-00913: TOO_MANY_VALUES. Note – this is an error message you might also come across when inserting data into a table.

Given this error causes a compilation error, in this example, you will not see a run-time error in your logic and therefore there is no out of the box exception to handle this error. To test this theory, I add added the above code into a procedure within a package body, when I attempted to compile the package body, I received the below error message as expected.

ORA-06502: NUMERIC_OR_VALUE_ERROR

Finally, the last common error to mention when using the SELECT INTO statement is NUMERIC_OR_VALUE_ERROR. In my previous post, I mentioned that datatypes in both your SELECT and your INTO must match. For example, column1 & variable1, column2 & variable2, column3 & variable3, etc must be compatible. This means that if you SELECT first name which has a datatype equal to VARCHAR2 and try to assign the data to a variable such as v_salary with a datatype equal to INT, you will run into ORA-06502: NUMERIC_OR_VALUE_ERROR. I can trigger this error in the following example:

DECLARE
 
     v_email  employees.email%TYPE;
     v_salary employees.salary%TYPE;
 
BEGIN
 
     -- get the email address of employee 100 and assign it (incorrectly) to v_salary
     SELECT  email
     INTO    v_salary
     FROM    employees
     WHERE   employee_id = 100;
 
     -- write employee email address
     dbms_output.put_line('The employees email address is: ' ||  v_salary);
 
END;
/

If you don’t want to come across unexpected errors, you ought to add some exception handling. Using the same example, I can add some exception handling as follows:

DECLARE
 
     v_email  employees.email%TYPE;
     v_salary employees.salary%TYPE;
 
BEGIN
 
     -- get the email address of employee 100 and assign it (incorrectly) to v_salary
     SELECT  email
     INTO    v_salary
     FROM    employees
     WHERE   employee_id = 100;
 
     -- write employee email address
     dbms_output.put_line('The employees email address is: ' ||  v_salary);

EXCEPTION

    WHEN VALUE_ERROR THEN

        dbms_output.put_line('An Error Occurred!');
 
END;
/

The exception handling added here simply catches the thrown error and writes a message using the standard dbms_output.put_line procedure describing that there is an issue. In the “real” world ( 🙂 ) you may wish to write the error to an output file or log it to your error logging framework so that the error is flagged, however, you will see in the image below that the PLSQL block no longer returns an error message: