Using Object Type in Merge
779550Jun 29 2011 — edited Jun 29 2011Hi All,
Got a small problem where i would like some suggestions.
I have got a TYPE declared.
CREATE OR REPLACE TYPE "MY_TYPE_OT" IS OBJECT (
field1 VARCHAR2,
field2 VARCHAR2,
field3 VARCHAR2);
I now have a table type declared for this
TYPE MY_TYPE_TT IS TABLE OF MY_TYPE_OT INDEX BY VARCHAR2(100);
I have collection of type MY_TYPE_TT
MY_VAR MY_TYPE_TT;
I am populating this variable with lets say, 10 records.
Then i try to save this to a database table, MY_TABLE for this i need to merge since it might be an update or insert. so my query goes like this
MERGE INTO MY_TABLE T2 USING TABLE(MY_VAR) T1 ON ( T1.field1 = T2.field1 AND T1.field2 = T2.field2)
here i get a error that MY_VAR is not a table. I tried different things like doing a cast, which gives me invalid data type etc.
Any suggestions on how to over come this?
Thanks in Advance
Paddy