update using pipeline function

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;
v_errordesc :=NULL;
for rec in (Select rowid rid , id, create_date, name cname from test_data)

          if rec.id is null then          
                if instr(v_errordesc ,'Missing Fields') >0 then
                  v_errordesc:=v_errordesc || 'ID' ||',';
                   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' ||',';
                   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' ||',';
                   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;
end loop;
when others then
DBMS_OUTPUT.PUT_LINE('Error occurred');

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

* from test_data a
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)
exists (select 1 from table(f_sendErroredData) b where b.rid=a.rowid)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Look at how many times you are selecting from the table.

The pipelined function doesn't need to query the entire table and pipe everything back then join against the table again.  Remember, I mentioned in your previous question that a pipelined function probably isn't what you need.

Why are you convinced that is the right approach?

Just create a function that accepts the values, validates the values and returns the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
Thanks a lot. really appreciate
slightwv (䄆 Netminder) Commented:
I'm thinking there will be a new question for this project coming.

Before you ask it, think again about the scope of the project.  I know you don't want to hard-code anything and want everything dynamic but that is next to impossible,

You would need validation code for every possible data type Oracle has.  Oracle can create new ones with every release.  You'll need to constantly tweak your code.

You'll also need to check every column for things like check constraints.

Just because a column is declared as char(1) doesn't mean that you can insert ANY single character in it.  I might have a check constraint that only allows 'Y' and 'N'.  If your staging table has 'X', just based on length, it appears that it should be good but it will fail on insert.

You'll also need to know which columns can be null and which cannot.

What if a column is a foreign key to another table?  You would need to check that the value exists in that table as part of the check.

You'll also need to know the column mappings between the staging table and destination table.  What column name goes with what column name.

What if I create a virtual column in my destination table?  You don't insert into those.

I'm not saying that what you want is impossible.  I'm trying to let you know that it is a HUGE effort and you'll probably not get everything you need by asking questions here.  For example, I've provided a regex to validate a single and specific number format.  It won't work for all possible combinations.  To try and validate all possible, it would likely be much more complex.

As I posted in one of the other questions, I would look into the EXCEPTIONS table and just do the insert then check what values didn't make it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.