Insert values from select query into variables

Question

I'm trying to insert into 2 variables values from select statement but I got an error which says:

exact fetch returns more than requested number of rows

I'm guessing that I need to put that data from select query into some table type? But I need to process single record for the purpose of comparing to variable v_zmienna with v_zmienna2.

As you can see I try to somehow transfer data into v_zmienna and v_zmienna2 and after that compare that values.

Could anyone take a look and propose what's the best approach?

I need to use execute immediate and variables because I have Table1, Table2 tables as configure tables with names of tables which should be connected etc.

declare
c1 SYS_REFCURSOR;  
v_sourcepkcolumn varchar2(50);
v_sourcetable varchar2(50); 
v_targetfkcolumn varchar2(50); 
v_targetpkcolumn varchar2(50);    
v_targettable varchar2(50); 
v_sourcecolumn varchar2(50);  
v_targetcolumn varchar2(50); 
v_verificationdefid varchar2(50); 
v_zmienna varchar2(50); 
v_zmienna2 varchar2(50); 
v_random_number number;
begin
v_random_number := sequence1.nextval;
open c1 for select  sourcepkcolumn,
                    sourcetable,
                    targetfkcolumn,
                    targetpkcolumn,
                    targettable,
                    sourcecolumn,
                    targetcolumn,
                    ID
            from    table1,table2 where table1.ID=table2.ID;
loop
  fetch c1 into   v_sourcepkcolumn, 
                  v_sourcetable,    
                  v_targetfkcolumn, 
                  v_targetpkcolumn,   
                  v_targettable, 
                  v_sourcecolumn,    
                  v_targetcolumn, 
                  v_verificationdefid;
  exit when c1%notfound;   

  execute immediate 'select '||v_sourcetable||'.'||v_sourcecolumn||','||v_targettable||'.'||v_targetcolumn||' from '||v_sourcetable||','||v_targettable||' where '||
v_sourcetable||'.'||v_sourcepkcolumn||'='||v_targettable||'.'||v_targetfkcolumn into v_zmienna,v_zmienna2;

  if (v_zmienna=v_zmienna2) then
    execute immediate 'insert into table3 values ('||v_random_number||','||v_sourcecolumn||','||v_sourcepkcolumn||','||v_zmienna||',1,'||v_targetcolumn||','||v_targetpkcolumn||','||v_zmienna2||',timestamp,'||v_verificationdefid||')';
  else 
    execute immediate 'insert into table3 values ('||v_random_number||','||v_sourcepkcolumn||','||v_zmienna||',0,'||v_targetcolumn||','||v_targetpkcolumn||','||v_zmienna2||',timestamp,'||v_verificationdefid||')';
  end if;
end loop;
close c1;
end;

EDIT:

Output of

select  sourcepkcolumn,
                    sourcetable,
                    targetfkcolumn,
                    targetpkcolumn,
                    targettable,
                    sourcecolumn,
                    targetcolumn,
                  from    table1,table2 where table1.id=table2.id

Could looks:

PK_ACCOUNT,ACCOUNT,PK_ACC,ID,ACCOUNT2,NAME,NAME

That's the output from table1 and table2,

IMPORTANT: ACCOUNT AND ACCOUNT2 ARE VIEWS! that's no TABLES.

So now I wan't to compare account.name and account2.name from account VIEW with account2 VIEW on account.pk_account=account2.pk_acc. Generally it works perfectly but sometimes (depends on oracle order) records in VIEW after join can be in other order. For example: Account:

PK_ACCOUNT   NAME
1             Eva
1             Adam

and Account2

PK_ACCOUNT  NAME
1            Adam
1            Eva

Why in VIEW different PK_ACCOUNT? Because I need to insert into Account and Account2 data from multiple tables. That's why records sometimes multiplies.

When procedure process one by one every records then takes it as divergent

Answer 1

I think you could just rewrite this as:

declare
  v_random_number number;
