Re: Problem with update inside a procedure

You're updating the value of time_stamp to itself, that's why you're seeing no change. Compare what you have:

   UPDATE TELEMETRY_LATEST set
   TIME_STAMP = time_stamp,
   MEASUREMENT = value
   WHERE PTV_ID=ptvID ;
with, say:

   UPDATE TELEMETRY_LATEST set
   TIME_STAMP = v_time_stamp,
   MEASUREMENT = value
   WHERE PTV_ID=ptvID ;
In other words change the name of the incoming parameter named time_stamp so that SQL can distinguish it from the column of the same name.

Regards

leo_at_6d.com (Leo) wrote in message news:<33ea13eb.0202201140.9ea0c73_at_posting.google.com>...
> I have wrote a simple procedure to update 2 fields in a table. The
> problem is only one field (MEASUREMENT) get updated but the other
> field (TIME_STAMP) is ignored. It works when I manually run the same
> update statement in SQL/PLUS. Any help is appreciated.
>
> -Leo
>
> CREATE OR REPLACE procedure CREATE_TELEMETRY (ptvID IN NUMBER,
> value IN NUMBER, time_stamp IN NUMBER)
> IS
> c NUMBER ;
> BEGIN
> SELECT count(*) INTO c FROM TELEMETRY_LATEST WHERE PTV_ID=ptvID;
>
> INSERT INTO TELEMETRY (PTV_ID, TIME_STAMP, MEASUREMENT)
> VALUES (ptvID, time_stamp, value);
>
> -- update TELEMETRY_LATEST
> IF c > 0 THEN
> UPDATE TELEMETRY_LATEST set
> TIME_STAMP = time_stamp,
> MEASUREMENT = value
> WHERE PTV_ID=ptvID ;
> ELSE
> INSERT INTO TELEMETRY_LATEST (PTV_ID, TIME_STAMP, MEASUREMENT)
> VALUES (ptvID, time_stamp, value);
> END IF;
>
> END CREATE_TELEMETRY;
> /
Received on Wed Feb 20 2002 - 23:06:03 CET