Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Merge data Using Array records. Possible?

5280May 3 2010 — edited May 4 2010
Hi,
I have a package with an array of emp%rowtype declared.
I now want to use merge. I.e if the elements of array is found in table, upate it else insert.
I am geeting compling errors table does not exist.

Can merge be used this way?
Please guide..

CREATE OR REPLACE PACKAGE UPD_emp_ALl AS
TYPE Varry_emp IS TABLE OF emp%ROWTYPE;
PROCEDURE UPD_emp(emp_rec in Varry_emp);
end UPD_emp_all;
/

CREATE OR REPLACE PACKAGE BODY UPD_emp_All AS
PROCEDURE UPD_emp(emp_rec in Varry_emp) is
BEGIN
MERGE INTO emp T
Using emp_rec V
ON (T.emp_id= V.emp_id and
T.emp_dept=V.emp_dept)
WHEN MATCHED THEN
UPDATE set
T.col1 = v.col1,
T.col2= v.col2,
so on...
WHEN NOT MATCHED THEN
Insert (T.col1,T.col2,T.col3,...)
vALUES (V.col1,V.col2,V.col3,...);
END UPD_emp;
END UPD_emp_All;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 1 2010
Added on May 3 2010
8 comments
10,122 views