Results 1 to 6 of 6

Thread: very urgent

  1. #1
    Join Date
    Aug 2004
    Location
    Rome, Italy
    Posts
    81

    Unanswered: very urgent

    Hi everybody,
    I have a function that returns a string that can be more than 5000 characters long. When the string is more then 4000 characters long I have an error. I declared it like this:

    Function hello
    (param1,param2)
    return varchar2 is

    my_string varchar2(6000) default null;

    begin
    expressions;

    return my_string;

    exception
    when other then
    RETURN 'ERRORE'||to_char(sqlcode)||': '||substr(sqlerrm,1,500) ;
    end;

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

    I have it very urgent. Any help would be apriciated.
    Thank you in advance,
    ducasio

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    did you try debugging?

    my guess is that you are getting a string larger than 6k,
    what is the max out value for a varchar2?
    Did you even look it up?

    PHP Code:
      1  create or replace procedure t_proc as
      
    2  x varchar2(6000);
      
    3  v varchar2(6000);
      
    4  begin
      5  
    for x in 1 .. 6000 loop
      6    v 
    := v||'         '||x;
      
    7    if length(v) > 5950 then
      8      dbms_output
    .put_line (x);
      
    9      dbms_output.put_line (to_char(length(v)));
     
    10    end if;
     
    11  end loop;
     
    12end;
    system@sgoldbat> /

    Procedure created.

    system@sgoldbatexec  t_proc;
    505
    5952
    506
    5964
    507
    5976
    508
    5988
    509
    6000
    BEGIN t_proc
    END;

    *
    ERROR at line 1:
    ORA-06502PL/SQLnumeric or value error
    ORA
    -06512at "SYSTEM.T_PROC"line 6
    ORA
    -06512at line 1 
    Last edited by The_Duck; 12-20-04 at 15:52.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    please post the exact code and how exactly you are calling the function and
    your exact error message. all cut/paste
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Native varchar2 sql datatype has a maximum limit of 4000 bytes, consider using a clob instead.

  5. #5
    Join Date
    Aug 2004
    Location
    Rome, Italy
    Posts
    81
    the example code is simple. I have this function that if returns a string with more than 4000 characters long, gives an error:

    FUNCTION MY_FUNCTION(
    PARAM1 IN VARCHAR2
    )
    RETURN VARCHAR2
    IS
    L_STR VARCHAR2(10000) DEFAULT NULL;
    BEGIN
    FOR I IN 1..801
    LOOP
    L_STR := L_STR || 'HELLO';
    END LOOP;
    RETURN L_STR ;

    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'ERROR:'||TO_CHAR(SQLCODE) ;
    END;

    I use this function like this:
    SELECT MY_FUNCTION(MY_PARAM) FROM DUAL;

    But I think that martines is right, maybe we can't use a string with more than 4000 character, as a string return in a function. I don't know how to do it.?????????

  6. #6
    Join Date
    Aug 2004
    Location
    Rome, Italy
    Posts
    81
    the example code is simple. I have this function that if returns a string with more than 4000 characters long, gives an error:

    FUNCTION MY_FUNCTION(
    PARAM1 IN VARCHAR2
    )
    RETURN VARCHAR2
    IS
    L_STR VARCHAR2(10000) DEFAULT NULL;
    BEGIN
    FOR I IN 1..801
    LOOP
    L_STR := L_STR || 'HELLO';
    END LOOP;
    RETURN L_STR ;

    EXCEPTION
    WHEN OTHERS THEN
    RETURN 'ERROR:'||TO_CHAR(SQLCODE) ;
    END;

    I use this function like this:
    SELECT MY_FUNCTION(MY_PARAM) FROM DUAL;

    But I think that martines is right, maybe we can't use a string with more than 4000 character, as a string return in a function. I don't know how to do it.?????????

Posting Permissions

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