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

From: Phil Powell <soazine_at_erols.com>
Date: 9 Aug 2002 12:08:38 -0700
Message-ID: <1cdca2a7.0208091108.742959f6@posting.google.com>

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 Received on Fri Aug 09 2002 - 14:08:38 CDT