Development question?

Development question?

Post by mdn.. » Thu, 20 May 1999 04:00:00



Looking for some development suggestions.  I need to merge multiple
tables containing customer-supplied data into a single table for
display on a web front end.

I want to merge the tables together into a single table but want to do
inserts only if the record doesn't exist, and updates only if there is
a change to the data.  Don't want to re-populate the table each time I
do a merge which would occur nightly.  Any development suggestions
would be appreciated.  I am not a developer but I am helping out on
a "hot fire" project.  I am from the DBA world.

Thanks in advance,

Mike Neal

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

 
 
 

Development question?

Post by Martin Jesterhou » Thu, 20 May 1999 04:00:00


Mike,

Quote:>Looking for some development suggestions.  I need to merge multiple
>tables containing customer-supplied data into a single table for
>display on a web front end.

Have you considered views?

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

 
 
 

Development question?

Post by mdn.. » Fri, 21 May 1999 04:00:00




> Mike,

> >Looking for some development suggestions.  I need to merge multiple
> >tables containing customer-supplied data into a single table for
> >display on a web front end.

> Have you considered views?

> ----------------------------------------------------------------------
---------


Martin,
Actually I did think about it, but I don't believe that is the ideal
strategy to use.  The tables within "multiple tables" will change quite
often. (i.e. new customer decides to start sending us data which
populates that table, or customers descides to stop sending data).  In
such a situation, the view would have to be re-created to include the
new tables or exclude the removed one, correct?  Other suggestions?

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

 
 
 

Development question?

Post by Thomas Ky » Fri, 21 May 1999 04:00:00



(if that email address didn't require changing)


>Looking for some development suggestions.  I need to merge multiple
>tables containing customer-supplied data into a single table for
>display on a web front end.

>I want to merge the tables together into a single table but want to do
>inserts only if the record doesn't exist, and updates only if there is
>a change to the data.  Don't want to re-populate the table each time I
>do a merge which would occur nightly.  Any development suggestions
>would be appreciated.  I am not a developer but I am helping out on
>a "hot fire" project.  I am from the DBA world.

Here is how to do that.  You can use a single UPDATE/INSERT pair of statements.
Assume 'new_data' represents one of your customer supplied data tables.
merged_data is the single table.  It might look like:

SQL> create table new_data( x      int primary key,
  2                         data   varchar2(255) );
Table created.

SQL> create table merged_data( y        int primary key,
  2                            mdata varchar2(255) );
Table created.

SQL> insert into new_data values ( 1, 'First Go Around' );
SQL> insert into new_data values ( 2, NULL );
SQL> insert into new_data values ( 3, NULL );

SQL> -- works as long as chr(0) is not a valid value for data!!
SQL> --
SQL> -- can use:
SQL> -- ( ( x <> y ) OR
SQL> --   ( x is not null and y is null ) OR
SQL> --   ( x is null and y is not null ))
SQL> --
SQL> -- instead of nvl...
SQL>
SQL> update
  2    ( select *
  3        from new_data, merged_data
  4       where new_data.x = merged_data.y
  5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr(0))
  6    )
  7  set mdata = data
  8  /

0 rows updated.

SQL>
SQL> insert into merged_data
  2  select *
  3    from new_data
  4   where not exists ( select NULL
  5                        from merged_data
  6                       where merged_data.y = new_data.x )
  7  /

3 rows created.

SQL> select * from merged_data
  2  /

         Y MDATA
---------- ----------------------------------------
         1 First Go Around
         2
         3

SQL> insert into new_data values ( 4, 'Second Go Around' );
SQL> update new_data set data = NULL where x = 1;
SQL> update new_data set data = 'Updated from NULL' where x = 2;

SQL> update
  2    ( select *
  3        from new_data, merged_data
  4       where new_data.x = merged_data.y
  5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr(0))
  6    )
  7  set mdata = data
  8  /
2 rows updated.

SQL> insert into merged_data
  2  select *
  3    from new_data
  4   where not exists ( select NULL
  5                        from merged_data
  6                       where merged_data.y = new_data.x )
  7  /

