Procedure call in an update query

[bheemsen]

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

 

[SantaMufasa]

Bheem,

You cannot "UPDATE...SET...dlc_type = <some procedure>..."; you can only "UPDATE...SET...dlc_type = <some function>...".

If you need help determining what to do from here, please advise us here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.