Error ora 00913 too many values

ORA-00913 When SELECT

Let’s see a SQL statement containing a subquery that can reproduce ORA-00913 for an example.

SQL> select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700);
select * from employees where department_id in (select department_id, manager_id from departments where location_id = 1700)
                                                *
ERROR at line 1:
ORA-00913: too many values

Cause: Column List Mismatch

The error message «too many values» actually means «too many column values» if I may say so. That is to say, the number of returned columns in the inner query is more than required number of columns in the outer statement.

Solution

Therefore, you have to make the subquery return exact column list to match the outer statement. In this case, only one column «department_id» should be in the column list of subquery.

SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);


In contrast, ORA-00947: not enough values means the number of returned columns in the inner query is less than required number of columns in the outer statement.

By the way, ORA-00913 or ORA-00947 complains about returned number of columns, not rows. For accommodating multiple rows, I use IN instead of = (equal) operator in case of error ORA-01427 in the above statement.

ORA-00913 When INSERT

Another type of column list mismatch may occur at INSERT … SELECT statement. Let’s see how I reproduce ORA-00913.

First of all, I create an empty table EMPLOYEES_2 from EMPLOYEES.

SQL> create table employees_2 as select * from employees where 1 = 2;

Then I drop one column from EMPLOYEES_2.

SQL> alter table employees_2 drop column PHONE_NUMBER;

Now I insert some rows by INSERT … SELECT.

SQL> insert into employees_2 select * from employees;
insert into employees_2 select * from employees
            *
ERROR at line 1:
ORA-00913: too many values

Once again, it’s a column list mismatch problem, although you don’t see any column in the statement. I know it’s very confusing. Just remember that «too many values» means «too many columns».

Solution

The solution is to compare two tables’ definitions and then adjust the column list to match each other.

SQL Error: ORA-00913: too many values issue appears when you enter more column values in the VALUES / SELECT clauses than the number of columns required in the insert statement. The values in the VALUES clause are more than what is needed by the insert statement. If you try to run an insert statement with too many values in the VALUES / SELECT clause, the insert statement will fail to insert the values into the table. Oracle throws the ORA-00913: too many values error in this scenario.

The SQL Error: ORA-00913: too many values error occurs in the select subqueries. If the number of columns returned by the subquery is more than the number of columns required by the outer query, the outer query will be unable to handle the data returned by the inner query. Oracle throws the error in this scenario.

The SQL statement such as insert and select requires two sets of values that are equal in number. The datatype and order of the column should be same in each sets. The second set will be in VALUES / SELECT clause. If the second set contains more items than the first set, then the oracle error SQL Error: ORA-00913: too many values occurs. If the subquery returns too many column values in the WHERE or HAVING clause, the outer query will fail to process.

When this ORA-00913 Error occur

The error SQL Error: ORA-00913: too many values occurs if the values or select clause returns more columns than the necessary columns in the insert statement. Also, if the subquery produces more column values than the main query requires, the error will occur. In the example below the employee table contains two columns id and name. The insert statement value clause contains three values. These three values could not insert into the table that contains only two columns.

create table emp (
id int,
name varchar2(100)
);
insert into EMP values(1,'emp1',1000);

Error

Error starting at line : 12 in command -
insert into EMP values(1,'emp1',1000)
Error at Command Line : 12 Column : 13
Error report -
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"

Root Cause

This SQL Error: ORA-00913: too many values error occurs if the number of columns returned in the values or select clause exceeds the number of columns required in the insert statement. The insert statement was unable to find the extra column in the table, or the extra column is irrelevant to the table.

Solution 1

Remove the additional column in the values clause if the extra column value is provided in the insert statement. If the table needs an additional column, modify the table and add the required column. The number of column values in the values clause should be the same as the number of columns in the table. Check the table’s columns and make changes.

Problem

create table emp (
id int,
name varchar2(100)
);
insert into EMP values(1,'emp1',1000);

Error report -
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"

Solution

insert into EMP values(1,'emp1');
1 row inserted.

create table emp (
id int,
name varchar2(100),
salary int
);
insert into EMP values(1,'emp1',1000);
1 row inserted.

Solution 2

The insert statement requires two sets of values equal in number. This SQL Error: ORA-00913: too many values occurs when the second set contains more items than the first set. The error will occur if the number of columns in the values column exceeds the number of inserted columns. The numbers of columns in the values clause should be matched by the inserted columns.

Problem

create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp (id,name) values(1,'emp1',1000);

Error report -
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"

Solution

insert into emp (id,name,salary) values(1,'emp1',1000);
1 row inserted.

insert into emp (id,name) values(1,'emp1');
1 row inserted.

Solution 3

In the insert statement, if a select statement returns too many values, the insert statement cannot add the data to the table. The select statement’s returning set must match the entered columns. The returned columns should be deleted in the select statement, or the needed columns should be specified in the insert statement.

Problem

create table emp (
id int,
name varchar2(100)
);

create table manager (
id int,
name varchar2(100),
salary int
);

insert into emp as select * from manager;

Error report -
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"

Solution

insert into emp as select id, name from manager;
1 row inserted.

insert into emp(id, name) as select id, name from manager;
1 row inserted.

create table emp (
id int,
name varchar2(100),
salary int
);

insert into emp as select * from manager;
1 row inserted.

Solution 4

The returned set in the subqueries should match the main query in the select subqueries. This error SQL Error: ORA-00913: too many values can also occur if your subquery in the WHERE clause produces too many columns values. If the subquery returns all columns from the table, you must change it to return only one column.

Problem

create table emp (
id int,
name varchar2(100),
salary int
);

create table manager (
id int,
name varchar2(100),
salary int
);

select * from emp where id in (select * from manager);

ORA-00913: too many values
00913. 00000 -  "too many values"

Solution

The subquery returns all column values from the manager table. In the main query where clause requires only id values. As the subquery returns too many values, the error occurs. In the query below returns only id column from the manager table. where clause matches id column value from manager table to id column value from employee table.

select * from emp where id in (select id from manager);

Ошибка SQL: ORA-00913: слишком много значений

insert into abc.employees select * from employees where employee_id=100; 

Error starting at line 1 in command:
insert into abc.employees select * from employees where employee_id=100; 

Error at Command Line:1 Column:25
Error report:
SQL Error: ORA-00913: too many values
00913. 00000 -  "too many values"
*Cause:    
*Action:

Ответы (5)

insert into abc.employees (col1,col2) 
select col1,col2 from employees where employee_id=100; 

select * 
from ALL_TAB_COLUMNS ATC1
left join ALL_TAB_COLUMNS ATC2 on ATC1.COLUMN_NAME = ATC1.COLUMN_NAME 
                               and  ATC1.owner = UPPER('2nd owner')
where ATC1.owner = UPPER('abc')
and ATC2.COLUMN_NAME is null
AND ATC1.TABLE_NAME = 'employees'

create table employees as select * from source_employees where employee_id=100;

copy from source_schema/password insert employees using select * from 
source_employees where employee_id=100;

insert into oehr.employees select * from employees where employee_id=99

In the previous tutorial we saw, how to initialize a record variable by fetching data from all the columns of a table using SELECT-INTO statement. But what if we want to fetch the data from selected columns of a table? Can we still use the same process for initializing the record variable?

The answer is No, we cannot use the same process shown in the previous tutorial for initializing a record datatype variable by fetching data from select columns of a table. The execution of the following example where we are using the data from one column “First Name” of the “Employees” table for initializing the record datatype variable “v_emp” will return an error.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name INTO v_emp FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_emp.first_name);
END;
/ 

That error is “PL/SQL: ORA-00913: too many values” So what does this error means? Why is it saying “too many values” even when we are fetching only one data.  I know sometimes PL/SQL error can be deceptive and confusing.

In order to find out the solution of this problem and understand this error we have to recall what we learnt in the previous tutorial. There we learnt that Database Records are composite data structures made up of multiple fields which share same name, datatype and data width as that of the columns of the table over which it is created. And the data which was fetched gets stored into these fields.

In case of initializing a record variable by fetching data from all the columns of a table, we don’t have to do anything, oracle engine does all the dirty work in the background for you. It first fetches the data from the table and then stores that data into the corresponding field into the record which has the same data type and data width, and for that matter also shares the same name.

But when it comes to initializing a record datatype variable by fetching the data from selected columns we have to do all this work manually. We have to specify the name of the column from which we want to fetch the data and then we have to specify the name of the field where we want to store that data.

In the above example we did specify the column name of the table whose data we want to fetch but we didn’t specify the name of the record’s field where we want to store the fetched data. That confuses the compiler on where to store the data which ultimately causes the error.

Reference Book for SQL Expert 1z0-047 Affiliate link
OCA Oracle Database SQL Certified Expert Exam Guide (Exam 1Z0-047)

How to Solve “PL/SQL ORA-00913: Too Many Values” Error?

The PL/SQL ORA-00913 error can easily be solved by specifying the record’s field name where you want the fetched data to be stored. But here you have to be a bit careful. You have to make sure that the field’s datatype and data width must match with the datatype and data width of the column whose data you have fetched.

Now the question here is that how to specify the record data type field’s name? We can specify the name of the field of the record variable using Dot (.) notation where we first write the name of the already declared record variable followed by a dot (.) and the name of the field. And luckily both the columns of the table and fields of the record share the same name.

So now that we have learnt why we are having PL/SQL ORA-00913 error on initializing the record data type variable using data from select columns. Let’s modify the above example accordingly and try to resolve it.

Example 1: Initialize the Record Datatype variable using data from One Column.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name INTO v_emp.first_name FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE (v_emp.first_name);
END;
/

Example 2: Initialize the Record Datatype variable using data from Multiple Columns.

SET SERVEROUTPUT ON;
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT first_name,
    hire_date
  INTO v_emp.first_name,
    v_emp.hire_date
  FROM employees
  WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE (v_emp.first_name);
  DBMS_OUTPUT.PUT_LINE (v_emp.hire_date);
END;
/

That is how we can initialize a record datatype variable using data from selected columns of a table.

Question: Can we assign values of one record datatype variable to another record datatype variable?
Try it yourself and send your answers to me on my Twitter or Facebook.

Hope you enjoyed reading. Send your feedbacks on my Twitter and Facebook. Also share this blog and tag me as I am giving away RebellionRider’s merchandise to randomly selected winners. Thanks & have a great day!