Why update SQL in procedure did not commit?

From: C Chang <cschang_at_maxinter.net>
Date: Thu, 05 Dec 2002 00:15:06 -0500
Message-ID: <3DEEE0DA.5B4B@maxinter.net>

I have an ASP page calling a procedure of a package to update records. The procedure has structure like following

PROCEDURE UpdateOrder

      (p_orderID   IN  table.column%TYPE,
       ..
       p_errFlag   OUT NUMBER,
       p_advMsg    OUT VARCHAR2) IS

   ..
BEGIN
   ..
  UPDATE requests_view
  SET loc_id = p_locID,

          remarks = p_remarks
  WHERE order_id = p_orderID;

  SELECT error_msg
  INTO v_advMsg
  FROM requests ;    

  p_advMsg:= v_advMsg;
  ..

EXCEPTION
 ...
END UpdateOrder;

In turn the UPDATE requests_view triggers a trigger doing the actual Updating a new error_msg. Even I received the updated message as p_advMsg on web page, when I query the same orderID again, the record still retains the pre-updated error msg. I have to put in the recObj.CommitTrans from ASP to commit, where recObj is the recordSet object of ASP. Is that when a procedure finish it task at database side, all the SQL schemes are supposed to be committed? My database is 8i on NT 4 box. Can anyone explain why?

C Chang Received on Wed Dec 04 2002 - 23:15:06 CST