1 row created.

SQL> select * from merged_data
  2  /

         Y MDATA
---------- ----------------------------------------
         1
         2 Updated from NULL
         3
         4 Second Go Around

Quote:>Thanks in advance,

>Mike Neal

>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation

 
 
 

Development question?

Post by mdn.. » Fri, 21 May 1999 04:00:00


With this type of approach, wouldn't I have a "pair" of statements per
customer supplied table?  The # of customer supplied tables here could
eventually be in the 100s.  Trying not to create high-maintanence code
for the long run.  I WANT to try and have a nightly run which will
merge ALL the customer-supplied tables into the merged table to refresh
the data within.

Any additional comments/suggestions?




> (if that email address didn't require changing)

> >Looking for some development suggestions.  I need to merge multiple
> >tables containing customer-supplied data into a single table for
> >display on a web front end.

> >I want to merge the tables together into a single table but want to
do
> >inserts only if the record doesn't exist, and updates only if there
is
> >a change to the data.  Don't want to re-populate the table each time
I
> >do a merge which would occur nightly.  Any development suggestions
> >would be appreciated.  I am not a developer but I am helping out on
> >a "hot fire" project.  I am from the DBA world.

> Here is how to do that.  You can use a single UPDATE/INSERT pair of
statements.
> Assume 'new_data' represents one of your customer supplied data
tables.
> merged_data is the single table.  It might look like:

> SQL> create table new_data( x      int primary key,
>   2                         data   varchar2(255) );
> Table created.

> SQL> create table merged_data( y        int primary key,
>   2                            mdata varchar2(255) );
> Table created.

> SQL> insert into new_data values ( 1, 'First Go Around' );
> SQL> insert into new_data values ( 2, NULL );
> SQL> insert into new_data values ( 3, NULL );

> SQL> -- works as long as chr(0) is not a valid value for data!!
> SQL> --
> SQL> -- can use:
> SQL> -- ( ( x <> y ) OR
> SQL> --   ( x is not null and y is null ) OR
> SQL> --   ( x is null and y is not null ))
> SQL> --
> SQL> -- instead of nvl...
> SQL>
> SQL> update
>   2    ( select *
>   3        from new_data, merged_data
>   4       where new_data.x = merged_data.y
>   5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
(0))
>   6    )
>   7  set mdata = data
>   8  /

> 0 rows updated.

> SQL>
> SQL> insert into merged_data
>   2  select *
>   3    from new_data
>   4   where not exists ( select NULL
>   5                        from merged_data
>   6                       where merged_data.y = new_data.x )
>   7  /

> 3 rows created.

> SQL> select * from merged_data
>   2  /

>          Y MDATA
> ---------- ----------------------------------------
>          1 First Go Around
>          2
>          3

> SQL> insert into new_data values ( 4, 'Second Go Around' );
> SQL> update new_data set data = NULL where x = 1;
> SQL> update new_data set data = 'Updated from NULL' where x = 2;

> SQL> update
>   2    ( select *
>   3        from new_data, merged_data
>   4       where new_data.x = merged_data.y
>   5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
(0))
>   6    )
>   7  set mdata = data
>   8  /
> 2 rows updated.

> SQL> insert into merged_data
>   2  select *
>   3    from new_data
>   4   where not exists ( select NULL
>   5                        from merged_data
>   6                       where merged_data.y = new_data.x )
>   7  /

> 1 row created.

> SQL> select * from merged_data
>   2  /

>          Y MDATA
> ---------- ----------------------------------------
>          1
>          2 Updated from NULL
>          3
>          4 Second Go Around

> >Thanks in advance,

> >Mike Neal

> >--== Sent via Deja.com http://www.deja.com/ ==--
> >---Share what you know. Learn what you don't.---

> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
Oracle8i'...

> Thomas Kyte

> Oracle Service Industries
> Reston, VA   USA

> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
 
 
 

Development question?

Post by Kevin A Lewi » Fri, 21 May 1999 04:00:00


Surely you will find view are okay unless you need to structurally change
the table, adding, updating or deleting data from the based tables should
have NO effect on the views.
However any new select on a view will bring back the new data.

