Oracle PL/SQL ORA-00947 Not Enough Values Error

»»»Oracle PL/SQL ORA-00947 Not Enough Values Error

In Oracle database, the following error message may appear when an incorrect SQL statement is used, in SQL Plus or in anywhere that send SQL queries to the databases such as stored procedure or external programs.

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00947: not enough values

The reason for ORA-00947 error is when a SQL statement requires two sets of value, i.e number of number of columns and its value that are specified in the SQL to be equal in number. But the second part of the query which is column values contains fewer items than the first part which is the number of columns. The error may happens in all kind of SQL statements – Insert, Update or Select – and it can also occur in a WHERE or HAVING clause in which a nested sub-SELECT returns too few columns. If the SQL does not specify columns, it means that the values entered are less that the columns existed in the table in database.

For example, the ORA-00947 error will be returned if you try to execute the following SQL statements:

INSERT INTO Customers (Customer_ID, Customer_Name, Birthday) VALUES ('1', 'My Customer');

Or,

SELECT * FROM Customers WHERE (Customer_ID, Customer_Name) IN (SELECT Customer_ID FROM Orders);

To solve the error and correct the problem, check that the number of values provided in the column values part is equal that the columns specified, or the number of columns in table, and change the SQL statement to make them equal.

You can either reduce the number of columns, or increase the number of values provided to the column to make the SQL statement equaled. This applies to the nested SQL statements with HAVING or WHERE clause too. For example, the correct SQL statements should be like the following:

INSERT INTO Customers (Customer_ID, Customer_Name, Birthday) VALUES ('1', 'My Customer', '08/08/1988');

Or,

INSERT INTO Customers (Customer_ID, Customer_Name) VALUES ('1', 'My Customer');

Or,

SELECT * FROM Customers WHERE (Customer_ID, Customer_Name) IN (SELECT Customer_ID, Customer_Name FROM Orders);

Or,

SELECT * FROM Customers WHERE (Customer_ID) IN (SELECT Customer_ID FROM Orders);

By | 2017-01-11T16:38:26+00:00 January 11th, 2017|Categories: Databases|Tags: |1 Comment

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • abhishek kumar

    while i was creating this function i got such an error, pl/sql: ora-00947:not enough values,

    here is the function which i have created,for which i would like you to plz tell me about the error,and the process to correct it.

    SQL> create or replace function EMPLOYEE_STATUS (aId IN number,aName IN varchar2,aDOB IN date)
    2 return varchar2
    3 as
    4 RETIREMENT varchar2(100);
    5 begin
    6 select id,to_number(to_char(sysdate,'YYYY')-to_char(DOB,'YYYY')),
    7 (case when
    8 to_number(to_char(sysdate,'YYYY')-to_char(DOB,'YYYY'))>60
    9 then
    10 'retired'
    11 else
    12 'employee'
    13 end) as status
    14 into retirement
    15 from employ
    16 where aName=Name and
    17 aID=ID;
    18 return RETIREMENT;
    19 end EMPLOYEE_STATUS;
    20 /
    here is the error:-

    LINE/COL ERROR
    ——– ————————————
    6/1 PL/SQL: SQL Statement ignored
    14/17 PL/SQL: ORA-00947: not enough values

    here is the table for which i have done such function:-

    create table EMPLOY(Id Number,Name varchar2(30),DOB Date,Designation varchar2(15),Date_of_Joining Date);

    insert into EMPLOY values(EmployId.nextval,'Ram Ratan kumar',to_date

    ('11/11/1953','DD/MM/YYYY'),'Director',to_date('10/06/1980','DD/MM/YYYY'));

    insert into EMPLOY values(EmployId.nextval,'Kiran Devi',to_date('19/05/1958','DD/MM/YYYY'),'Asst Director',to_date

    ('20/04/1982','DD/MM/YYYY'));

    insert into EMPLOY values(EmployId.nextval,'Gunjan Kumar Sinha',to_date('12/01/1975','DD/MM/YYYY'),'General

    Manager',to_date('17/09/1999','DD/MM/YYYY'));

    insert into EMPLOY values(EmployId.nextval,'Ranjan Kumar Sinha',to_date('10/10/1976','DD/MM/YYYY'),'HR',to_date

    ('04/08/2002','DD/MM/YYYY'));

    insert into EMPLOY values(EmployId.nextval,'Ashish Kumar',to_date('24/03/1985','DD/MM/YYYY'),'Engineer',to_date

    ('27/01/2009','DD/MM/YYYY'));

    insert into EMPLOY values(EmployId.nextval,'Abhishek kumar',to_date

    ('14/04/1945','DD/MM/YYYY'),'supervisor',to_date('28/06/1979','DD/MM/YYYY'));

    please solve my problem as early as possible.

Pin It on Pinterest

Share This

Share This

Share this post with your friends!