Routine AFPGRQ encountered an ORACLE error. ORA-01455: converting column overflows integer datatype

Issue:

Standard Managers not coming up. All other managers are coming up and are running fine.

Details:

Our standard managers were not coming up where as other managers were up and running. The standard manager log file had the following error logged:

Routine AFPGRQ encountered an ORACLE error. ORA-01455: converting column overflows integer datatype

Troubleshooting:

In our instance, we had 10 standard managers defined as part of the default workshift. Also, the packages used by AFPGRQ routine were the correct ones and in sync with the appl_top. So no reason to suspect any changes there.

However, there was one request pending on Standard Managers while viewing the "Administer Concurrent Managers" form. From the Administer Concurrent Managers form, queried Standard Managers and in that screen, clicked on the "Requests" button. The form did not show the details of the concurrent request.

So changed focus to fnd_concurrent_requests table and here is what I found:

sql> select request_id,phase_code,status_code from fnd_Concurrent_requests where phase_code='P';

This query returned 108 rows of which one was having the status_code "I".

So, narrowing down, here is what further investigation revealed:

SQL> select request_id,phase_code,status_code,requested_by,CONCURRENT_PROGRAM_ID from fnd_Concurrent_requests where request_id=1012680;

REQUEST_ID P S REQUESTED_BY CONCURRENT_PROGRAM_ID

---------- - - ------------ ---------------------

   1012680 P I   3.4535E+14                101330

Here, REQUESTED_BY column should be a valid id (number) which represents the user who submitted the request.

As you can see, some junk value (large integer value) was entered  which was causing the concurrent managers to crash. Now, this phenonmenon may not be unique to Standard Manager and can happen with any manager which is assigned to pick up such erroneous requests.

Querying this requests from the "Run Requests" form also would not yield any result as the form was unable to handle this condition.

Now, to overcome the issue, I ran the following update:

SQL> update fnd_concurrent_Requests set Phase_Code='C', status_Code='E', requested_by=1145 where request_id=1012680;

SQL> commit;

The requested_by id 1145 is a valid user from fnd_user table as identified by user_id column.

Then, from the administer managers screen, clicked on restart for standard managers and the managers came up clean.

Further you can identify the program in the following way:

select concurrent_program_id from fnd_concurrent_requests where request_id=1012680 ;

select user_concurrent_program_name from fnd_concurrent_programs_tl where concurrent_program_id=101330;

From this you can identify a set of users who ran this program in the past and inform them to be careful while submitting the requests programatically.

- Aravind Kamath Posral