How to return variable to client?

I have a simple scenario where we're trying to insert a row into a table and return the ID created to the client. I thought this would be a straightforward solution (table created in 11g so no identity column, changing it to an identity column is not an option):

DECLARE
    new_ID INT;
BEGIN

    SELECT MAX(MY_TABLE_ID) + 1
    INTO new_ID
    FROM MY_SCHEMA.MY_TABLE
    ;

    INSERT INTO MY_SCHEMA.MY_TABLE (MY_TABLE_ID)
    VALUES (new_ID)
    ;

    SELECT new_ID
    FROM dual
    ;

    COMMIT;
END;

But this returns error 'PLS-00428: an INTO clause is expected in this SELECT statement'. It turns out Oracle doesn't allow variables to be used as expressions in SELECT clauses like SQL Server does.

So how do we get this data back to the calling client? Every single answer on the subject I've seen suggests using DBMS_OUTPUT to return this (and like the askers in those questions, I'm coming here confused from a SQL Server background). But DMBS_OUTPUT is a debugging tool, not appropriate for production code (not every client is an IDE!). Ok, I'm using ODP.NET as the client and while it is possible to retrieve PUT_LINE() output, but I'm sure developers will understand why this is a bad idea to be avoided. Similarly, I'm not too comfortable using the RETURN statement, which imo should only be returning non-0 values as error statuses.

The only other reasonable approach would be to use an Oracle equivalent of SQL Server's OUTPUT.INSERTED (an equivalent of @@ROWID not being an option for obvious reasons), but it turns out the RETURNING clause also requires... an INTO clause, meaning we're back using variables again.

In this instance, the neatest solution appears to be (I'm assuming transaction is isolated enough to avoid selecting a different execution's ID):

INSERT INTO MY_SCHEMA.MY_TABLE (MY_TABLE_ID)
SELECT MAX(MY_TABLE_ID) + 1
FROM MY_SCHEMA.MY_TABLE
;

SELECT AS NewId
FROM MY_SCHEMA.MY_TABLE
;

COMMIT;

It'll have to do, but it seems clunky to have to hit the table twice, both in terms of performance and coding. But ultimately my question is: is there any way to get the value of a variable back to the client without first having to insert it into a table, or using a method that's not designed for that kind of usage?

Solution

To insert a row and get back an identity value generated, use a sequence and simply insert with returning clause.

create sequence mysequence; -- one time only

If using SQL Plus:

var pkcol integer

begin
  insert into mytable (pkcol,col2,col3) values (mysequence.nextval,'abcd','efgh') 
  returning pkcol into :pkcol;
end;
/
print pkcol -- now it's in the sqlplus client and you can do something with it, which in sqlplus is not much other than printing it

The key to getting the value back is using proper binds. This is how you'd do it with the SQL Plus client. If using a different client (you mention ODP.NET), bind :pkcol to a client variable in that environment using the conventions it provides. You would not in that case use the var pkcol integer or print pkcol commands, but the PL/SQL snippet would be the same. How you submit a block to the database and bind the return value to a client var varies with the client technology in question.

Another option is not to require anything back at all. Generate your sequence first, then do the insert:

select mysequence.nextval from dual -- fetch into client var pkcol

then later,

insert into mytable (pkcol,col2,col3) values (:pkcol,'abcd','efgh')

with :pkcol defined as a bind to the pkcol local var. No return needed on the insert, as you already know the pkcol value.

If you don't want to use a sequence and prefer to do a select on the table to get the maximum value, then simply select first and do the insert later. In SQL Plus that'd look like:

var pkcol integer

begin
  select max(pkcol)+1
    into :pkcol
    from mytable;

  insert into mytable values (:pkcol,'abcd','efgh');
end;
/
print pkcol

However keep in mind this isn't a good idea. Another session can do that same operation at the same time and end up with a PK violation. Plus you incur the penalty of that unnecessary select and sort. A sequence is much better. But however you choose to do it, the key thing is to use bind variables to exchange values between the client and the database.