Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Development question?

Re: Development question?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 May 1999 17:19:24 GMT
Message-ID: <374a42d0.22673933@newshost.us.oracle.com>


A copy of this was sent to mdneal_at_my-dejanews.com (if that email address didn't require changing) On Thu, 20 May 1999 14:58:50 GMT, you wrote:

>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;
/

>
>In article <37460396.6487037_at_newshost.us.oracle.com>,
> tkyte_at_us.oracle.com wrote:
>> A copy of this was sent to mdneal_at_my-dejanews.com
>> (if that email address didn't require changing)
>> On Wed, 19 May 1999 20:10:40 GMT, you wrote:
>>
>> >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
>> tkyte_at_us.oracle.com
>> 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
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 20 1999 - 12:19:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US