Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Error: ORA-06502: PL/SQL: numeric or value error: host bind array too small

    Hello,

    I am getting this error when i am trying to execute a PL/SQL script using an Oracle API
    We are using Oracle 10g release 2 database. I am getting this error only on some instances. This script is running fine in Development instances.
    Script:


    SET VERIFY OFF;
    SET serveroutput on size 100000;

    SET linesize 300;


    EXEC FND_STATS.GATHER_COLUMN_STATS ('AR', 'HZ_LOCATIONS', 'COUNTRY');

    --PROMPT Dropping Table pmr13286_temptable
    DROP TABLE pmr13286_temptable;



    CREATE TABLE pmr13286_temptable AS (SELECT
    hp.attribute_category , hl.address_lines_phonetic,
    hps.party_site_number,hca.cust_account_id,
    hcsua.site_use_code, hcsua.primary_flag, hcsua.status,
    hl.address1, hl.address2, hl.address3, hl.address4,
    hl.city, hl.state, hl.postal_code, hl.country,
    hl.county, hl.province, hcasa.org_id,
    hl.creation_date
    FROM hz_cust_site_uses_all hcsua,
    hz_cust_acct_sites_all hcasa,
    hz_party_sites hps, apps.hz_locations hl,
    hz_parties hp,
    hz_cust_accounts hca
    WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hps.location_id = hl.location_id
    AND hp.party_id = hps.party_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND hca.party_id = hp.party_id
    AND hl.country NOT IN ('US','CA')
    AND hps.party_site_number NOT LIKE 'M%');




    DECLARE

    l_location_rec hz_location_v2pub.location_rec_type;
    l_object_version_number hz_locations.object_version_number%TYPE;
    p_status_code NUMBER;
    l_api_status VARCHAR2 (2000);
    l_msg_count NUMBER (15);
    l_msg_data VARCHAR2 (2000);
    v_total_count NUMBER (15);
    v_prvi_count NUMBER (15);
    v_countr_count NUMBER (15);
    v_null_count NUMBER (15);
    v_rem_recs NUMBER (15);
    v_status_code VARCHAR2 (2000);
    l_sql_errmsg VARCHAR2 (500);







    CURSOR c_cust_addr
    IS
    SELECT
    hl.*
    FROM hz_cust_site_uses_all hcsua,
    hz_cust_acct_sites_all hcasa,
    hz_party_sites hps, apps.hz_locations hl,
    hz_parties hp,
    hz_cust_accounts hca
    WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hps.location_id = hl.location_id
    AND hp.party_id = hps.party_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND hca.party_id = hp.party_id
    AND hl.country NOT IN ('US','CA')
    AND hps.party_site_number NOT LIKE 'M%';

    BEGIN

    v_total_count := 0;
    v_prvi_count := 0;
    v_countr_count := 0;
    v_null_count := 0;


    dbms_output.put_line('Total Count'||v_total_count );
    dbms_output.put_line('PR/VI Conversion Count'||v_prvi_count );
    dbms_output.put_line('PR/VI to US Conversion Count'||v_countr_count );
    dbms_output.put_line('Non US/CA State Null Count'||v_null_count );

    FOR i IN c_cust_addr
    LOOP
    l_location_rec := NULL;
    v_total_count := v_total_count + 1;
    l_location_rec.location_id := i.location_id;
    l_location_rec.state := i.state;
    l_location_rec.country := i.country;

    dbms_output.put_line('Location_id '||l_location_rec.location_id||' '||'State '||l_location_rec.state||' '||'Country '||l_location_rec.country);





    BEGIN
    SELECT hl.object_version_number
    INTO l_object_version_number
    FROM hz_locations hl
    WHERE hl.location_id = l_location_rec.location_id;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN

    raise_application_error (
    -20059,
    'object_version_number not found for location = '
    || TO_CHAR (l_location_rec.location_id)
    );
    END;
    /* ************************************************** ************************************************** ***********
    Update 1 - Update Addresses of 3 records that have wrong country names
    ************************************************** ************************************************** *********** */
    BEGIN
    IF (l_location_rec.location_id IN (1436720,119456,604649)) THEN
    dbms_output.put_line(' 3 records Location_id '||l_location_rec.location_id);
    IF ( l_location_rec.location_id = 1436720) THEN
    l_location_rec.state := 'OH';
    l_location_rec.country := 'US';
    ELSIF ( l_location_rec.location_id = 119456) THEN
    l_location_rec.country := 'US';
    ELSIF ( l_location_rec.location_id = 604649) THEN
    l_location_rec.country := 'PK';
    END IF;
    hz_location_v2pub.update_location (
    fnd_api.g_true,
    l_location_rec,
    l_object_version_number,
    l_api_status,
    l_msg_count,
    l_msg_data
    );
    IF l_api_status <> fnd_api.g_ret_sts_success
    THEN
    dbms_output.put_line(' Error In API with status of '||l_api_status);

    IF l_msg_count > 1
    THEN
    FOR s IN 1 .. l_msg_count
    LOOP
    l_sql_errmsg :=
    SUBSTR (
    RTRIM (l_sql_errmsg)
    || ' , '
    || RTRIM (
    fnd_msg_pub.get (p_encoded => fnd_api.g_false)
    ),
    1,
    500
    );
    END LOOP;
    ELSE
    l_sql_errmsg := SUBSTR (l_msg_data, 1, 500);
    END IF;
    dbms_output.put_line(' Error Message '||l_sql_errmsg);
    ROLLBACK;
    RETURN;
    END IF;



    END IF;
    END;

    /* ************************************************** ************************************************** **************
    Update 2 - Update state to 'VI','PR' where country is in 'VI','PR'
    ************************************************** ************************************************** ************** */

    BEGIN


    IF (i.country = 'PR' AND (i.state <> 'PR' OR i.state IS NULL))
    OR (i.country = 'VI' AND (i.state <> 'VI'OR i.state IS NULL)) THEN
    v_prvi_count := v_prvi_count + 1;
    dbms_output.put_line(' State to PR/VI Location_id '||l_location_rec.location_id);
    SELECT DECODE (i.country,'PR','PR','VI', 'VI') INTO l_location_rec.state
    FROM apps.hz_locations WHERE
    location_id = l_location_rec.location_id;

    hz_location_v2pub.update_location (
    fnd_api.g_true,
    l_location_rec,
    l_object_version_number,
    l_api_status,
    l_msg_count,
    l_msg_data
    );

    IF l_api_status <> fnd_api.g_ret_sts_success
    THEN
    dbms_output.put_line(' Error In API with status of '||l_api_status);

    IF l_msg_count > 1
    THEN
    FOR s IN 1 .. l_msg_count
    LOOP
    l_sql_errmsg :=
    SUBSTR (
    RTRIM (l_sql_errmsg)
    || ' , '
    || RTRIM (
    fnd_msg_pub.get (p_encoded => fnd_api.g_false)
    ),
    1,
    500
    );
    END LOOP;
    ELSE
    l_sql_errmsg := SUBSTR (l_msg_data, 1, 500);
    END IF;
    dbms_output.put_line(' Error Message '||l_sql_errmsg);
    ROLLBACK;
    RETURN;
    END IF;

    dbms_output.put_line('Location_id '||l_location_rec.location_id||' '||'State '||l_location_rec.state||' '||'Country '||l_location_rec.country);
    END IF;
    END;

    /* ************************************************** ************************************************** *************
    Update 3 - Update Country code to 'US' where state code is either 'PR' or 'VI'
    ************************************************** ************************************************** ************* */


    BEGIN

    IF l_location_rec.state = 'PR' OR l_location_rec.state = 'VI'
    THEN l_location_rec.country := 'US';
    v_countr_count := v_countr_count + 1;
    dbms_output.put_line('PR/VI country to US Location_id '||l_location_rec.location_id);
    hz_location_v2pub.update_location (
    fnd_api.g_true,
    l_location_rec,
    l_object_version_number,
    l_api_status,
    l_msg_count,
    l_msg_data
    );

    IF l_api_status <> fnd_api.g_ret_sts_success
    THEN
    dbms_output.put_line(' Error In API with status of '||l_api_status);

    IF l_msg_count > 1
    THEN
    FOR s IN 1 .. l_msg_count
    LOOP
    l_sql_errmsg :=
    SUBSTR (
    RTRIM (l_sql_errmsg)
    || ' , '
    || RTRIM (
    fnd_msg_pub.get (p_encoded => fnd_api.g_false)
    ),
    1,
    500
    );
    END LOOP;
    ELSE
    l_sql_errmsg := SUBSTR (l_msg_data, 1, 500);
    END IF;
    dbms_output.put_line(' Error Message '||l_sql_errmsg);
    ROLLBACK;
    RETURN;
    END IF;

    dbms_output.put_line('Location_id '||l_location_rec.location_id||' '||'State '||l_location_rec.state||' '||'Country '||l_location_rec.country);
    END IF;
    END;



    /* ************************************************** ************************************************** ****************
    Update 4 - Update state code to NULL where state code is NOT NULL and country code is not in US or CA
    ************************************************** ************************************************** **************** */

    IF l_location_rec.country NOT IN ('US','CA') AND l_location_rec.state IS NOT null
    THEN l_location_rec.state := FND_API.G_NULL_CHAR;
    v_null_count := v_null_count + 1;
    dbms_output.put_line('Non US/CA to Null Location_id '||l_location_rec.location_id);
    hz_location_v2pub.update_location (
    fnd_api.g_true,
    l_location_rec,
    l_object_version_number,
    l_api_status,
    l_msg_count,
    l_msg_data
    );

    IF l_api_status <> fnd_api.g_ret_sts_success
    THEN
    dbms_output.put_line(' Error In API with status of '||l_api_status);

    IF l_msg_count > 1
    THEN
    FOR s IN 1 .. l_msg_count
    LOOP
    l_sql_errmsg :=
    SUBSTR (
    RTRIM (l_sql_errmsg)
    || ' , '
    || RTRIM (
    fnd_msg_pub.get (p_encoded => fnd_api.g_false)
    ),
    1,
    500
    );
    END LOOP;
    ELSE
    l_sql_errmsg := SUBSTR (l_msg_data, 1, 500);
    END IF;
    dbms_output.put_line(' Error Message '||l_sql_errmsg);
    ROLLBACK;
    RETURN;
    END IF;


    dbms_output.put_line('Location_id '||l_location_rec.location_id||' '||'State '||l_location_rec.state||' '||'Country '||l_location_rec.country);
    END IF;



    END LOOP;
    v_countr_count := v_countr_count +2;
    dbms_output.put_line('Total Count'||v_total_count );
    dbms_output.put_line('PR/VI Conversion Count'||v_prvi_count );
    dbms_output.put_line('PR/VI to US Conversion Count'||v_countr_count );
    dbms_output.put_line('Non US/CA State Null Count'||v_null_count );


    BEGIN

    SELECT
    count(*) into v_rem_recs
    FROM hz_cust_site_uses_all hcsua,
    hz_cust_acct_sites_all hcasa,
    hz_party_sites hps, apps.hz_locations hl,
    hz_parties hp,
    hz_cust_accounts hca
    WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hps.location_id = hl.location_id
    AND hp.party_id = hps.party_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND hca.party_id = hp.party_id
    AND hl.country NOT IN ('US','CA')
    AND hps.party_site_number NOT LIKE 'M%';


    dbms_output.put_line('remaining count '||v_rem_recs );
    END;

    COMMIT;

    EXCEPTION
    WHEN OTHERS THEN

    dbms_output.put_line('Error in Program: ' ||' Error Message ' || SQLERRM);
    ROLLBACK;
    END;


    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORA-06502: PL/SQL: numeric or value error: host bind array too small
    This is a data dependent bug.
    This error occurs for 1 of 2 reasons.
    1) assigning a non-numeric value into a number
    2) assigning a string which is longer than the size of the field.

    So the solution is to either change the data or change the field.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking NO help

    + NO complete error messages
    + NO formatted code

    = NO help.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •