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!

Using Object Type in Merge

779550Jun 29 2011 — edited Jun 29 2011
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
This post has been answered by Sayan Malakshinov. on Jun 29 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 27 2011
Added on Jun 29 2011
2 comments
947 views