Re: All fields in UPDATE statement not updated (PL/SQL procedure).

"don" <chambers_at_inquiregroup.com> wrote in message news:g0i4bt8fth6nfmfsnnio86ae9sf2s23ihp_at_4ax.com...
> I have a procedure (not a stored proc) in a trigger that performs an
> update but all columns in the update statement are not updated. This
> procedure is part on an Instead Of Insert trigger on a view.
>
> The procedure is below::
> -----------
> PROCEDURE updateBody(theSDId IN NUMBER, docTypeID IN NUMBER,
> statusID IN NUMBER,contactEmployeeID IN NUMBER)
> IS
> BEGIN
> UPDATE SourceDocuments
> SET
> DocTypeID=docTypeID,
> statusID=statusID,
> Title=:new.Title,
> AmendmentNumber=:new.AmendmentNumber,
> contactEmpID=contactEmployeeID,
> WHERE sourceDocumentID=theSDId;
> END updateBody;
> -----------
> and I call it as follows:
> updateBody(theSdID,13,statusID,contactEmployeeID);
>
> The columns DocTypeID and statusID do not get updated - I do not get
> an error. As you get see I have hard-coded the DocTypeID and it still
> does not work. I can throw a user defined exception just before the
> update and include a message with the docTypeID and I see that it is
> the ID I passed (13 in this case).
> The update leaves just those two fields unchaged. - it does not set
> them to null or the wrong value. The update is processed because the
> other fields are updated.
>
> What could be causing this?
>

Scoping...
The name of your variable is identical to the name of the column. You should *never* do this, and always prefix your parameter names with p_ in order to avoid confusion.

Regards,

Sybrand Bakker, Oracle DBA Received on Fri Mar 16 2001 - 18:58:20 CET