CODEWITHSUNDEEP
sqlstored-proceduresplsql
Enthusiastic
Enthusiastic

Reputation: 549

How to increase buffer size of OUT parameter

For my procedure, I'm getting an error

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

below is the sample code for my stored procedure. How to increase buffer size for out_id paramter

create or replace
PROCEDURE TEST_SRVC(in_id IN VARCHAR2,   in_name IN VARCHAR2, OUT_Id OUT VARCHAR)

AS

--------------
-----------

END;

I tried increasing the buffer by changing signature to:

create or replace
PROCEDURE TEST_SRVC(in_id IN VARCHAR2,   in_name IN VARCHAR2, OUT_Id OUT VARCHAR(32767))

but this is resulting in compilation error

Upvotes: 1

Views: 1534

Answers (2)

Enthusiastic
Enthusiastic

Reputation: 549

Copying "Bob Jarvis's comment as an answer"

The size of the OUT parameter is dictated by the caller, not by the calling routine. The only way to "increase the size" is to have the caller pass a longer VARCHAR variable. So it's the calling routine's problem to deal with.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You cannot increase the buffer size of the OUT parameter. The OUT parameter receives the size of the local variable to hold the data and value is assigned from that variable to OUT parameter.

ie, your OUT_Id is an OUT parameter and it will have the same memory which you will assign it from the local variable(which would be assigning the value to it.)

Upvotes: 2

Related Questions