PL/SQL MERGE Using Collection

Question

I am having trouble merging a table with a collection. Let's say I have a table emp.

Here is my PL/SQL code snippet.

TYPE empcol is table of emp%ROWTYPE INDEX BY BINARY_INTEGER;
tmpemp empcol;

-- Code here to load data from a CSV file into tmpemp collection  
-- tmpemp(1).emp_id := parsedstring   
-- etc.
MERGE INTO emp A using tmpemp B ON A.emp_id = B.emp_id 
WHEN MATCHED THEN UPDATE SET A.fname = B.fname, A.lname = B.lname 
WHEN NOT MATCHED THEN INSERT (emp_id, fname, lname) VALUES (b.emp_id, b.fname, b.lname);

Compiler doesn't like it. Its throwing ORA-0942 - Table or View doesn't exist. What am I doing wrong? or How can I do this better. Thanks a lot for any help you can provide.

1) Which one it does not like? TYPE or MERGE? 2) is table `emp` present at all? — David Jashi, Jul 24 '13 at 20:05
Is `type empcol` declared on database level? E.g. `create or replace type empcol as ...` . You need to declare type globally in database schema if you want to use it in SQL. — ThinkJet, Jul 24 '13 at 20:13
empcol is declared at the procedure level not at the schema level. Do you think that's the issue? — Billy8988, Jul 24 '13 at 20:15
http://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement — David Jashi, Jul 24 '13 at 20:19

score 5 · Answer 1 · answered Jul 24 '13 at 20:27

PL/SQL types like emp%ROWTYPE or TABLE OF ... INDEX BY ... cannot be used in SQL queries.
The type must be declared as SQL type (not as PL/SQL type) to be used in SQL query.

Try this approach (example):

create table emp(
  firstname varchar2(100),
  salary number
);

insert into emp values( 'John', 100 );
commit;

create type my_emp_obj is object(
     firstname varchar2(100),
     salary number
);
/

create type my_emp_obj_table is table of my_emp_obj;
/

declare
  my_emp_tab my_emp_obj_table;
begin
  null;
  my_emp_tab := my_emp_obj_table( my_emp_obj( 'John', 200 ), my_emp_obj( 'Tom', 300 ));

  MERGE INTO emp
  USING ( SELECT * FROM TABLE( my_emp_tab )) src
  ON ( emp.firstname = src.firstname )
  WHEN MATCHED THEN UPDATE SET salary = src.salary
  WHEN NOT MATCHED THEN INSERT VALUES( src.firstname, src.salary );
end;
/

select * from emp;

FIRSTNAME               SALARY
----------------------- ----------
John                           200 
Tom                            300

PL/SQL MERGE Using Collection

1 Answers1