Shipment & Booking History Collections Fails ORA-06502 (Doc ID 1209633.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Demantra Demand Management - Version: 7.2.0.2 and later   [Release: 7.2 and later ]
Information in this document applies to any platform.

Symptoms


Shipment and Booking history Data collections program fails
The Log file shows the following:

Log File:

**Starts**03-MAR-2010 03:12:00

**Ends**03-MAR-2010 03:12:01

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

Collecting Locations



Step 1: Cleaning up staging tables for Locations



Step 2: Populating Temporary Tables Locations



Step 3: Populating Demantra Staging Tables Locations



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

+---------------------------------------------------------------------------+

End of log messages from FND_FILE



Debug Output file:

Start Time: 03-MAR-2010 03:21:35

End Time: 03-MAR-2010 03:21:35

Step 3: Populating Demantra Staging Tables Locations



In procedure populate_demantra_staging

In procedure: get_query

The instance dblink for this query will be: @APS_TO_EBS

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

************************************************************

As a workaround the customer did the following to bypass the error:


Oracle Database package: MSD_DEM_COLLECT_LEVEL_TYPES

procedure populate_demantra_staging(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_instance_id IN NUMBER,
p_collect_level_type IN NUMBER,
p_plan_id IN NUMBER)

as

--l_stmt varchar2(12000);
l_stmt varchar2(32000);


Customer modified the size of local variable l_stmt from 12000 to 32000 to accommodate a bigger sql.


### Steps to Reproduce ###
Create Custom queries to extract data from EBS.
Make sure that custom query is greater than 12000 chars.

Run Shipment and Booking History data collections for Demantra.
Collections programs will fail with error message:

Step 3: Populating Demantra Staging Tables Locations

In procedure populate_demantra_staging

In procedure: get_query

The instance dblink for this query will be: @APS_TO_EBS

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


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms