Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Error: ORA-00913: too many values

Tags:

sql

oracle

Two tables are identical in terms of table name, column names, datatype and size. These tables are located in separate databases, but I am use to current Log in in hr user.

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

I can not give use original query from corporate office.

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:
like image 348
user2703444 Avatar asked Sep 11 '13 09:09

user2703444


People also ask

How do you fix too many 00913 values?

You might change your mind and for example insert all your values in another table, to delete the rows that are not in the other table (with a join).

How do I fix Ora 01422 exact fetch returns more than requested number of rows?

This is where the error is being thrown. When an ORA-01422 is triggered, your SELECT INTO statement is retrieving multiple rows of data or none at all. If it is returning multiple, the predefined exception TOO_MANY_ROWS will be raised, and for no returns the PL/SQL will raise NO_DATA_FOUND.

How do you resolve ORA 00918 column ambiguously defined?

Luckily the solution to this Oracle error is just about as straightforward as finding what causes the problem. What is needed is to add the prefix to each column with the table name that it originally belonged too and then re-execute the SQL statement.


2 Answers

You should specify column names as below. It's good practice and probably solve your problem

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

EDIT:

As you said employees has 112 columns (sic!) try to run below select to compare both tables' columns

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'

and than you should upgrade your tables to have the same structure.

like image 160
Robert Avatar answered Sep 18 '22 16:09

Robert


The 00947 message indicates that the record which you are trying to send to Oracle lacks one or more of the columns which was included at the time the table was created. The 00913 message indicates that the record which you are trying to send to Oracle includes more columns than were included at the time the table was created. You just need to check the number of columns and its type in both the tables ie the tables that are involved in the sql.

like image 21
Anurag sinha Avatar answered Sep 18 '22 16:09

Anurag sinha