Hi Team,
It is in continuation to my previous questions
Here is what Iam trying to achieve I have written a pipeline function that gives me the records that has error condition.
Create or replace type t_profile_data is object
(
rid varchar2(50 byte),
profileid number,
error_code varchar2(40),
error_desc varchar2(1000),
status char
);
/
Create or replace type tt_profile_tab is table of t_profile_data;
/
create or replace
FUNCTION f_sendErroredData return tt_profile_tab pipelined is
v_errordesc varchar2(1000);
v_idx number;
begin
v_errordesc :=NULL;
for rec in (Select rowid rid , id, create_date, name cname from test_data)
loop
if rec.id is null then
if instr(v_errordesc ,'Missing Fields') >0 then
v_errordesc:=v_errordesc || 'ID' ||',';
else
v_errordesc:='Missing Fields:' || 'ID' ||',';
End if;
End If;
If rec.cname is null then
if instr(v_errordesc ,'Missing Fields') >0 then
v_errordesc:=v_errordesc || 'Name' ||',';
else
v_errordesc:='Missing Fields:' || rec.cname ||',';
End if;
End if;
if rec.Create_date is null then
if instr(v_errordesc ,'Missing Fields') >0 then
v_errordesc:=v_errordesc || 'Create_date' ||',';
else
v_errordesc:='Missing Fields:' || 'Create_date' ||',';
End if;
End IF;
if instr(v_errordesc,'Missing Fields') >0 then
pipe row (t_profile_data (rec.rid ,rec.id,'MISSID',rtrim(v_errordesc,','),'E'));
End If;
v_errordesc:=NULL;
v_idx:=v_idx+1;
end loop;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error occurred');
end;
test_data table
Id Name create_date Status Error_desc Error_code
1 XYZ 07-MAR-17 NULL NULL NULL
NULL XYZ 07-MAR-17 NULL NULL NULL
NUll XYZ NULL NULL NULL NULL
The below query works
Select
* from test_data a
where
exists (select 1 from table(f_sendErroredData) b where b.rid=a.rowid)
Id Name create_date Status Error_desc Error_code
NULL XYZ 07-MAR-17 NULL NULL NULL
NUll XYZ NULL NULL NULL NULL
select * from table(f_sendErroredData), below is the output :
AAAXfEAAKAAAmRGAAB MISSID Missing Fields:ID E
AAAXfEAAKAAAmRGAAC MISSID Missing Fields:ID,Create_date E
But this update does not work : Any reason why it is failing. It gives 0 rows updated
Update test_data a
Set ( status, error_Code,error_Description ) = (Select status,error_code,error_Desc from table(f_sendErroredData) b where b.rid=a.rowid)
where
exists (select 1 from table(f_sendErroredData) b where b.rid=a.rowid)