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@xxxxxxxxx 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

.

Relevant Pages

  • Re: ORATCL help needed!
    ... set cursor ... Wow, An Oratcl 3.3. ... It is coded with the the OCI layer released with Oracle ... Oracle instant client is available for many many platforms, ...
    (comp.lang.tcl)
  • Re: Using Cursors
    ... it seems I've stirred up a hornet's nest with my cursor question. ... finding the discussion valuable in my circumstances (moving from Oracle to ... SQL Server). ... > DECLARE curEpisode SCROLL CURSOR FOR ...
    (microsoft.public.sqlserver.programming)
  • Strange behaviour with SQLBulkOperations
    ... I'm trying to implement bulk inserts via ODBC. ... I then tried it with Oracle Express, ... // Set the cursor type. ... Shouldn't SQL Server and Oracle support bulk operations? ...
    (microsoft.public.data.odbc)
  • Re: Using Cursors
    ... WHERE refcol IN ... SQL Server specific, ... > I have a question about how to replace a cursor with a set based operation. ... I don't really know Oracle well enough to say, ...
    (microsoft.public.sqlserver.programming)
  • Re: Beginners question : How to use varchar2 within a sql statement ?
    ... in a Oracle 10g database. ... If I try to use the variable tablename in a select statement I get the ... The sql is also parsed at compile time. ... The opposite of static sql is dynamic sql. ...
    (comp.databases.oracle.misc)