[lug] collect cumulative datas once in a while from provincial databases

[Balázsik Gábor]

HELLO!

There is a database in the centre. There are x bit provincial databases.
I want collect cumulative datas once in a while from provincial databases.
The name of schemas and name of database links are in a table in the centre.
The plain dynamic stored procedures run, but the each other dynamic stored procedures
with dynamic memory table doesn't work. The Oracle server run to out of memory.
In the tables are only a few rows.

Please send a copy of reply mail to my address, because I'm not on the list!

Good bye! Gábor

Here is the code:

CREATE OR REPLACE PROCEDURE "WW"."PF_VJV"
(
DBLINK IN varchar2,
SEMA IN varchar2,
VALPK IN INTEGER,
VALZZTOTT IN INTEGER
)
AS
I INTEGER;
BEGIN
    SELECT COUNT(VISSZAJELZOXX.VVL_PK)
    INTO I
    FROM VISSZAJELZOXX
    WHERE VISSZAJELZOXX.VVL_VAL_PK_FK = VALPK;

    IF I <> 0 THEN
      UPDATE VISSZAJELZOXX SET VVL_VAL_ZZTOTT = VVL_VAL_ZZTOTT +
VALZZTOTT;
    ELSE
      INSERT INTO VISSZAJELZOXX (VVL_VAL_PK_FK, VVL_VAL_ZZTOTT) VALUES
(VALPK, VALZZTOTT);
    END IF;

   execute immediate 'UPDATE '||SEMA||'.XX@' ||dblink ||' SET VAL_ZZTOTT = 0
WHERE VAL_PK = VALPK';
END;
/


CREATE OR REPLACE PROCEDURE "WW"."P_SZ"
(
DBLINK IN varchar2,
SEMA IN varchar2,
UTSO_TOLTES_DATUMA IN DATE,
KRDPK IN INTEGER,
OSSZES_SZEREPLES OUT INTEGER,
ATLAGIDO OUT INTEGER,
ATLAGPONTSZAM OUT INTEGER,
MEGZZOLATLANOK OUT INTEGER
)
AS
    type snirec is record (IVAL_PK INTEGER, IVAL_ZZTOTT INTEGER);/*REKORD
TÍPUS*/
    type snitab is table of snirec index by binary_integer;/*TÁBLA TÍPUS*/
    v_tab snitab;                                          /*TÁBLA*/
    v_final varchar2(4000);                                /*UTASÍTÁSVÁLTOZÓ*/
    VAR1            INTEGER;
BEGIN
    v_final := 'SELECT COUNT(ktlp_YYEK.KTK_KRD_PK_FK) AS
OSSZES_SZEREPLES,'
    || ' AVG(VNT_FELHASZNALT_IDO) AS ATLAGIDO,'
    || ' AVG(VNT_KAPOTT_PONT) AS ATLAGPONTSZAM'
    || ' FROM '||SEMA||'.ktlpOK@' ||dblink ||' , '||SEMA||'.SSSNAPLOFEJEK@' ||dblink
    || ' , '||SEMA||'.ktlp_YYEK@' ||dblink ||' , '||SEMA||'.SSSNAPLOTETELEK@' ||dblink
    || ' WHERE SSSNAPLOFEJEK.VFEJ_KTL_PK_FK = ktlpOK.KTL_PK'
    || ' AND ktlpOK.KTL_PK = ktlp_YYEK.KTK_KTL_PK_FK'
    || ' AND SSSNAPLOFEJEK.VFEJ_PK = SSSNAPLOTETELEK.VNT_VFEJ_PK_FK'
    || ' AND SSSNAPLOTETELEK.VNT_KTK_PK_FK = ktlp_YYEK.KTK_PK'
    || ' and ktlp_YYEK.KTK_KRD_PK_FK = :1'
    || ' and VFEJ_SSSVEGE IS NOT NULL'
    || ' AND VFEJ_SSSELEJE >= :2';

    execute immediate v_final                      /*ITT KÖZVETLENÜL IS LEHETNE A
STRING*/
    INTO OSSZES_SZEREPLES, ATLAGIDO, ATLAGPONTSZAM
    USING KRDPK, UTSO_TOLTES_DATUMA;

    v_final := 'SELECT VAL_PK, VAL_ZZTOTT'
    || ' FROM '||SEMA||'.XX@' ||dblink
    || ' WHERE XX.VAL_KRD_PK_FK = :1'; /*EZ TÖBB SORT AD*/

    execute immediate v_final bulk collect into v_tab USING KRDPK;

    for i in 1..v_tab.count loop
        PF_VJV(v_tab(i).IVAL_PK, v_tab(i).IVAL_ZZTOTT);
    end loop;

    --KISZÁMOLJUK A MEGVÁLASZOLATLANOKAT
    execute immediate 'SELECT COUNT(ktlp_YYEK.KTK_KRD_PK_FK)'
    || ' FROM '||SEMA||'.SSSNAPLOFEJEK@' ||dblink ||' ,
