Re: SQL*Loader and Mutating Table

From: <buckeye714_at_my-deja.com>
Date: Tue, 25 Jan 2000 12:59:15 GMT
Message-ID: <86k6n2$92p$1@nnrp1.deja.com>

In article <948752956.29851.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> The uet$ table is provided by Oracle. The trigger
> EVT.CG$AIS_LTRK_APPLICATIONS is reading from this table, while this
table is
> being updated. The table uet$, which contains the used extents is
> transparently updated by Oracle. IMO, an application program should
NEVER
> refer to it. Post the source of the abovementioned trigger and we will
see
> whether we can do anything about it.
>
> Hth,
>
> --
> Sybrand Bakker, Oracle DBA

Here is the source code. Thanks again for your help.

TRIGGER "EVT".cg$AIS_LTRK_APPLICATIONS
AFTER INSERT ON LTRK_APPLICATIONS
DECLARE
    idx BINARY_INTEGER := cg$LTRK_APPLICATIONS.cg$table.FIRST;     cg$rec cg$LTRK_APPLICATIONS.cg$row_type;     cg$old_rec cg$LTRK_APPLICATIONS.cg$row_type; BEGIN
-- Application_logic Pre-After.Insert.Statement <<Start>> DECLARE
events_loaded BOOLEAN;
uw_loaded BOOLEAN;
exci_loaded BOOLEAN;

BEGIN events_loaded := ltrk.load_events;
uw_loaded := ltrk.load_uw_events;
exci_loaded := ltrk.load_exci_events;

END;
-- Application_logic Pre-After.Insert.Statement << End >>

    IF NOT (cg$LTRK_APPLICATIONS.called_from_package) THEN

        WHILE idx IS NOT NULL LOOP
            cg$rec.APPL_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_ID;
            cg$rec.APPL_LTYP_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_LTYP_ID;
            cg$rec.APPL_CREDIT_REQ_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_CREDIT_REQ_ID;
            cg$rec.APPL_ACCOUNT_NUM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_ACCOUNT_NUM;
            cg$rec.APPL_CURRENT_STATUS :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_CURRENT_STATUS;
            cg$rec.APPL_APPLCNT_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_APPLCNT_NM;
            cg$rec.APPL_APPLCNT2_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_APPLCNT2_NM;
            cg$rec.APPL_INDV_JOINT_TYPE :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_INDV_JOINT_TYPE;
            cg$rec.APPL_PERSONAL_BANKER_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_PERSONAL_BANKER_NM;
            cg$rec.APPL_PERSONAL_BANKER_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_PERSONAL_BANKER_ID;
            cg$rec.APPL_UNDERWRITER_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UNDERWRITER_NM;
            cg$rec.APPL_UNDERWRITER_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UNDERWRITER_ID;
            cg$rec.APPL_UNDERWRITER_LOCATION :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UNDERWRITER_LOCATION;
            cg$rec.APPL_UW_INTERNAL_ZIP :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UW_INTERNAL_ZIP;
            cg$rec.APPL_UW_COMPLETE_IND :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UW_COMPLETE_IND;
            cg$rec.APPL_UW_COMPLETE_DT_TIME :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UW_COMPLETE_DT_TIME;
            cg$rec.APPL_UNDERWRITING_STATUS :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_UNDERWRITING_STATUS;
            cg$rec.APPL_REVIEW_COMPLETE_IND :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_REVIEW_COMPLETE_IND;
            cg$rec.APPL_REVIEW_COMPLETE_DT_TIME :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_REVIEW_COMPLETE_DT_TIME;
            cg$rec.APPL_REVIEW_STATUS :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_REVIEW_STATUS;
            cg$rec.APPL_REVIEWED_BY_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_REVIEWED_BY_NM;
            cg$rec.APPL_REVIEWED_BY_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_REVIEWED_BY_ID;
            cg$rec.APPL_CLOSED_BY_NM :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_CLOSED_BY_NM;
            cg$rec.APPL_CLOSED_BY_ID :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_CLOSED_BY_ID;
            cg$rec.APPL_LOAN_DT :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_LOAN_DT;
            cg$rec.APPL_LOAN_AMT :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_LOAN_AMT;
            cg$rec.APPL_START_DT_TIME :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_START_DT_TIME;
            cg$rec.APPL_ORIGIN_SOURCE_CODE :=
cg$LTRK_APPLICATIONS.cg$table(idx).APPL_ORIGIN_SOURCE_CODE;
            cg$rec.APPL_ORIG_CONDITION_TYPE :=

cg$LTRK_APPLICATIONS.cg$table(idx).APPL_ORIG_CONDITION_TYPE;

            cg$LTRK_APPLICATIONS.upd_oper_denorm(cg$rec,
                                              cg$old_rec,

cg$LTRK_APPLICATIONS.cg$tableind(idx),
                                              'INS'
                                             );

            idx := cg$LTRK_APPLICATIONS.cg$table.NEXT(idx);
        END LOOP;

    END IF;

• Application_logic Post-After.Insert.Statement <<Start>>
• Application_logic Post-After.Insert.Statement << End >>

END; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jan 25 2000 - 06:59:15 CST