Oracle SQL, variable in normal SQL?

April 27, 2022

Need to send a SQL string to Oracle, to firstly "select" and then "insert" on the same table. The code in db is out of my control so have to it like this. Because of the structure of the app, I’m not able to call "select" first, get the new-id and then do insert, they have to be in the same SQL string. Want to do something like following but don’t know how to use, or even Oracle has, the variable like this. Have checked this link but not the same case.

var newid number;
SELECT max(id) + 1 into :newid FROM usr;
INSERT INTO usr(id, name) VALUES (:newid, 'new-name');

>Solution :

(Note that using max(id)+1 to generate keys does not work in a multi-user environment. You’d really want to use a sequence).

It sounds like you just want

INSERT INTO usr(id, name)
  SELECT max(id) + 1, 'new-name'
    FROM usr;

You could probably pass the database an anonymous PL/SQL block as well though that will be less efficient. And your front end might not support passing in PL/SQL if it expects to get just SQL.

DECLARE
  l_newID usr.id%type;
BEGIN
  SELECT max(id) + 1
    INTO l_newID
    FROM usr;

  INSERT INTO usr( id, name )
    VALUES( l_newID, 'new-name' );
END;