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: Code modularity, seeking suggestions

Re: Code modularity, seeking suggestions

From: <david.archer_at_chase.com>
Date: Fri, 20 Aug 1999 14:31:12 GMT
Message-ID: <7pjorg$qn7$1@nnrp1.deja.com>


Not wanting to get into the details of you query ... have you tried MINUS ? If this works for you it will be considerably faster than the cursors you are working with.

Example:
INSERT INTO Clients_Missing_1
  (
   SELECT *
   FROM Clients_1
   MINUS
   SELECT *
   FROM Clients_2
   )

... then go back the other way.

INSERT INTO Clients_Missing_2
  (
   SELECT *
   FROM Clients_2
   MINUS
   SELECT *
   FROM Clients_1
  )

Happy Coding,

David Archer

In article
<7FD2432186B9CEC5.11DF2E2DC448445B.521862DE68E8B5A1_at_lp.airnews.net>,   hlh_nospam_at_excite.com wrote:
> Here is a package that I cobbled together to generate comparisons of
> two tables with identical structure, which should have identical
> contents, provided the parallel data entry efforts are both the same.
> This is part of a Y2K migration project.
>
> The two procedures in the package, secpsf and hlhpsf, are basically
> mirror images of each other. Each procedure just finds all assy/part
> combinations in one file that are not in the other. Is there a way to
> combine these into a single procedure, to which I pass the cursors and
> tables?
>
> Or, is there a different and better approach to this?
>
> CREATE OR REPLACE PACKAGE BODY Compare IS
> --
> PROCEDURE secpsf
> IS
> CURSOR sec_cur
> IS
> SELECT *
> FROM sec_psf;
> --
> CURSOR hlh_cur(part IN VARCHAR2, assy IN VARCHAR2)
> IS
> SELECT *
> FROM hlh_psf
> WHERE psf_part = part
> AND psf_assy = assy;
> hlh_rec hlh_cur%ROWTYPE;
> --
> BEGIN
> FOR sec_rec IN sec_cur
> LOOP
> OPEN hlh_cur(sec_rec.psf_part, sec_rec.psf_assy);
> FETCH hlh_cur INTO hlh_rec;
> IF hlh_cur%NOTFOUND
> THEN
> INSERT INTO sec_psf_diff
> VALUES
> (
> sec_rec.psf_assy,
> sec_rec.psf_part
> );
> END IF;
> CLOSE hlh_cur;
> END LOOP;
> COMMIT;
> END;
> --
> PROCEDURE hlhpsf
> IS
> CURSOR hlh_cur
> IS
> SELECT *
> FROM hlh_psf;
> --
> CURSOR sec_cur(part IN VARCHAR2, assy IN VARCHAR2)
> IS
> SELECT *
> FROM sec_psf
> WHERE psf_part = part
> AND psf_assy = assy;
> sec_rec hlh_cur%ROWTYPE;
> --
> BEGIN
> FOR hlh_rec IN hlh_cur
> LOOP
> OPEN sec_cur(hlh_rec.psf_part, hlh_rec.psf_assy);
> FETCH sec_cur INTO sec_rec;
> IF sec_cur%NOTFOUND
> THEN
> INSERT INTO hlh_psf_diff
> VALUES
> (
> hlh_rec.psf_assy,
> hlh_rec.psf_part
> );
> END IF;
> CLOSE sec_cur;
> END LOOP;
> COMMIT;
> END;
> END Compare;
>
> hlh_NOSPAM_at_excite.com is a valid, unmunged address!
> It is also so full of spam(!) that I don't read it.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 20 1999 - 09:31:12 CDT

Original text of this message

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