2

In a SSIS package I build, I need to capture the output of a update clause in Oracle, in order to send a warning email

I have read the related question Is there an Oracle equivalent to SQL Server's OUTPUT INSERTED.*? But it doesn't give me a proper result set, that I can capture through a Execute SQL task

DECLARE
TYPE ra_InfoErrorMail is RECORD
(LFUKID Crpdta.F580002.LFUKID%TYPE
,LFAA10 Crpdta.F580002.LFAA10%TYPE
,LFJOBDETLS Crpdta.F580002.LFJOBDETLS%TYPE);
TYPE ta_InfoErrorMail is  TABLE OF ra_InfoErrorMail;
t_InfoErrorMail ta_InfoErrorMail;

BEGIN
UPDATE CRPDTA.F580002 SET LFKY = 'ERROR' WHERE LFAA10 = 'MYPROJECT' AND LFUSER ='MYUSER'
RETURNING  LFUKID,LFAA10,LFJOBDETLS   BULK COLLECT  INTO t_InfoErrorMail;


--SELECT LFUKID,LFAA10,LFJOBDETLS FROM t_InfoErrorMail t; -- this doesn't work


END;

How do I get the whole t_InfoErrorMail in a neat SSIS-capturable ResultSet ?

Johan Buret
  • 2,614
  • 24
  • 32
  • Thanks Johan Buret. I wondered if you have tried compiling `ra_InfoErrorMail` and `ta_InfoErrorMail` as persistent object types via `CREATE TYPE...`, and then `... RETURNING RA_INFOERRORMAIL(LFUKID,LFAA10,LFJOBDETLS) BULK COLLECT INTO t_InfoErrorMail ...`? – alexgibbs Apr 04 '19 at 17:11
  • Could you just have a select statement in an `Execute SQL` task before your update statement? You could just `select blah WHERE LFAA10 = 'MYPROJECT' AND LFUSER ='MYUSER' ` and then have an `execute sql` task that does the update? – Scotch Apr 04 '19 at 18:48

0 Answers0