'||SEMA||'.SSSNAPLOTETELEK@' ||dblink
    || ' , '||SEMA||'.ktlp_YYEK@' ||dblink
    || ' WHERE SSSNAPLOFEJEK.VFEJ_PK =
SSSNAPLOTETELEK.VNT_VFEJ_PK_FK'
    || ' AND SSSNAPLOTETELEK.VNT_KTK_PK_FK = ktlp_YYEK.KTK_PK'
    || ' AND ktlp_YYEK.KTK_KRD_PK_FK = KRDPK'
    || ' AND VFEJ_SSSVEGE IS NOT NULL'
    || ' AND VFEJ_SSSELEJE >= UTSO_TOLTES_DATUMA'
    || ' AND VNT_ZZ1 = ''N'''
    || ' AND VNT_ZZ2 = ''N'''
    || ' AND VNT_ZZ3 = ''N'''
    || ' AND VNT_ZZ4 = ''N'''
    || ' AND VNT_ZZ5 = ''N'''
    INTO MEGZZOLATLANOK;
END;
/

CREATE OR REPLACE PROCEDURE "WW"."P_TOLT_VISSZAJELZOYY"
 (
DBLINK IN varchar2,
SEMA IN varchar2,
UTSO_TOLTES_DATUMA IN DATE,
VKPPK INTEGER
)
AS
    type snirec is record (IKTK_KRD_PK_FK INTEGER);/*REKORD TÍPUS*/
    type snitab is table of snirec index by binary_integer;/*TÁBLA TÍPUS*/
    v_tab snitab;                                          /*TÁBLA*/
    v_final varchar2(4000);                                /*UTASÍTÁSVÁLTOZÓ*/
OSSZES_SZEREPLES INTEGER;
ATLAGIDO INTEGER;
ATLAGPONTSZAM INTEGER;
MEGZZOLATLANOK INTEGER;
BEGIN
  v_final := 'SELECT ktlp_YYEK.KTK_KRD_PK_FK'
    || ' FROM '||SEMA||'.ktlpOK@' ||dblink ||' , '||SEMA||'.SSSNAPLOFEJEK@' ||dblink
    || ' ,'||SEMA||'.ktlp_YYEK@' ||dblink ||' , '||SEMA||'.SSSNAPLOTETELEK@' ||dblink
    || ' WHERE SSSNAPLOFEJEK.VFEJ_PK =
SSSNAPLOTETELEK.VNT_VFEJ_PK_FK'
    || ' AND SSSNAPLOTETELEK.VNT_KTK_PK_FK = ktlp_YYEK.KTK_PK'
    || ' and VFEJ_SSSVEGE IS NOT NULL'
    || ' AND VFEJ_SSSELEJE >= :1';

    execute immediate v_final bulk collect into v_tab USING UTSO_TOLTES_DATUMA;

    for i in 1..v_tab.count loop

P_SZ(DBLINK,SEMA,UTSO_TOLTES_DATUMA,v_tab(i).IKTK_KRD_PK_FK,OSSZES
_SZEREPLES,ATLAGIDO,ATLAGPONTSZAM,MEGZZOLATLANOK);
        INSERT INTO VISSZAJELZOYYEK
        (VKD_VKP_PK_FK, VKD_KRD_PK_FK, VKD_IDOPONT, VKD_H_ZZOLATLAN,
VKD_H_SZEREPELT, VKD_ATL_IDO, VKD_ATL_PONTSZAM ) VALUES
        (VKPPK,   v_tab(i).IKTK_KRD_PK_FK, SYSDATE,   MEGZZOLATLANOK,
OSSZES_SZEREPLES,ATLAGIDO,    ATLAGPONTSZAM);
    end loop;
END;/


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20040304/d17ed4ee/attachment.html>