Re: Transparent database objects after merging

From: Arup Nanda <orarup_at_hotmail.com>
Date: Tue, 15 Jul 2003 23:47:23 -0400
Message-Id: <25929.337932@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_006A_01C34B2B.701BC190 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Well, then a view is what the doctor ordered.

rename table CUSTOMERS to CUSTOMERS_A
create view CUSTOMERS select * from CUSTOMERS_A UNION ALL = CUSTOMERS_B;

If you do not want to show the duplicates in these tables, make the = UNION ALL just UNION.

HTH. Arup

Original Message -----=20 From: Ukrit.Kuprasit_at_allianz.com.sg=20 To: Multiple recipients of list ORACLE-L=20 Sent: Wednesday, July 16, 2003 12:14 AM Subject: Re: Transparent database objects after merging

Hello,=20

Sorry I might not give all the problem corectly. I need to = access to both CUSTOMERS and also CUSTOMERS_B. Not just only one. If = only one table, my head will not spin like this.=20 ^__^=20
Ukrit K,=20

       "Arup Nanda" <orarup_at_hotmail.com>=20
        Sent by: ml-errors_at_fatcity.com=20
        07/16/2003 11:59 AM=20
        Please respond to ORACLE-L=20

              =20
                To:        Multiple recipients of list ORACLE-L =


<ORACLE-L_at_fatcity.com>=20

                cc:        =20
                Subject:        Re: Transparent database objects after =

merging=20

  You can drop or rename CUSTOMERS to, say, CUSTOMERS_A, and then create = synonym CUSTOMERS pointing to CUSTOMERS_B. Or rename CUSTOMERS_B to = CUSTOMERS.=20
   =20
  HTH.=20
   =20
  Arup Nanda=20

Original Message -----=20 From: Ukrit.Kuprasit_at_allianz.com.sg=20 To: Multiple recipients of list ORACLE-L=20 Sent: Tuesday, July 15, 2003 11:29 PM=20 Subject: Transparent database objects after merging=20

Hello Gurus,=20

         I'm working merging 2 databases which have sane structure of = database objects into one database. However, after merging, the business = policies and regulations force us to have 2 set of database objects; for = example :=20
  Database A has CUSTOMERS table.=20
  Database B has CUSTOMERS table.=20

After merging database A will have CUSTOMERS and CUSTOMERS_B (data = from database B). I just wonder that is there any way that we don't need = to change our script in forms and reports to point into a new table name = but still can access to the data in the new table ( CUSTOMERS_B) not = CUSTOMERS.=20
Thank you for any comment and suggestion.=20 Ukrit K,=20

------=_NextPart_000_006A_01C34B2B.701BC190 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2719.2200" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Well, then a view is what the =
doctor=20
ordered.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>1. rename table CUSTOMERS to=20
CUSTOMERS_A</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>2. create view CUSTOMERS select * from =
CUSTOMERS_A=20
UNION ALL CUSTOMERS_B;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>If you do not want to show the =
duplicates in these=20
tables, make the UNION ALL just UNION.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>HTH.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Arup</FONT></DIV>
<BLOCKQUOTE=20

style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black"><B>From:</B>=20
  <A title=3DUkrit.Kuprasit_at_allianz.com.sg=20   =
href=3D"mailto:Ukrit.Kuprasit_at_allianz.com.sg">Ukrit.Kuprasit_at_allianz.com.=

sg</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =

title=3DORACLE-L_at_fatcity.com=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, July 16, 2003 = 12:14=20
  AM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: Transparent = database objects=20
  after merging</DIV>
  <DIV><BR></DIV><BR><FONT face=3Dsans-serif size=3D2>Hello,</FONT> =
<BR><FONT=20

  face=3Dsans-serif size=3D2>        Sorry I might = not give all=20
  the problem corectly. I need to access to both CUSTOMERS and also = CUSTOMERS_B.=20
  Not just only one. If only one table, my head will not spin like = this.</FONT>=20
  <BR><FONT face=3Dsans-serif size=3D2>^__^</FONT> <BR><FONT = face=3Dsans-serif=20
  size=3D2>Ukrit K,</FONT> <BR><BR><BR>
  <TABLE width=3D"100%">
    <TBODY>
    <TR vAlign=3Dtop>

      <TD>
      <TD><FONT face=3Dsans-serif size=3D1><B>"Arup Nanda" &lt;<A=20
        =

