SQL & PL/SQL

Hi 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