0

I have a table named Users with just 2 columns id (in type NUMBER) and Name (in type VARCHAR2). All columns are non-nullable

I created an SQL procedure to ONLY take in ONE parameter (new username) as follows:

  CREATE OR REPLACE PROCEDURE insert_new_user
  (
        this_name VARCHAR2;
  )
  AS
  BEGIN
        INSERT INTO Users
        VALUES (this_name);
        COMMIT;
  END;
  /

The procedure compiles BUT with errors as follows: Error ORA-00947: not enough values.

When I run it as follows:

  BEGIN
       insert_new_user ('Bob');
  END
  /

It throws a PL/SQL compilation error.

How do I set the id column to automatically put in the next number without needing to pass it through a parameter?

The ideal output would be a scenario like:

id Name

1 Adam

2 Ben

3 Bob

Where Bob is the new user row inserted into the user table and 3 is the number added automatically in the id column.

5120bee
  • 689
  • 1
  • 14
  • 36
  • Id column is an identity column? – Chetan Apr 21 '17 at 00:32
  • yes, it's the PK – 5120bee Apr 21 '17 at 00:33
  • Primary key and identity are not the same. Identity column values are auto incremental. In your case if id column is primary key but not auto incremental then you must provide a value for it when inserting row in the table. Answer from @Dan below should give more details about auto incremental column. – Chetan Apr 21 '17 at 00:42

2 Answers2

3

It looks like what you are trying to do is set an Identity Column (which automatically increments for each record) on the TABLE, so you won't have to make any changes to the stored procedure.

This answer appears to address that issue.

Basically, you just need to run (if your version of Oracle supports it):

CREATE TABLE t1 (
    c1 NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1), 
    c2 VARCHAR2(10)
);
Community
  • 1
  • 1
Dan
  • 231
  • 1
  • 12
1

In Oracle 11 and earlier, use a sequence:

CREATE SEQUENCE Users__ID__Seq;
/

CREATE OR REPLACE PROCEDURE insert_new_user
(
  this_name USERS.NAME%TYPE
)
AS
BEGIN
  INSERT INTO Users ( id, name )
  VALUES ( Users__ID__Seq.NEXTVAL, this_name );
END;
/

Note: see this discussion on why you should not generally use COMMIT in procedures but should, instead, COMMIT in the user's transaction.

Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117