1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by krushnasahoo, Apr 17, 2013.

  1. krushnasahoo

    krushnasahoo Guest

    I have written a procedure to fetch data from DB and append all to a variable. procedure like below.
    ROCEDURE get_Data (
    p_firstname VARCHAR2,
    p_middlename VARCHAR2,
    p_lastname VARCHAR2,
    p_outputdata OUT VARCHAR2,

    IS

    h_firstname VARCHAR2 (255) := TRIM (p_firstname);
    h_middlename VARCHAR2 (255) := TRIM (p_middlename);
    h_lastname VARCHAR2 (255) := TRIM (p_lastname);

    CURSOR gsa_ind_cur
    IS
    SELECT a1.*, LEAST (score (1) * score_markup, 99) AS score_output
    FROM gsa_exclusionary_list a1
    WHERE contains (a1.namestring, fuzzy_info, 1) > p_score
    --contains (a1.crossreference, fuzzy_info, 1) > p_score
    --AND istestdata = p_istest
    ORDER BY score_output DESC;


    v_err_num NUMBER;
    v_err_msg VARCHAR2 (100);
    BEGIN

    IF h_firstname IS NOT NULL
    THEN
    OPEN gsa_ind_cur;
    LOOP
    FETCH gsa_ind_cur INTO gsa_ind_rec;

    IF gsa_ind_cur%NOTFOUND
    THEN
    IF fetch_cnt = 0
    THEN
    RAISE NO_DATA_FOUND;
    ELSE
    EXIT;
    END IF;
    END IF;

    p_outputdata := p_outputdata
    || '<_DETAIL _Score="'
    || gsa_ind_rec.score_output
    || '" ';
    --appending all the column like this.
    EXIT WHEN gsa_ind_cur%NOTFOUND;
    END LOOP;

    CLOSE gsa_ind_cur;
    END IF;

    EXCEPTION

    WHEN OTHERS
    THEN
    h_search_result := 'Error';
    v_err_num := SQLCODE;
    v_err_msg := SUBSTR (SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE('Unknown Exception Raised: '||sqlcode||' '||sqlerrm);

    END IF
    END ;

    Its fetching total 11 rows. It throws error if length(p_outputdata) aprox 10K. What is the cause for this error. Error is mentioned below.
    "ORA-06502: PL/SQL: numeric or value error"
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi,

    p_outputdata is a VARCHAR2 datatype. it can hold max 4K only. If you are trying to assign more than 4K data it will give " ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error.

    If you require to work with character data greater than 4K, use the CLOB data type and the DBMS_LOB package.

    Regards
    Sambasiva Reddy.
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Oracle provides the ability in both SQL and PL/SQL to work with the VARCHAR2 data type. However, they differ in their maximum allowed length. Within PL/SQL, local variables of type VARCHAR2 can be declared to contain up to 32,767 characters while declaring a database column of type VARCHAR2 can contain up to 4000 characters.
    This SQL VARCHAR2 limit of 4000 characters exists even when VARCHAR2 values appear inside DML statements like SELECT, INSERT, UPDATE and DELETE.

    When referencing PL/SQL functions inside DML statements, ensure the return value does not exceed 4000 characters.