Inserting Values Into A Column Without Passing It Through a Parameter?

Question

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.

Answer 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.