Re: Intermittent ORA-06502: PL/SQL: numeric or value error

jlsports_at_hotmail.com (Joe Lee) wrote in message news:<178b8d98.0206191205.672ead4f_at_posting.google.com>...
> Intermittent ORA-06502: PL/SQL: numeric or value error ORA-06512: at
> line 1.
>
> OS: Windows 2000 Advanced Server 5.0.2195 &#8211; Service Pack 1, Build
> 2195
>
> Database:
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> PL/SQL Release 8.1.6.0.0 - Production
> CORE 8.1.6.0.0 Production
> TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
> Running a Visual Basic batch application that calls a stored
> procedure. The stored procedure executes 2 SQL statements. The first
> is a delete statement that deletes the record with the primary keys
> that will be inserted in the second statement. The second statement
> inserts a single record into a table. The same stored procedure is
> executed multiple times during a batch run. The following error
> occurs when the stored procedure is executed:
> Input Parameters do not have the correct data types as the Stored
> Procedure
> [Oracle][ODBC][Ora]ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at line 1.
> The record that it fails on is similar to the ones that get inserted
> correctly during the batch run. The same record gets inserted without
> error after the database service is restarted. The error has occurred
> intermittently, once last month and twice in the last five days. This
> batch job runs daily every 2 hours. All variable parameters that are
> passed into the stored procedure are in the correct variable type.
> All variable definitions in the stored procedure do not have a defined
> size and are the correct variable type. No data type conversion is
> done in the stored procedure. This stored procedure receives 81
> parameters from the Visual Basic code to get inserted into the table.
> Also, prior to the first failure, the shared pool was increased from
> 41943040 (40 M) to 83886080 (80 M).

"PL/SQL: numeric or value error" does not only mean that you have incorrect datatype. If you try to assign string of length 20 to a VARCHAR2(15), you will get the same error. Looks like you need to scrub your data before inserting it. Maybe there are white spaces padded in some VARCHAR2 values that are not visible. You can use RPAD or LPAD on VARCHAR2 values before inserting.

HTH Received on Wed Jun 19 2002 - 23:38:57 CDT