Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    62

    Question Unanswered: Procedure call in update statement

    I am trying to use a procedure call in an update query, But it is not working. The main procedure is not getting compiled. I am on oracle 9i.

    The statement is
    UPDATE --+INDEX(BPT_TO_DLM BPT_TO_DLM_INDEX)
    bpt_to_dlm
    SET rmt_trml_ind = NULL,
    rmt_trml_avail_ind = NULL,
    rmt_trml_clli_cd = NULL,
    dlc_type = spGetDlcType(dlc_type, vDlcType)
    WHERE wtn IN (SELECT m.wtn
    FROM rp_wtn n,
    bpt_to_dlm m,
    web_user.web_ref_direct_fed@WEB p
    WHERE TO_NUMBER(m.wtn) = n.wtn
    AND m.wire_cntr_cd = n.wire_cntr_cd
    AND m.taper_cd = n.taper_cd
    AND m.wire_cntr_cd = p.wire_cntr_cd
    AND m.taper_cd = p.taper_cd
    AND n.da_cd = p.da_cd
    AND m.return_code = 203
    AND LENGTH(p.override_dt) IS NULL);

    The inline procedure spGetDlcType is below:

    PROCEDURE spGetDlcType(vdlc IN bpt_to_dlm.dlc_type%TYPE,
    vDlcType OUT bpt_to_dlm.dlc_type%TYPE)
    IS
    BEGIN
    IF (vdlc = 'T124/148' OR vdlc = 'T124D' OR vdlc = 'T148D')) THEN
    vDlcType := NULL ;
    ELSE
    vDlcType := vdlc;
    END IF;
    END;

    The update statement and inline procdure both are inside another main procedure.

    Can someone help me how to fix this problem.

    -Bheem

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Procedures can not be used in this context; instead of the procedure, create a FUNCTION which would look like this:
    Code:
    CREATE OR REPLACE FUNCTION spgetdlctype (vdlc IN bpt_to_dlm.dlc_type%TYPE)
       RETURN bpt_to_dlm.dlc_type%TYPE
    IS
       vdlctype   bpt_to_dlm.dlc_type%TYPE;
    BEGIN
       IF (vdlc = 'T124/148' OR vdlc = 'T124D' OR vdlc = 'T148D')
       THEN
          vdlctype := NULL;
       ELSE
          vdlctype := vdlc;
       END IF;
    
       RETURN (vdlctype);
    END;
    Update statement would then be
    Code:
    UPDATE bpt_to_dlm SET
      dlc_type = spGetDlcType(dlc_type)
    WHERE ...
    If you insist on using procedure instead of a function, consider enclosing the UPDATE statement into a PL/SQL block; you would have to declare a variable which would store value of the procedure's OUT parameter and update table column value with parameter's value. Personally, I'd rather use a function.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Seeing as your function is so simple I would suggest scrapping the function and putting a case statement directly in your update statement, it will be much faster.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •