Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, René.

Asked: December 18, 2024 - 12:40 am UTC

Last updated: January 07, 2025 - 6:45 pm UTC

Version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Viewed 1000+ times

You Asked

I'm using a UDT with dataset inside there as a table and i want to use that UDT on Merge statement. When using that UDT, i put that UDT inside "ON (" as following: "... ON (SELECT * FROM TABLE (UDT)) ..." then, how can i to use that UDT inside INSERT statment putting a only variable in the "VALUES" statment? For Example:



MERGE INTO TABLE1 T1
USING (SELECT * FROM TABLE(UDT)) T2
ON (T1.X = T2.Y)

WHEN NOT MATCHED THEN
INSERT VALUES UDT; //<-------- here, i want only use UDT and that's all, how can i create something like this?



The Table1 (T1) and UDT (T2) has the same structure

and Connor said...

I don't think it logically makes sense to do this:

table(udt) means udt is a nested table, which means it returns "n" rows (n>=1)

That means the join condition ON (T1.X = T2.Y) means you will potential some rows that match, and some rows that do not. The UDT is no longer a single entity

For example, if the "Y" values in UDT were:

1
2
3
4
5

and the ON condition matched 1, 3 and 5

then why would you insert the UDT (which is all 5 rows )

Rating

  (1 rating)

Comments

How can i to create a variable of some type for save a SELECT * FROM TABLE(UDT)

René, December 20, 2024 - 2:28 am UTC

Thank you for reply me

And yes, you're right but the question is not that, i just want to know how can i make an some type object for save the result of "SELECT * FROM TABLE(UDT)" inside a variable and after to uses that variable inside MERGE.. that is all

Thak you so much
Chris Saxon
January 07, 2025 - 6:45 pm UTC

You don't need to save the results of querying the UDT; reference its attributes in the VALUES clause like in the ON clause.

create table t ( c1 int, c2 date );
create or replace type tp as 
  object ( c1 int, c2 date );
/
create or replace type tp_t as 
  table of tp;
/

declare
  v tp_t := tp_t ( tp ( 1, sysdate ), tp ( 2, sysdate ) );
begin
  merge into t
  using ( select * from table ( v ) ) tp
  on    ( t.c1 = tp.c1 )
  when not matched then 
    insert values ( tp.c1, tp.c2 );
end;
/
select * from t;

        C1 C2                  
---------- --------------------
         1 07-JAN-2025 18:41:50
         2 07-JAN-2025 18:41:50


If this isn't what you're looking for then be more specific about what you're trying to achieve.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.