Re: Problem with Oracle Stored Procedure - will not update INT col fields

Phil Powell wrote:

> I am unable to update two integer null column fields using my written
> stored procedure. I can insert into the null column fields with a
> value (or leave blank and it sets to null, no problem), however, upon
> updating that existing row the column fields not only are not updated
> but their values are erased and replaced with NULL each time.
>
> Attached is the stored proc:
>
> CREATE OR REPLACE PROCEDURE SQLUPDATEBOOKSTORE2
> /********************************************************************
> Name: sqlUpdateBookstore2
> Function: Update a row in tblBookstore.
> Created: 08/09/2002 By: Phil Powell
> Changed:
> ********************************************************************/
> (
> intBookstoreID IN INTEGER,
> strBookstoreName IN VARCHAR2,
> strBookstoreAddress1 IN VARCHAR2,
> strBookstoreAddress2 IN VARCHAR2,
> strBookstoreCity IN VARCHAR2,
> intStateProvinceID IN INTEGER,
> strBookstoreZip IN VARCHAR2,
> intCountryID IN INTEGER,
> strBookstoreLink IN VARCHAR2,
> strBookstoreLinkText IN VARCHAR2,
> strBookstorePhone IN VARCHAR2,
> strBookstoreFax IN VARCHAR2,
> strBookstoreEmail IN VARCHAR2,
> strBookstoreContactName IN VARCHAR2
> )
>
> AS
>
> myIntStateProvinceID INTEGER := NULL;
> myIntCountryID INTEGER := NULL;
>
> BEGIN
> /****************************************************************
> Get the State and Country IDs
> ****************************************************************/
>
> IF sqlUpdateBookstore2.intStateProvinceID != 0 THEN
> myIntStateProvinceID := sqlUpdateBookstore2.intStateProvinceID;
> END IF;
>
> IF sqlUpdateBookstore2.intCountryID != 0 THEN
> myIntCountryID := sqlUpdateBookstore2.intCountryID;
> END IF;
>
> /****************************************************************
> Update Bookstore row
> ****************************************************************/
> UPDATE tblBookstore
> SET strBookstoreName =

<snip>

>
> intStateProvinceID = myIntStateProvinceID,
> strBookstoreZip = TRIM(sqlUpdateBookstore2.strBookstoreZip),
> intCountryID = myIntCountryID,
>

<snip>

> WHERE intBookstoreID = sqlUpdateBookstore2.intBookstoreID;
> COMMIT;
>
> EXCEPTION
> /****************************************************************
> Rollback the transaction and reraise the error
> ****************************************************************/
> WHEN OTHERS THEN
> ROLLBACK;
>
> sqlInsertError(
> strErrorSource => 'sqlUpdateBookstore2',
> intErrorNumber => SQLCODE,
> strErrorMessage => SQLERRM,
> strErrorComments => 'intBookstoreID = "' || intBookstoreID
> ||'" ' ||
> 'strBookstoreName = "' || strBookstoreName ||'" ' ||
> 'strBookstoreAddress1 = "' || strBookstoreAddress1
> ||'" ' ||
> 'strBookstoreAddress2 = "' || strBookstoreAddress2
> ||'" ' ||
> 'strBookstoreCity = "' || strBookstoreCity ||'" ' ||
> 'intStateProvinceID = "' || intStateProvinceID ||'" '
> ||
> 'strBookstoreZip = "' || strBookstoreZip ||'" ' ||
> 'intCountryID = "' || intCountryID ||'" ' ||
> 'strBookstoreLink = "' || strBookstoreLink ||'" ' ||
> 'strBookstoreLinkText = "' || strBookstoreLinkText
> ||'" ' ||
> 'strBookstorePhone = "' || strBookstorePhone ||'" '
> ||
> 'strBookstoreFax = "' || strBookstoreFax ||'" ' ||
> 'strBookstoreEmail = "' || strBookstoreEmail ||'" '
> ||
> 'strBookstoreContactName = "' ||
> strBookstoreContactName ||'" '
> );
>
> RAISE;
> END;
>
> Thanx
> Phil

I'm assuming that the procedure completed without generating an error message, since you didn't mention you were getting one.

I would first ensure that my local variables were in fact being set correctly, by inserting some trace statements just before the update:

dbms_output.put_line ('myIntStateProvinceID=[' || to_char(myIntStateProvinceID) ); dbms_output.put_line ('myIntCountryID=[' || to_char(myIntCountryID) );

If they are having the expected values, but the committed update results in nulls in the two target columns, then I'd check for any ON UPDATE DML triggers on that table. If there is none, then I can't think why it's not working.

A general comment on your code: you are updating without ensuring that the row locks are available. If any matching rows are locked by other sessions, then your code will hang (without error message) until all the locks are available, or forever, whichever comes first. This is bad.

I *NEVER* code an update statement in a pl/sql block without first issuing a SELECT ... FOR UPDATE NOWAIT (with appropriate exception handling e.g pragma exception_init (-54, p_my_exception) or whatever that damn syntax is to catch the 'row lock not available' error. I put this SELECT statement into a cursor, and then issue separate UPDATEs row by row, using the WHERE CURRENT OF <cursor> clause. This technique could probably be optimized using array processing, with which I am not familiar (my knowledge is frozen at the Oracle 7.3 level).

hth
Martin