About Returning Into Clause on Remote DB - ORA-22816

We use PL/SQL RETURNING INTO clause for returning value(s) after dml commands(INSERT,UPDATE,DELETE). Assume that you would insert row a employee table with employee_id value. Likely, you type this sql statement:

INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES( SEQ_TEMPLOYEES.NEXTVAL,'FUAT SUNGUR');

After this statement you need this employee_id to add another table. You can use RETURNING INTO clause to get employee_id value after an insert statement as follows:

INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES( SEQ_TEMPLOYEES.NEXTVAL,'FUAT SUNGUR') RETURNING EMP_ID INTO iEmpId;

After these, iEmpId variable has employee_id value.
There is no problem if you perform these on one database.

If you want to perform this between databases, likely you would get :

ORA-22816: unsupported feature with RETURNING clause


This error may be pothering and discouraging you to what do you want. We can use other tactic to perform our willings.

Assume that you have two databases A_DB, B_DB and in B_DB databases you want to add a row in Employee Table that table in A_DB. In this situation you take advantage of functions. Write a function that returns the sequence number of just added row.

FUNCTION INSERT_EMPLOYEE_AND_GET_SEQ(vEmpName_in IN VARCHAR2)
RETURN INTEGER
IS
    iEmpId INTEGER;
BEGIN

    INSERT INTO TEMPLOYEES (EMP_ID,EMP_NAME) VALUES (SEQ_TEMPLOYEES.NEXTVAL,vEmpName_in) RETURNING EMP_ID INTO iEmpId;
    RETURN iEmpId;
EXCEPTION
    WHEN OTHERS THEN NULL;
END;

Also you have add function parameter to whether the returning into is used or not. May be this sequence number is need only once and after you would use that sequence to insert employee. So, there are lots of need why you do it.