This discussion is archived
7 Replies Latest reply: Aug 12, 2008 4:28 AM by NoLongerActive RSS

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

432580 Newbie
Currently Being Moderated
I get this error on two function activities in a workflow of mine. I don't even get into the pl/sql procedure call.

The exact error is:
Error Name -6502
Error Message: ORA-0605: PL/SQL: numeric or value error: character string buffer too small
Error Stack: @#@Wf_Engine_Util.Function_Call(MY_PACKAGE.MY_PROC, ITEM_TYPE, ITEM_KEY, 6519, RUN)

If I retry the step (expedite -> retry) it runs until it hits the second function which does the same thing (there is a function in between the two functions that do not have any issues). I then have to retry that step as well. Any ideas?

This seems similar to the following issue that does not have a resolution posted:
ORA-06502 PL/SQL numeric or value error in wf process

I've tried commenting out the bodies of the procedures called but that does not help (again -- im not getting into the bodies but im not sure what else it could be).

Any ideas or suggestions?
  • 1. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    NoLongerActive Pro
    Currently Being Moderated
    Hi,

    Does this happen on every instance of the process, or just a few?

    Are these activities in the middle, or right at the start of the flow?

    Are any other activities being performed correctly first? If so, are these standard or bespoke?

    My inclination would be that there is some kind of variable that is being populated (or concatenated onto), which is growing too big. When the process errors, it gets cleared, which is why an expedite would move the activity on.

    Matt
    --
    WorkflowFAQ.com - the ONLY independent resource for Oracle Workflow development

    Alpha review chapters from my book "Developing With Oracle Workflow" are available via my website http://www.workflowfaq.com
    Have you read the blog at http://thoughts.workflowfaq.com ?
    WorkflowFAQ support forum: http://forum.workflowfaq.com
  • 2. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    432580 Newbie
    Currently Being Moderated
    Thank you for the response, it was happening on two steps in the middle (separated by 3 steps in between).

    The reason was the package had an error (constant had varchar size too small). I still am unclear as of why a retry would work. It funny b/c the two steps that errored were not even using the constant. It would be nice if workflow would give you the line number of the error.

    I hope this helps anyone else that may run into the problem!
  • 3. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    514323 Newbie
    Currently Being Moderated
    I can explain the possible cause for the funny behavior.

    If you have a variable declared as follows.
    create or replace package my_package as
    g_contant varchar2(2) := '123';
    procedure my_procedure
    is
    ..
    This would cause the character string buffer too small error when the package is first loaded into memory. Once it is loaded, the error will not appear until you exit that session and re-load the package in another session.

    As far as displaying the line number is concerned, workflow does not have control over the PLSQL error stack or the line numbers. It is only PLSQL run-time that should give us the line number.
  • 4. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    441141 Newbie
    Currently Being Moderated
    Hi,

    We are facing the similar error.

    Process A calls Process B for each project number.
    Process B has couple of Functions. The first function ex: find_apr_style

    So, the process created on the second project is errored with ORA-06502 PL/SQL numeric or value character string buffer is too small.

    But, when I RETRY it works fine for that entire session. Also worked for other sessions also.

    The same package worked without such errors a week ago.

    If any one has done analysis on this and has solution to prevent this, kindly post in forum for all oracle users.

    Thanks
    Rao
  • 5. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    441141 Newbie
    Currently Being Moderated
    Hi,

    I found the bug and resolved it. As one of our friends mentioned above. This happens because of improper variable lengths and values. We have given a wrong length of variable initialization which is used in the function. Thats the root cause for the error.

    So, when ever you see this error. Go thru the code, check the variables and length. Most of these cases, this is the root cause.

    Thanks
    Rao

    Edited by: raochennai@gmail.com on Jul 6, 2010 7:18 PM
  • 6. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    654571 Newbie
    Currently Being Moderated
    Hi, I have the same error.
    PL/SQL: numeric or value error: character string buffer too small.
    I am using variable of varchar2 type and it's size is max 32767. When i run this procedure and it produce the outbut of small size it works ok but when it produce the output of large size this exception is thrown.

    How can i solve it?
  • 7. Re: ORA-0605: PL/SQL: numeric or value error: character string buffer too s
    NoLongerActive Pro
    Currently Being Moderated
    Hi,

    You need to track back the code to see where the value is being passed around. Somewhere, there will be a variable declared at a smaller size than your 32k variable, which is causing the problem.

    HTH,

    Matt
    --
    WorkflowFAQ.com - the ONLY independent resource for Oracle Workflow development

    Alpha review chapters from my book "Developing With Oracle Workflow" are available via my website http://www.workflowfaq.com
    Have you read the blog at http://www.workflowfaq.com/blog ?
    WorkflowFAQ support forum: http://forum.workflowfaq.com