[AlanP]

I cant get this merge command to work in a stored proc (Oracle 9.0.1). It works fine in SQLPlus but not in the stored proc where it throws up an ORA-1790: expression must have same datatype as corresponding expression ORA-6512. Anyone got any clues?

CREATE OR REPLACE PROCEDURE DEALERPROFILE_UPDATE(netid number,bikes_yn char, bodyshop_yn char, LABOURGPANALYSIS_YN char, MINI_YN char, OTHER_YN char, PARTSRGPANALYSIS_YN char) IS
BEGIN
merge into networkentityitemgroupoption x
using (
select netid networkentity_id, 449 itemgroup_id, BIKES_YN selected from dual union
select netid networkentity_id, 395 itemgroup_id, BODYSHOP_YN selected from dual union
select netid networkentity_id, 303 itemgroup_id, LABOURGPANALYSIS_YN selected from dual union
select netid networkentity_id, 363 itemgroup_id, LABOURGPANALYSIS_YN selected from dual union
select netid networkentity_id, 192 itemgroup_id, MINI_YN selected from dual union
select netid networkentity_id, 249 itemgroup_id, MINI_YN selected from dual union
select netid networkentity_id, 454 itemgroup_id, OTHER_YN selected from dual union
select netid networkentity_id, 259 itemgroup_id, PARTSRGPANALYSIS_YN selected from dual union
select netid networkentity_id, 269 itemgroup_id, PARTSRGPANALYSIS_YN selected from dual
) y
on (x.networkentity_id=y.networkentity_id and x.itemgroup_id=y.itemgroup_id)
when matched then update set x.selected=y.selected
when not matched then insert (x.networkentity_id, x.itemgroup_id, x.selected) values (y.networkentity_id, y.itemgroup_id,y.selected);
END DEALERPROFILE_UPDATE_TRIG;
/


CREATE TABLE NETWORKENTITYITEMGROUPOPTION (
NETWORKENTITY_ID NUMBER (38) NOT NULL,
ITEMGROUP_ID NUMBER (38) NOT NULL,
SELECTED CHAR (1),
CONSTRAINT NETENTITYITEMGROUPOPTION_PK
PRIMARY KEY ( NETWORKENTITY_ID, ITEMGROUP_ID )
USING INDEX TABLESPACE INDX_SMALL PCTFREE 10
)
TABLESPACE DATA_SMALL