Issue with IN OUT VARCHAR2 formal parameter

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Issue with IN OUT VARCHAR2 formal parameter

Postby Sampath » Thu Sep 20, 2012 3:55 pm

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.
Sampath
Member
 
Posts: 39
Joined: Wed Aug 12, 2009 3:40 pm

Re: Issue with IN OUT VARCHAR2 formal parameter

Postby Tim... » Thu Sep 20, 2012 5:18 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17952
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest