Home » SQL & PL/SQL » SQL & PL/SQL » Merge Statement with PL/SQL Collection type (Oracle 10g)
Merge Statement with PL/SQL Collection type [message #385741] Tue, 10 February 2009 22:48 Go to next message
ashishjoshi02
Messages: 2
Registered: February 2009
Junior Member
I am facing following problem in piece of code:
declare
TYPE destination_table IS RECORD
(
proposal_id proposal.proposal_id%TYPE,
status proposal.status%TYPE,
app_type proposal.app_type %TYPE
);
TYPE destination IS TABLE OF destination_table;
source destination;
begin
select t.proposal_id,t.status,t.app_type BULK COLLECT INTO source from proposal t;
FORALL i IN 1..source.COUNT
MERGE INTO proposal_target DEST
USING( SELECT TREAT(source(i) AS proposal_target) AS obj FROM DUAL ) src
ON (DEST.PROPOSAL_ID = src.obj.proposal_id)
WHEN MATCHED THEN
UPDATE SET DEST.STATUS = 'M'
WHEN NOT MATCHED THEN
INSERT (proposal_id,status,app_type) VALUES( src.obj.proposal_id, src.obj.status,src.obj.app_type );
END;
/

I am getting ora-00902 error. Here my collection is same as of oracle table 'proposal'( i created it).
My concern:

1. Can i use pl/sql record as source in merge statements. If yes then how.
2. If no is there any efficient other way of doing so.(Upsert with collections)

Thanks in advance.
Regards
Ashish
Re: Merge Statement with PL/SQL Collection type [message #385744 is a reply to message #385741] Tue, 10 February 2009 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Merge Statement with PL/SQL Collection type [message #385784 is a reply to message #385741] Wed, 11 February 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also post your version with 4 decimals and a full test case we can reproduce.
Copy and paste your SQL*Plus session, before read the above link.

Regards
Michel
Re: Merge Statement with PL/SQL Collection type [message #385814 is a reply to message #385741] Wed, 11 February 2009 03:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I just don't think you can do what you're trying to do:
1) the documentation for the TREAT function clearly states
Quote:
type must be some supertype or subtype of the declared type of expr.


Proposal_Target is no form of Type at all - it's a table.


2) SQL has no knowledge of your types - they're only defined in Pl/Sql, and so it can't operate on them.


As an alternative, have you considered scrapping the FORALL approach completely and just using a single MERGE statement:
select t.proposal_id,t.status,t.app_type BULK COLLECT INTO source from proposal t;

 MERGE INTO proposal_target DEST
 USING( select t.proposal_id,t.status,t.app_type from proposal t) src
 ON (DEST.PROPOSAL_ID = src.proposal_id)
 WHEN MATCHED THEN
 UPDATE SET DEST.STATUS = 'M'
 WHEN NOT MATCHED THEN
 INSERT (proposal_id,status,app_type) VALUES( src.proposal_id, src.status,src.app_type ); 


On an only partly related note, I see that the 3rd reply was the first to actually offer anything other that carping and formatting request - not a great signal to noise ratio, really.
Re: Merge Statement with PL/SQL Collection type [message #385849 is a reply to message #385814] Wed, 11 February 2009 07:25 Go to previous messageGo to next message
ashishjoshi02
Messages: 2
Registered: February 2009
Junior Member
Thanks..
I can understand direct select statements in using() but the problem is in actual scenario the data set is retrieved based on many computation on source table 'proposal' rather than simply select.So the idea was to populate a pl/sql collection with all required fields with relavent computations and use that to merge with target. Number of rows can be fairly large around 0.1 M to 0.2 M. Any suggestions. Any other way of casting pl/sql type to oracle schema type. The TREAT() syntax i found during googling.
I am not aware of its details.

Please Suggest.

Regards
Ashish
Re: Merge Statement with PL/SQL Collection type [message #385869 is a reply to message #385849] Wed, 11 February 2009 10:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
a good way to do it would be to make sure that your collection type exists as an SQL type, and use the TABLE function to convert it into a queriable data source. This example shows you how to update a table (Test_096 here) from data held in a collection:
create table test_095 (col_1  number, col_2  number);

create table test_096 (col_1  number, col_2  number);

insert into test_095 values (1,10);
insert into test_095 values (2,20);

insert into test_096 values (1,0);

create or replace TYPE rec_type IS object
 (c1 number
 ,c2 number);
 /
 
create or replace  TYPE tab_type IS TABLE OF rec_type;
/


declare
 
   src tab_type;
begin
 
   select rec_type(c.col_1,c.col_2) BULK COLLECT INTO src from test_095 c;
  
   MERGE INTO test_096 DEST
   USING( SELECT c1,c2 from table(src) ) s
   ON (DEST.col_1 = s.c1)
   WHEN MATCHED THEN
   UPDATE SET DEST.col_2 = -1
   WHEN NOT MATCHED THEN
   INSERT (col_1,col_2) VALUES( s.c1, s.c2 ); 
 END;
 /
 

select * from test_096;
Previous Topic: Job schedule (merged)
Next Topic: Storing image in database
Goto Forum:
  


Current Time: Thu Dec 08 10:23:45 CST 2016

Total time taken to generate the page: 0.11694 seconds