troubleshooting Question

update using pipeline function

Avatar of sam_2012
sam_2012 asked on
Oracle Database
3 Comments1 Solution171 ViewsLast Modified:
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)
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros