Merge Statement with PL/SQL Collection type [message #385741] |
Tue, 10 February 2009 22:48  |
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 #385814 is a reply to message #385741] |
Wed, 11 February 2009 03:29   |
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   |
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  |
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;
|
|
|