Home > Uncategorized > About Returning Into Clause on Remote DB – ORA-22816

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.

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: