A way to pass :NEW to proc in a trigger?

[Taoism]

Hi all,

This is using Oracle 8i. I need to do a bulk load every week or so where the data may or not may not be new (so UPDATE or INSERT). I have had this working in the past with an INSTEAD OF trigger (and indeed, if I move the code from the proc listed below into the body of the trigger the trigger works fine).

What I am trying to figure out is why the code below doesn't work, and if there is a way to make it work? The basis of my question and desire to use a proc call instead of running the code directly is that I read that the trigger must go through a "soft parse" for every row loaded, whereas the proc will only be "soft parsed" once, the first time it is run for the session. Is there no way to pass the :NEW values into the proc?

I have a trigger that I would like to run like so:

CREATE OR REPLACE TRIGGER upd_scc_inv_stab_yy_nsr
INSTEAD OF INSERT ON v_scc_inv_stab_yy_nsr
FOR EACH ROW
BEGIN
  scc_trg_procs.trg_v_scc_inv_stab_yy_nsr(:NEW.inv_code,
                                          :NEW.stab_yy,
                                          :NEW.prov_code,
                                          :NEW.strct_grp_code,
                                          :NEW.fmv_val,
                                          :NEW.alwble_fmv_diff_pct);
END;
/

The proc in the package looks like this:

  PROCEDURE trg_v_scc_inv_stab_yy_nsr(p_inv_code            IN scc_inv_stab_yy_nsr.inv_code%TYPE,
                                      p_stab_yy             IN scc_inv_stab_yy_nsr.stab_yy%TYPE,
                                      p_prov_code           IN scc_inv_stab_yy_nsr.prov_code%TYPE,
                                      p_strct_grp_code      IN scc_inv_stab_yy_nsr.strct_grp_code%TYPE,
                                      p_fmv_val             IN scc_inv_stab_yy_nsr.fmv_val%TYPE,
                                      p_alwble_fmv_diff_pct IN scc_inv_stab_yy_nsr.alwble_fmv_diff_pct%TYPE) IS
  BEGIN
    UPDATE scc_inv_stab_yy_nsr
       SET strct_grp_code      = p_strct_grp_code,
           fmv_val             = p_fmv_val,
           alwble_fmv_diff_pct = p_alwble_fmv_diff_pct
     WHERE inv_code  = p_inv_code
       AND stab_yy   = p_stab_yy
       AND prov_code = p_prov_code;
    IF SQL%ROWCOUNT = 0 THEN
      BEGIN
        INSERT
          INTO scc_inv_stab_yy_nsr
               (inv_code,
                stab_yy,
                prov_code,
                strct_grp_code,
                fmv_val,
                alwble_fmv_diff_pct)
        VALUES (p_inv_code,
                p_stab_yy,
                p_prov_code,
                p_strct_grp_code,
                p_fmv_val,
                p_alwble_fmv_diff_pct);
      END;
    END IF;
  END trg_v_scc_inv_stab_yy_nsr;

Cheers,
Keith.

[TimHall]

Hi.

Have you tried to output/trace the values of the :NEW parameters after they've entered the procedure? What do they contain?