CODEWITHSUNDEEP
oracle-database
Patrick
Patrick

Reputation: 2577

Oracle Update and select in a single query

I'm working with a vendor's database, and I need to increment a value and return it. I have the right command, it updates the table, but the output in oracle developer just says "PL/SQL procedure successfully completed." How do I return NewReceiptNumber? 

DECLARE
   NewReceiptNumber int;
BEGIN
    UPDATE SYSCODES 
    SET (VAR_VALUE) = VAR_VALUE+1
    WHERE VAR_NAME='LAST_RCPT'
    RETURNING VAR_VALUE INTO NewReceiptNumber;
END;

Upvotes: 0

Views: 75

Answers (1)

Jair Hernandez
Jair Hernandez

Reputation: 494

You are just depicting an anonymous block there, to VIEW the value of NewReceiptNumber in that code you'll have to use:

dbms_output.put_line(NewReceiptNumber).

as in:

DECLARE
   NewReceiptNumber int;
BEGIN
    UPDATE SYSCODES 
    SET (VAR_VALUE) = VAR_VALUE+1
    WHERE VAR_NAME='LAST_RCPT'
    RETURNING VAR_VALUE INTO NewReceiptNumber;

    dbms_output.put_line(NewReceiptNumber);
END;

But if your intention is to actually return this value to another procedure or a call from your front-end, you'll have to declare a stored procedure and within that declaration indicate your output parameter, something like:

CREATE OR REPLACE PROCEDURE my_proc(newreceiptnumber out INT) AS
BEGIN
    UPDATE syscodes 
    SET (var_value) = var_value+1
    WHERE var_name='LAST_RCPT'
    RETURNING var_value INTO newreceiptnumber;
END my_proc;

And of course it'll be a more robust solution if you send your updated values as parameters as well, this is just the minimal approach.

Upvotes: 2

Related Questions