Hi,
I have several tables in a tablespace.
Every "real" (used) table has a "clone" table in another tablespace with
the same field structure.
What I have to do periodically, with example:
Real Table:
cNum cName
--------------------------
1 First
2 Second
3 Third
4 Forth
5 Fifth
Clone Table:
cNum cName
--------------------------
1 First
2 Second
4 Forth
5 Fifth
6 Sixth
1.Delete all rows that are in "real" tables that are not in the "clone" tables
( delete Third)
2.Update the "real" tables with the rows of the "clone" tables
( update First,Second,Forth,Fifth)
3.Insert into "real" tables the valus that are in "clone" tables but not
in "real" tables
( insert Sixth)
Unfortunatelly I can't use Truncate and then "Insert Into RealTable(Select
* from CloneTable);"
due to database charteristics ( there are tables that overlappes each other) so
I have to use Update to do this.
For there are many "real" and "clone" tables I'd like to make an efficient
procedure that can do the
whole thing.
Can you send me some sample code how to make the update?
I have tried to make Cursors with 'for update' but it seems too rigid for me:
1. in Update statement I have to fill the Set colums e.g. I can't use Set
RealTable.* = 'values'
2.
If I have a cursor:
CURSOR MyCursor IS
SELECT * FROM RealTable,CloneTable
Where RealTable.Column1 = CloneTable.Column1
FOR UPDATE ;
I get the error: PLS-00402 alias required in SELECT list of cursor to avoid
duplicate column names.
That's correct since the two tables has the same structure.
Thank you for your help
Zsolt Csillag,
Hungary
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Csillag Zsolt
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