In fact if you would actively want to use views in this case as a merged
table will need constant maintenance.

If you MUST have a merged table have a look at Materialized Views in 8.1
(i.e. 8i) this gives the best of both worlds.

Regards

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)

The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.



> > Mike,

> > >Looking for some development suggestions.  I need to merge multiple
> > >tables containing customer-supplied data into a single table for
> > >display on a web front end.

> > Have you considered views?

> > ----------------------------------------------------------------------
> ---------

> Martin,
> Actually I did think about it, but I don't believe that is the ideal
> strategy to use.  The tables within "multiple tables" will change quite
> often. (i.e. new customer decides to start sending us data which
> populates that table, or customers descides to stop sending data).  In
> such a situation, the view would have to be re-created to include the
> new tables or exclude the removed one, correct?  Other suggestions?

> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---

 
 
 

Development question?

Post by mdn.. » Fri, 21 May 1999 04:00:00


OK...here is my concern.  I create a view based on 3 tables (A,B,and C).

Format of all tables.

MFG   PART_NUM   URL
---   --------   ---
AAA   0123-abc   http://www.somesite.com/subdir/somefile.txt
BBB   0124-bca   http://www.anothersite.com/subdir/otherfile.txt

Tables A,B,and C would contain data like the above from different
suppliers.  I would want to nightly merge them into for instance
PROD_URLS.  A week down the road D, E, and F join and want to start
sending me equal data to above.  Would I not have to recreate the view
with each addition to select from the newly added table or do I just
not understand views at all?

Thanks for all the help, and sorry for the possible ignorance.

Mike Neal



> Surely you will find view are okay unless you need to structurally
change
> the table, adding, updating or deleting data from the based tables
should
> have NO effect on the views.
> However any new select on a view will bring back the new data.

> In fact if you would actively want to use views in this case as a
merged
> table will need constant maintenance.

> If you MUST have a merged table have a look at Materialized Views in
8.1
> (i.e. 8i) this gives the best of both worlds.

> Regards

> --
> Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich
England)

> The views expressed herein by the author of this document
> are not necessarily those of BOCM PAULS Ltd.




> > > Mike,

> > > >Looking for some development suggestions.  I need to merge
multiple
> > > >tables containing customer-supplied data into a single table for
> > > >display on a web front end.

> > > Have you considered views?

> > > ------------------------------------------------------------------
----
> > ---------

> > Martin,
> > Actually I did think about it, but I don't believe that is the ideal
> > strategy to use.  The tables within "multiple tables" will change
quite
> > often. (i.e. new customer decides to start sending us data which
> > populates that table, or customers descides to stop sending data).
In
> > such a situation, the view would have to be re-created to include
the
> > new tables or exclude the removed one, correct?  Other suggestions?

> > --== Sent via Deja.com http://www.deja.com/ ==--
> > ---Share what you know. Learn what you don't.---

--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
 
 
 

Development question?

Post by Thomas Ky » Fri, 21 May 1999 04:00:00



(if that email address didn't require changing)


>With this type of approach, wouldn't I have a "pair" of statements per
>customer supplied table?  The # of customer supplied tables here could
>eventually be in the 100s.  Trying not to create high-maintanence code
>for the long run.  I WANT to try and have a nightly run which will
>merge ALL the customer-supplied tables into the merged table to refresh
>the data within.

>Any additional comments/suggestions?

Oh, you wanted the whole program :)

No problem.  In Oracle8i, release 8.1 it would be:

create or replace procedure merge_data( p_tname in varchar2 )
as
begin
        execute immediate
         'update
           ( select *
               from ' || p_tname || ' a, merged_data
              where a.x = merged_data.y
                and nvl(a.data,chr(0)) <> nvl(merged_data.mdata,chr(0))
           )
             set mdata = data';

    execute immediate
          'insert into merged_data
           select *
             from ' || p_tname || ' a
            where not exists ( select NULL
                                 from merged_data
                                where merged_data.y = a.x )';

    commit;
end;

and then if you had a list of 'new data' tables in some table T, you would just:

