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 =
> TRIM(sqlUpdateBookstore2.strBookstoreName),
> strBookstoreAddress1 =
> TRIM(sqlUpdateBookstore2.strBookstoreAddress1),
> strBookstoreAddress2 =
> TRIM(sqlUpdateBookstore2.strBookstoreAddress2),
> strBookstoreCity =
> TRIM(sqlUpdateBookstore2.strBookstoreCity),
> intStateProvinceID = myIntStateProvinceID,
> strBookstoreZip = TRIM(sqlUpdateBookstore2.strBookstoreZip),
> intCountryID = myIntCountryID,
> strBookstoreLink =
> TRIM(sqlUpdateBookstore2.strBookstoreLink),
> strBookstoreLinkText =
> TRIM(sqlUpdateBookstore2.strBookstoreLinkText),
> strBookstorePhone =
> TRIM(sqlUpdateBookstore2.strBookstorePhone),
> strBookstoreFax = TRIM(sqlUpdateBookstore2.strBookstoreFax),
> strBookstoreEmail =
> TRIM(sqlUpdateBookstore2.strBookstoreEmail),
> strBookstoreContactName =
> TRIM(sqlUpdateBookstore2.strBookstoreContactName)
> 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 don't have time to build the tables and run the code to find out the error message generated by Oracle. Perhaps others will be willing to invest the time to figure out what you should have told us ... what error message (full number and text) did it generate?

The problem could be as simple as a NOT NULL constraint but you give no clue.

Daniel Morgan Received on Fri Aug 09 2002 - 15:49:23 CDT