How to insert specific columns without entering in all the values. PLSQL

Question

0.00/5 (No votes)

See more:

Trying to create a procedure that updates 2 columns (and no more), of a table with 11 columns. I get an error saying:

PL/SQL: ORA-00947: not enough values

So I'm guessing it wants me to add every column, but I only want to update 2. Is there a way to do this? Yes, I'm a beginner lol

Here is code:

CREATE OR REPLACE PROCEDURE sp_update_contact(
p_employee_id employees.employee_id%TYPE,
p_phone_number employees.phone_number%TYPE,
p_status OUT NUMBER
)
IS
v_count_id NUMBER;
BEGIN
SELECT COUNT(employee_id) INTO v_count_id FROM EMPLOYEES WHERE employee_id = p_employee_id;
IF v_count_id =0 THEN
Insert INTO EMPLOYEES VALUES(p_employee_id,p_phone_number);
COMMIT;
p_status:=0;
ELSE
p_status:=-1;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_status:=-2;
end;

What I have tried:

Reading text book over and over

Posted 29-Apr-22 13:36pm

Member 15619928

Updated 30-Apr-22 18:22pm

Add a Solution

Comments

Sandeep Mewara 30-Apr-22 0:30am

If you want to just UPDATE, use the UPDATE query instead of INSERT:
Insert INTO EMPLOYEES VALUES(p_employee_id,p_phone_number);

That should solve it.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2022-04-30T18:22:00

In your question you talk about updating but based on the code I take it that you want to insert the record if it doesn't exist.

If that is the case, when you insert a row and you don't provide all values, you need to specify the columns you're inserting into. As a matter of fact this is a good practice always since the order of the columns may change over time. So you're code could look something like

SQL

CREATE OR REPLACE PROCEDURE sp_update_contact(
   p_employee_id employees.employee_id%TYPE,
   p_phone_number employees.phone_number%TYPE,
   p_status OUT NUMBER) IS

   v_count_id NUMBER;
   BEGIN
      SELECT COUNT(employee_id) 
      INTO   v_count_id 
      FROM   EMPLOYEES 
      WHERE  employee_id = p_employee_id;
      
      IF v_count_id =0 THEN
         Insert INTO EMPLOYEES (employee_id, phone_number) 
         VALUES(p_employee_id,p_phone_number);

         COMMIT;
         p_status:=0;
      ELSE
         p_status:=-1;
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         p_status:=-2;
end;