begin
   for x in ( select tname from T )
   loop
         merge_data( x.t );
   end loop;
end;
/

In 8.0 and before, you would have to code another procedure, execute_immediate,
as follows and pass the update/insert to it instead of using the verbs execute
immediate as above.

create or replace procedure execute_immediate( sql_stmt in varchar2 )
as
    exec_cursor     integer default dbms_sql.open_cursor;
    rows_processed  number  default 0;
begin
    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );
end;
/




>> (if that email address didn't require changing)

>> >Looking for some development suggestions.  I need to merge multiple
>> >tables containing customer-supplied data into a single table for
>> >display on a web front end.

>> >I want to merge the tables together into a single table but want to
>do
>> >inserts only if the record doesn't exist, and updates only if there
>is
>> >a change to the data.  Don't want to re-populate the table each time
>I
>> >do a merge which would occur nightly.  Any development suggestions
>> >would be appreciated.  I am not a developer but I am helping out on
>> >a "hot fire" project.  I am from the DBA world.

>> Here is how to do that.  You can use a single UPDATE/INSERT pair of
>statements.
>> Assume 'new_data' represents one of your customer supplied data
>tables.
>> merged_data is the single table.  It might look like:

>> SQL> create table new_data( x      int primary key,
>>   2                         data   varchar2(255) );
>> Table created.

>> SQL> create table merged_data( y        int primary key,
>>   2                            mdata varchar2(255) );
>> Table created.

>> SQL> insert into new_data values ( 1, 'First Go Around' );
>> SQL> insert into new_data values ( 2, NULL );
>> SQL> insert into new_data values ( 3, NULL );

>> SQL> -- works as long as chr(0) is not a valid value for data!!
>> SQL> --
>> SQL> -- can use:
>> SQL> -- ( ( x <> y ) OR
>> SQL> --   ( x is not null and y is null ) OR
>> SQL> --   ( x is null and y is not null ))
>> SQL> --
>> SQL> -- instead of nvl...
>> SQL>
>> SQL> update
>>   2    ( select *
>>   3        from new_data, merged_data
>>   4       where new_data.x = merged_data.y
>>   5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
>(0))
>>   6    )
>>   7  set mdata = data
>>   8  /

>> 0 rows updated.

>> SQL>
>> SQL> insert into merged_data
>>   2  select *
>>   3    from new_data
>>   4   where not exists ( select NULL
>>   5                        from merged_data
>>   6                       where merged_data.y = new_data.x )
>>   7  /

>> 3 rows created.

>> SQL> select * from merged_data
>>   2  /

>>          Y MDATA
>> ---------- ----------------------------------------
>>          1 First Go Around
>>          2
>>          3

>> SQL> insert into new_data values ( 4, 'Second Go Around' );
>> SQL> update new_data set data = NULL where x = 1;
>> SQL> update new_data set data = 'Updated from NULL' where x = 2;

>> SQL> update
>>   2    ( select *
>>   3        from new_data, merged_data
>>   4       where new_data.x = merged_data.y
>>   5         and nvl(new_data.data,chr(0)) <> nvl(merged_data.mdata,chr
>(0))
>>   6    )
>>   7  set mdata = data
>>   8  /
>> 2 rows updated.

>> SQL> insert into merged_data
>>   2  select *
>>   3    from new_data
>>   4   where not exists ( select NULL
>>   5                        from merged_data
>>   6                       where merged_data.y = new_data.x )
>>   7  /

>> 1 row created.

>> SQL> select * from merged_data
>>   2  /

>>          Y MDATA
>> ---------- ----------------------------------------
>>          1
>>          2 Updated from NULL
>>          3
>>          4 Second Go Around

>> >Thanks in advance,

>> >Mike Neal

>> >--== Sent via Deja.com http://www.deja.com/ ==--
>> >---Share what you know. Learn what you don't.---

>> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
>Oracle8i'...

>> Thomas Kyte

>> Oracle Service Industries
>> Reston, VA   USA

>> --
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation

>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...

Thomas Kyte

Oracle Service Industries
Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation