This forum is currently locked. You can't register or post questions at this time. (read more)

Issue with IN OUT VARCHAR2 formal parameter

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Sampath
Member
Posts: 39
Joined: Wed Aug 12, 2009 3:40 pm

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.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron