Solved

Using results of a cursor in another cursor

Posted on 2008-06-26
6
338 Views
Last Modified: 2013-12-18
Hi

I'm looking at creating a procedure that will allow me to check to see if data has changed in a column from staging table compared with the reporting table. Yep simple

select * from stage_tbl s
where exists (select 1 from report_tbl r
                       where s.id = r.id
                       and s.compare_col_a <> r.compare_col_a
                       )
but .... I have a number of columns to compare between the two table and I can't rely on the columns having the same name.  So I have created a mapping table that consists of the column names between the two tables.  Though how can I make 'and s.compare_col_a <> r.compare_col_a' dynamic so it accepts the other compare columns such as 'and s.compare_col_b <> r.compare_col_b'

Your help would certainly be appreciated
Thanks
0
Comment
Question by:alines
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Expert Comment

by:OMC2000
ID: 21876856
if you need such procedure for various tables dynamic query might make sense, however it is much slower than direct query.
So I would recommend to select records from you mapping table once to construct this query and then make static SQL statement
select ' and ' || first_table_field || ' <> ' || second_table_field from mapping table.

If you nevertheless need dynamic SQL, you can use the same statement in cursor and construct the query dynamically in your procedure
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 21877857
alines,
    If you just want to know which records contain a change and you don't need to know WHICH column, then you could use the minus (assuming the columns are in the same order or can be arranged to be in the same order).  Of course, you need to make sure it's only when the id exists, but that can be done with a simple IN.  Set operations are pretty fast:

create table updates_to_process as
select * from staging_table
 where id in
        (select id from reporting_table)
minus
select * from reporting_table;

ANY change in the data will result in the MINUS not trimming that record from the set going to the "updates_to_process".

Good luck!
0
 
LVL 15

Expert Comment

by:ishando
ID: 21881231
Are you looking for something like this...


create or replace procedure StgRptCompare(inStgTable in varchar2, inRptTable in varchar2)
is
  type cur_ref is ref cursor;
  cursor cColList is select colA, colB from table_map where <<some condition>>;
  cr cur_ref;
 
  sql_qry    varchar2(4000);
begin
  sql_qry := 'select * from ' || inStgTable || ' s ' ||
             'where exists (select 1 from ' || inRptTable || ' r ' ||
             '              where s.id = r.id ';
  
  for c1 in cColList loop
    sql_qry := sql_qry || 'and s.' || c1.colA || ' <> r.' || c1.colB || ' ';
  end loop;
  sql_qry := sql_qry || ')';
 
  open cr for sql_qry;
  ...

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:alines
ID: 21882299
Hi Ishando

I'm pretty sure this is what I'm looking for.  Will it allow me to create a cursor that will loop each of the compare columns 'c1.colA || ' <> r.' || c1.colB ' so that any data that is returned can then be incorporated into an insert statement.

Thanks

Andrew
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 500 total points
ID: 21882971
Andrew,
    Other than the fact that it is using a negative join, rather than a set operation (so it will perform worse), this has the same limitations as what I proposed - you don't know the column that caused the record to be selected.  If your issue with the MINUS approach is that you won't use every column, then just generate those:

create or replace procedure GrabDifferences(StagingTable varchar2, ReportTable varchar2) is
  Type varchartable is table of varchar2(2000);
  StageColumns varchartable;
  ReportColumns varchartable;
  StageSelect varchar2(2000);  -- you may need this to be longer
  ReportSelect varchar2(2000);
  StagePK varchar2(30);
  ReportPK varchar2(30);
  this number;
begin

    select column_name bulk collect into StageColumns
       from table_map
     where table_name=StagingTable
     order by column_position;

    select column_name bulk collect into ReportColumns
       from table_map
     where table_name=ReportTable
     order by column_position;

    select column_name into StagePK from table_map where table_name=StagingTable and PK='Y';
    select column_name into ReportPK from table_map where table_name=ReportTable and PK='Y';

    for this in StageColumns.first..StageColumn.Last loop
        StageSelect := StageSelect || ',' || StageColumns(this);
        ReportSelect := ReportSelect || ',' || ReportSelect(this);
   end loop;

   execute immediate
            'insert into '||StagingTable|| '_changes ' ||
                'select ' || ltrim(StageSelect,',') || ' from ' || StagingTable ||
                '  where ' || StagePK || ' in (select ' || ReportPK || ' from ' || ReportTable || ')' ||
                ' minus' ||
                ' select ' || ltrim(ReportSelect,',') || ' from ' || ReportTable ;
end;
.

If you want to know which column(s) is/are responsible for the difference you'll need to pivot the table.  Let us know if that's the goal.

Good luck!
0
 

Author Closing Comment

by:alines
ID: 31471034
DrSQL .... Thanks for your help.  Why is it the simplest solutions are always the best.

Also thanks to everyone else who replied
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
format dd/mm/yyyy parameter 16 58
setting local variables in a cursor block 3 41
Oracle Errors 11 82
How do I search for a text string in my SQL code 18 48
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question