How to get the return value in a advanced query using oracle database

How to get the return value in a advanced query using oracle database

  

Hello,


I need help on how to get the return value in a advanced query using oracle database:


INSERT INTO Table1(Name) VALUES(@Name)

RETURN SCOPE_IDENTITY()


I'm using an extention to connect to an external oracle database, and I need to return the value of the autonumber field generated by the sequence.

I can do it in Postgress by putting "Returning" in the advanced query:


INSERT INTO Table1(Name) VALUES(@Name)

RETURNING field_id


It works in Postgres, but in oracle database it doesn't work

Does anyone know how I do it ?


Hi, from Oracle Database perspective there is RETURNING ... INTO ... syntax...

Please have a read here:

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/returninginto_clause.htm

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABHDGIG

DECLARE
   TYPE EmpRec IS RECORD (last_name   employees.last_name%TYPE, 
                          salary      employees.salary%TYPE);
   emp_info EmpRec;
   emp_id   NUMBER := 100;
BEGIN
   UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id
      RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE('Just gave a raise to ' || emp_info.last_name ||
      ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/


As from Outsystems perspective, you can read my How To/Guide how to return values from insert returning into by using package and Advance Query only: (no need for extension)
http://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql/