LANG_UPDTAB Package, 23-Mar-98
Copyright © 1998, Mark Lang
(Requires Oracle 7.3,8.0 or greater)


LANG_UPDTAB is a package that contains one procedure: UPDATE_TABLE.
This extremely useful procedure is a generic utility to copy data
from one table to another, much like the SQL*Plus COPY command, with
some important differences:

	Table structures do not have to be identical
	Can insert new rows and update existing rows, and even update
	  only those rows that have changed
	Compare tables by row and/or column
	Exclude specific columns not to insert and/or update
	Warns you of differences in column type, size, nullness
	Print execution report of all actions
	Allow set COMMITPOINT for large loads
	Works with remote tables
	Reports progress via DBMS_APPLICATION_INFO
	...and much, much more

Here is a simple example which copies rows from OLD_EMPLOYEES
into EMPLOYEES which do not already exist:

	lang_updtab.update_table(
	dtab=>'EMPLOYEES'
	, stab=>'EMPLOYEES_STAGE'
	, flags=>'I'
	);

	The "I" flag tells UPDATE_TABLE to insert NEW rows from
	OLD_EMPLOYEES into the EMPLOYEE table.

	You could easily modify the behavior of the update by
	adding some other common flags (there are 20 available)

	U=update existing rows with same primary key (determined
	  by procedure; you can also override)
	C=update only those existing rows which have changed
	  (reduces unnecessary redo)
	R=work in REPORT-ONLY mode (no changes made)

	L=SPOOL generated code to DBMS_OUTPUT!!!
	J=submit as a job

	E=echo errors to screen
	W=show warning of column differences in type, size, nullness

	There are also 19 PARAMETERS available (including the
	3 above: STAB, DTAB, and FLAGS) to further customize
	behavior.


All this can be done with a single procedure call.  UPDATE_TABLE
dynamically generates and executes an anonymous PL/SQL routine
to perform the update based on your parameters.  You can even
have UPDATE_TABLE spool the code it generates to DBMS_OUTPUT
where you can view or modify it.

This procedure has a variety of applications for DBA's and
developers.  Anyone who has ever had to write a large INSERT INTO
(...) SELECT ... FROM, or similar UPDATE statement will know
what I'm talking about.  Below lists just some of the most common
uses:

	Move / copy data when...
	  Table structures are different
	  Want to update existing rows
	  Need to load large amounts of data (COMMITPOINT)
	  Take advantage of any other UPDATE_TABLE features
	Load external data via stage tables loaded by SQL*Loader
	Compare data in two tables by row or column
	Support drop_column and other table maintenance operations
	Refresh TEST / DEVELOPMENT databases
	Maintain read-only, primary-key "snapshots"
	Use to generate template code for more complex updates

UPDATE_TABLE is completely dynamic and generic.  You may set
up to 19 parameters and 20 flags to customize the behavior of the
procedure, making it extremely flexible and usuable in a great
deal of situations.  It also contains complete (13+ pages in
Micorsoft Word 95 or HTML formats) documentation and all the
source code.  Hopefully, future versions of the package will
contain other helpful routines for managing table data.

Enjoy!

The latest version of any packages can be downloaded from

www.geocities.com/SiliconValley/Lakes/1261

See readme.txt for license terms.


Mark Lang
mlang@phoenixgroup.com