Release 3.00
May 27, 2006
|
Sybernet / Supplied Procedures Reference
Release 3.00 May 27, 2006 |
|
This procedure allows you to compare two tables. A unique index is required so that this program can determine rows that are different as well as rows that are absent between the two. The compare is done between remote databases and not between schemas. Since this program runs under the context of the current user, database links must either be public or owned by you. You of course will require select permission to do the compare and if you wish to synchronize rows that are different, you must also have delete and insert permission. To run the compare offline only public database links are allowed.
You can search for tables and indicies. When searching for table names, the search is literal and case-insensitve. When searching for indicies, the display includes only those indexes that are unique for this table. In general most tables have no unique index (which is not allowed) or one unique index (which is allowed). If a table happens to have more than one unique index, this option allows you to pick the one you want to use.
The output includes rows in the source database that do not exist in the destination database. This is followed by rows in the destination database that do not exist in the source database. This is followed by rows that exist in both databases but are different in one or more columns. Columns that are different have a column heading displayed in red. Columns that are part of the unique index are underlined.
If you have both delete and insert permission on these tables, you can select the link under the database column to synch both rows. The problem here is you can click the wrong link and cause both tables to be wrong. You will, however, be prompted with the actual SQL statements before this is done so at least you have a chance to confirm this request before it is commited. If you think it's of any help, the rule is you click the database link that you want to synch from the other database.
As of this writing (and don't expect it to change anytime soon) only the following column data types are allowed: char, date, float, number, varchar2, and long.
Procedure HTTP.SP_HTML_DIFF
(
BUTTON => NULL -- VARCHAR2 (IN)
, OWNER => NULL -- VARCHAR2 (IN)
, NAME => NULL -- VARCHAR2 (IN)
, INDEX_NAME => NULL -- VARCHAR2 (IN)
, SRCESERVER => NULL -- VARCHAR2 (IN)
, DESTSERVER => NULL -- VARCHAR2 (IN)
, DEBUG => NULL -- PL/SQL BOOLEAN (IN)
, VERBOSE => NULL -- PL/SQL BOOLEAN (IN)
, OFFLINE => NULL -- VARCHAR2 (IN)
) ;
| Parameter | Description |
|---|---|
owner |
The owner of this table. |
name |
The name of this table. |
index_name |
The name of a unique index for this table. |
srceserver |
The db_link for the source database. |
destserver |
The db_link for the destination database. |
debug |
When TRUE this option allows you to view the anonymous block that is created by this program. |
verbose |
When TRUE this option determines whether you are running this interactively or when FALSE via a script. |
offline |
Determines if this is to be run interactively (FALSE/USER) or via Sybercron (TRUE/PUBLIC). FLASE and TRUE is what is displayed in interactive mode. USER and PUBLIC is what is actually passed to this procedure. |
The preferred method is to run this application interactively by selecting it from the list of applications under Sybernet Utilities. It's called Sybernet Table Compare. It can also be run in batch mode in case you have several tables to compare.
The following example illustrates how to call SP_HTML_DIFF in batch mode:
DECLARE
PROCEDURE COMPARE(NAME IN VARCHAR2)
IS
BEGIN
HTTP.SP_HTML_DIFF
(
BUTTON => 'REPORT'
, OWNER => 'OWNER'
, NAME => NAME
, INDEX_NAME => NAME
, SRCESERVER => 'SRCESERVER'
, DESTSERVER => 'DESTSERVER'
, DEBUG => FALSE
, VERBOSE => FALSE
) ;
HTTP.WRITELN('<hr size=1 width="100%">');
HTTP.WRITELN('<br>');
HTTP.WRITELN('<br>');
RETURN;
END COMPARE;
BEGIN
HTTP.WRITELN('<html>');
HTTP.SP_HTML_PREFERENCES;
COMPARE('PS_EARNINGS_TBL');
COMPARE('PS_DEDUCTION_TBL');
COMPARE('PS_DEDUCTION_CLASS');
COMPARE('PS_DEDUCTION_FREQ');
COMPARE('PS_BEN_DEFN_PGM');
COMPARE('PS_BEN_DEFN_PLAN');
COMPARE('PS_BEN_DEFN_OPTN');
COMPARE('PS_BEN_DEFN_COST');
HTTP.WRITELN('</body>');
HTTP.WRITELN('</html>');
END;