href=3D"mailto:orarup_at_hotmail.com">orarup_at_hotmail.com</A>></B></FONT> =

        <BR><FONT face=3Dsans-serif size=3D1>Sent by: <A=20
        =
href=3D"mailto:ml-errors_at_fatcity.com">ml-errors_at_fatcity.com</A></FONT>=20
        <P><FONT face=3Dsans-serif size=3D1>07/16/2003 11:59 AM</FONT> =


<BR><FONT=20

        face=3Dsans-serif size=3D1>Please respond to ORACLE-L</FONT> =


<BR></P>

      <TD><FONT face=3DArial size=3D1>&nbsp; &nbsp; &nbsp; &nbsp; =


</FONT><BR><FONT=20

        face=3Dsans-serif size=3D1>&nbsp; &nbsp; &nbsp; &nbsp; To: =
&nbsp; &nbsp;=20
        &nbsp; &nbsp;Multiple recipients of list ORACLE-L=20
        &lt;ORACLE-L_at_fatcity.com&gt;</FONT> <BR><FONT face=3Dsans-serif=20
        size=3D1>&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp;=20
        &nbsp;</FONT> <BR><FONT face=3Dsans-serif size=3D1>&nbsp; &nbsp; =
&nbsp;=20
        &nbsp; Subject: &nbsp; &nbsp; &nbsp; &nbsp;Re: Transparent =
database=20
        objects after =

merging</FONT></TR></TBODY></TABLE><BR><BR><BR><FONT face=3DArial=20   size=3D2>You can drop or rename CUSTOMERS to, say, CUSTOMERS_A, and = then create=20
  synonym CUSTOMERS pointing to CUSTOMERS_B. Or rename CUSTOMERS_B to=20   CUSTOMERS.</FONT> <BR><FONT face=3DArial size=3D3> </FONT> =
<BR><FONT=20

  face=3DArial size=3D2>HTH.</FONT> <BR><FONT face=3DArial = size=3D3> </FONT>=20
  <BR><FONT face=3DArial size=3D2>Arup Nanda</FONT> <BR><FONT = face=3DArial=20

  size=3D3>----- Original Message ----- </FONT><BR><FONT face=3DArial=20
  size=3D3><B>From:</B> </FONT><A=20
  href=3D"mailto:Ukrit.Kuprasit_at_allianz.com.sg"><FONT face=3DArial =

color=3Dblue=20
  size=3D3><U>Ukrit.Kuprasit_at_allianz.com.sg</U></FONT></A><FONT = face=3DArial size=3D3>=20
  </FONT><BR><FONT face=3DArial size=3D3><B>To:</B> </FONT><A=20   href=3D"mailto:ORACLE-L_at_fatcity.com"><FONT face=3DArial color=3Dblue=20   size=3D3><U>Multiple recipients of list ORACLE-L</U></FONT></A><FONT = face=3DArial=20
  size=3D3> </FONT><BR><FONT face=3DArial size=3D3><B>Sent:</B> Tuesday, = July 15, 2003=20
  11:29 PM</FONT> <BR><FONT face=3DArial size=3D3><B>Subject:</B> = Transparent=20
  database objects after merging</FONT> <BR><BR><FONT face=3D"Times New = Roman"=20
  size=3D3><BR>Hello Gurus,</FONT><FONT face=3DArial size=3D3> =
</FONT><FONT=20

  face=3D"Times New Roman" size=3D3><BR>       I'm = working=20
  merging 2 databases which have sane structure of  database = objects into=20
  one database. However, after merging, the business policies and = regulations=20
  force us to have 2 set of database objects; for example : <BR>Database = A has=20
  CUSTOMERS table.</FONT><FONT face=3DArial size=3D3> </FONT><FONT=20   face=3D"Times New Roman" size=3D3><BR>Database B has CUSTOMERS = table.</FONT><FONT=20
  face=3DArial size=3D3> <BR></FONT><FONT face=3D"Times New Roman" = size=3D3><BR>After=20
  merging database A will have CUSTOMERS and CUSTOMERS_B (data from = database B).=20
  I just wonder that is there any way that we don't need to change our = script in=20
  forms and reports to point into a new table name but still can access = to the=20
  data in the new table ( CUSTOMERS_B) not CUSTOMERS.</FONT><FONT = face=3DArial=20
  size=3D3> </FONT><FONT face=3D"Times New Roman" size=3D3><BR>Thank you = for any=20
  comment and suggestion.</FONT><FONT face=3DArial size=3D3> = Received on Tue Jul 15 2003 - 22:47:23 CDT