Avatar of Swaminathan K
Swaminathan K
Flag for India asked on

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)
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Swaminathan K

Thanks a lot. really appreciate
slightwv (䄆 Netminder)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.