ORA-06502: PL/SQL: numeric or value error: character string buffer too

[Glowworm27]

I have a package with the following procedure in it.

I am searching for an existing record. If I find the record I will return the SPiff_ID (record id) if Not I wish to return a -1 but when I try to execute the statement from my ASP.net page I recieve the error.

here is the procedure

PROCEDURE GetSpiffTrackingID (
	oUniversalID In Number,
	oStoreNbr In Number,
	oWeekEndingDate In Date,
	oSPiffID Out Number
	) IS
	
	  BEGIN -- executable part starts here
		--Search for a matching Spiff tracking ID if Found return the ID else return a -1
		select Spiff_Id into oSpiffID from SPiff_tracking
		Where Universal_ID = oUniversalID
		and StoreNbr = oStoreNbr
		and Week_Ending_Date = oWeekEndingDate;
			
      
     EXCEPTION -- exception-handling part starts here
     WHEN No_Data_Found THEN
		oSpiffID := -1;

    END GetSpiffTrackingID;

Thanks in advance.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SantaMufasa]

ORA-06502: PL/SQL: numeric or value error: character string buffer too...

Sorry, George, the Tek-Tips subject line is also apparently too...

Could you please post a copy and paste of the exact message that ASP.net gives, along with the definitions of any IN/OUT arguments involved?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Glowworm27]

ORA-06502: PL/SQL: numeric or value error: character string buffer too Small

the asp.net output param....

Dim iret as int32
... Code left out for brevity ...
arParms(6) = New OracleParameter("oRetSpiffTrackingID", OracleDbType.Long, ParameterDirection.Output)

            myCon = OpenConnection(pOracleUserID, pOraclePwd, pOracleDatasource)
        Catch ex As Exception
            Throw ex
        End Try

        Try
            ExecuteNonQuery(myCon, CommandType.StoredProcedure, "SpiffDetail.InsertSpiffTracking", arParms)
            iRet = arParms(6).Value
            Return iRet

I figured it out apparently it does not like to return the ID back to a OracleDbType.Long parameter, I changed it to a OracleDbType.Double parameter and now it works. ???

Anyway thanks for your time.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net