-1

I get an Oracle error ORA-6502 Character string buffer too small in my code at the statement below

EXECUTE IMMEDIATE 'BEGIN :project_id :=   
               Activity_API.Get_Project_Id(:activity_seq); END;'
USING OUT project_id_, activity_seq_

project_id_ - this is a local variable in the function
activity_seq_ -- this is an IN parameter to the function.

I don't understand the cause of the error. Besides, the error is not consistently showing up.

Please help me know what am I missing out. Thanks.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
user4507518
  • 71
  • 1
  • 7
  • Some `VARCHAR` variable is been assigned with a string of length greater than it's defined size. Depends upon the data only. – Maheswaran Ravisankar Jan 29 '15 at 14:44
  • What is the full error stack - is the error from that assignment, or from within the function you're calling? What size is `project_id_` declared as in your function? What's the maximum possible length of value that the function can return? – Alex Poole Jan 29 '15 at 15:12
  • I have checked the size of the project_id_ variable. It is the same as the one returned by the Get_Project_ID function here. Besides there is no size issue even within the Get_Project_id function. Besides, the error is thrown from within the outer function from this very line where the execute immediate is called. As I said, the error is not consistent so it makes it even more difficult to understand the real cause of the error. – user4507518 Jan 29 '15 at 15:19
  • If the error is coming from that line, and the stack doesn't refer to the function, then the function is returning a value that is longer than you expect. Try to isolate an `activity_seq_` value that causes the error; or make your variable large and check it after it's fetched. We can't see your function or your data so you need to debug it. – Alex Poole Jan 29 '15 at 16:37

1 Answers1

1

Generally this error means that you have a VARCHAR(N) variable somewhere in your code and you tried to assign VARCHAR(N+x) value to it. It may happens anywhere, say:

  • size of activity_seq_ is too big for function local variable
  • size of project_id_ is too small for function result
  • there is some oversized value used into the function itself
  • etc.

Sometimes it may happens because of multibyte character set used, say, if value is VARCHAR(N chars) while assignment target is VARCHAR(n bytes). Anyway, you should just debug it. Use PL/SQL Developer or any other tool which can trace stored procedures row by row, run your statement into the test window and see what happens, where and why.

Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28