begin
  v_random_number := sequence1.nextval;
  for rec in (select  sourcepkcolumn,
                      sourcetable,
                      targetfkcolumn,
                      targetpkcolumn,
                      targettable,
                      sourcecolumn,
                      targetcolumn,
                      ID
              from    table1
                      inner join table2 on (table1.ID = table2.ID))
  loop
    execute immediate 'insert into table3 '||chr(10)|| -- where is the list of columns to insert into?!
                      ' select :v_random_number random_number,'||chr(10)||
                      '        :v_sourcecolumn source_col,'||chr(10)||
                      '        :v_sourcepkcolumn source_pk_col,'||chr(10)||
                      '        src.'||v_sourcecolumn||' source_col,'||chr(10)||
                      '        case when src.'||v_sourcecolumn||' = tgt.'||v_targetcolumn||' then 1 else 0 end one_or_zero'||chr(10)||
                      '        :v_targetcolumn target_col'||chr(10)||
                      '        :v_targetpkcolumn target_pk_col'||chr(10)||
                      '        tgt.'||v_targetcolumn||' target_col,'||chr(10)||
                      '        systimestamp tstamp,'||chr(10)||
                      '        :v_verificationdefid'||chr(10)||
                      ' from   '||v_sourcetable||' src'||chr(10)||
                      '        inner join '||v_targettable||' tgt on (src.'||v_sourcepkcolumn||' = tgt.'||v_targetfkcolumn||')'
                      using v_random_number,
                            rec.sourcecolumn,
                            rec.sourcepkcolumn,
                            rec.targetcolumn,
                            rec.targetpkcolumn,
                            rec.id;
  end loop;
end;
/

N.B. Untested. I guessed that your 2nd insert statement was missing the sourcecolumn, so I added that in.

Answer 2

The error you are getting indicates that this statement is returning more than one row

execute immediate 'select '||v_sourcetable||'.'||v_sourcecolumn||','||v_targettable||'.'||v_targetcolumn||' from '||v_sourcetable||','||v_targettable||' where '||
v_sourcetable||'.'||v_sourcepkcolumn||'='||v_targettable||'.'||v_targetfkcolumn into v_zmienna,v_zmienna2;

You have more than one way to fix that, depending on your goal you may choose.

My reccomended method would be to change the approach a little bit. Instead of selecting into a set of variables, comparing them and doing different things depending on the variables, you can use a different method. First, insert into table3 all the rows where your dynamic condition is met. Then, insert into table3 all the rows where your dynamic condition is not met. This would be something like this:

execute immediate 'insert into table3 values ('||v_random_number||','||v_sourcecolumn||','||v_sourcepkcolumn||','||v_zmienna||',1,'||v_targetcolumn||','||v_targetpkcolumn||','||v_zmienna2||',timestamp,'||v_verificationdefid||') ' ||
' select '||v_sourcetable||'.'||v_sourcecolumn||','||v_targettable||'.'||v_targetcolumn||' from '||v_sourcetable||','||v_targettable||' where '||
v_sourcetable||'.'||v_sourcepkcolumn||'='||v_targettable||'.'||v_targetfkcolumn );

execute immediate 'insert into table3 values ('||v_random_number||','||v_sourcepkcolumn||','||v_zmienna||',0,'||v_targetcolumn||','||v_targetpkcolumn||','||v_zmienna2||',timestamp,'||v_verificationdefid||' ||
' select '||v_sourcetable||'.'||v_sourcecolumn||','||v_targettable||'.'||v_targetcolumn||' from '||v_sourcetable||','||v_targettable||' where '||
v_sourcetable||'.'||v_sourcepkcolumn||'<>'||v_targettable||'.'||v_targetfkcolumn );

Your second option would be to do it basically the same way as before, but use cursors on the dynamic part as well as on the fixed part. I'm not going to try to create the source code for that, but it would mean you need to create a cursor for the dynamic select query and do the "if" part inside the cursor loop