Re: Accessing a cursor using dynamic SQL

If it matters, the Oracle version is 9.2, or it can be 10g if I implemented it on another machine. I tried to avoid specifics and ask generically, but I will be happy to expand on the problem I am trying to solve.

I would like to write a procedure(s) which compares a copy of today's table with a copy of the same table from yesterday, resulting in a list of changes made. I would like to know only the fields that were changed and their old/new values. In the end I want a table that looks like

Action Table Key Field Old New

Add         Tab1     Key1
Update    Tab1     Key1     Dept      123     987
Update    Tab1     Key1     Name     Bob    Tom
Delete     Tab1     Key1

I want this procedure to be as generic as possible, so it can be reused with any table. Finding the deletes and adds are simple enough, and knowing that a record has changed is simple enough. Knowing the particular fields that were updated and their previous and new values is causing me headaches. Since I do not know the structure of the table ahead of time, the query would be built dynamically using the ALL_TAB_COLS table, with a table having 1 to 500 columns. I would then just do an equal join of the tables and then compare each field, reporting any differences. It seems that I would use a cursor when executing this dynamic sql for the join, but I do not know how to define the record that I am fetching into.

Once that is figured out, I will have to also dynamically create a CASE statement to check all the fields, but I am hoping one solution would work for both, or I can worry about playing with anonymous blocks later. I know the problem is complicated (and common), but if I am making the solution too complicated, please let me know. As for the bad performance, I can only imagine. But if that is what it takes to get it done, then that is what it takes. Thanks for any insights.

Mike

DA Morgan wrote:
> mikeg13_at_gmail.com wrote:
> > I am creating dynamic SQL, from the ALL_TAB_COLS table, and the output
> > is unknown until runtime. My SELECT statement may select 4 columns, 20
> > columns, or 200. Once I have built this SQL, how can I run it into a
> > cursor? How do I define that cursor?
>
> This is the definition of a bad idea. You have decided on a technology
> you don't know how to implement to solve a business problem that you
> don't state in a version of the database that is a secret.
>
> What is the business case?
>
> Sorry to be harsh but this has unscalable and bad performance written
> all over it.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Tue Jul 25 2006 - 12:43:15 CDT