Issue with IN OUT VARCHAR2 formal parameter

[Sampath]

Good Morning Tim,

I have a below scenario and we are using Oracle 11g.

We have a IN OUT parameter to a stroed proc with VARCHAR2 data type.

In the stored proc, a query is built dynamically and it is assigned to this IN OUT parameter.But, the size of the query is more than 9000 characters.
When I assign the value to this parameter it retun the below error.

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

Could you please help me out how to overcome this issue as it an high priority.

Please reply with an appropriate example.

It seems, for a locale varible in the proc, we can give a max size of 32767, but it allows only 4000 for formal parameter.

Thanks in advance.

Regards,
Sampath.

[Tim...]

Hi.

For this to work, the variable you pass in as a parameter have to be big enough to hold the data, the parameter itself has to be big enough (assuming you are constraining the size) and any actions inside the procedure have to be done with variables big enough to hold the data.

Do this can accept 32767 characters.

Code: Select all

PROCEDURE my_proc(p1 IN VARCHAR)

But call it like this and you are limited to 5000 characters.

Code: Select all

DECLARE
  l_var VARCHAR2(5000)
BEGIN
  my_proc(l_var);
END;

This would limit the data to 5000 characters.

Code: Select all

PROCEDURE my_proc(p1 IN VARCHAR) AS
  l_var VARCHAR2(5000)
BEGIN
  l_var := p1;
END;

So there are three ways you can make a mistake and limit the amount of characters that can be passed.

Cheers

Tim...