Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle OUT Parameters

Re: Oracle OUT Parameters

From: <>
Date: Thu, 22 Nov 2007 04:44:58 -0800 (PST)
Message-ID: <>

On Nov 6, 4:10 pm, Frank van Bortel <> wrote:
> wrote:
> > Hello Everyone,
> > We are running Oracle 8.1.7. I have a package procedure which has
> > some OUT parameters. The procedure however is not working. After a
> > very detailed investigation, bringing the procedure through a
> > debugger, I found the problem.
> > One of the parameters that is being passed back to the calling
> > application, which is a PHP script, is more than 1000 characters
> > long. The OUT parameter is defined as a VARCHAR2. However, only 200
> > characters are being returned. When I move my mouse over that
> > variable in the debugger, it shows VARCHAR2(200).
> > I was under the impression that the limitation of the OUT parameter
> > was somewhere in the 32,000 range.
> No - that would be internal to Oracle - passing varchars to
> other packaged procedures.
> To external interfaces, it is 4000 characters (more precise:
> 4000 byte - multi byte characters may bite you!)
> > Does anyone know why it would limit to 200 characters? It is very
> > frustrating.
> there is an option to define those before you start the debugging
> process.
> In an other response, you claim: "When the variable
> p_save_head basically reaches 1000 characters, it says
> "<Value too Large>".
> what does OCIBindByName($stmt, ':p_str',&$p_str, 1024); and
> OCIBindByName($stmt, ':p_save_out',&$p_save_out, 1024); do?
> Are you sure it reaches 1000 characters, not 1024? Are you
> sure it is characters, not bytes (1000 char may be > 1024 byte)?
> --
> Regards,
> Frank van Bortel
> Top-posting is one way to shut me up...- Hide quoted text -
> - Show quoted text -

I am eager to know if there has been a resolution to this problem? I am experiencing something very similar, only I am directly using the stored proc step through in TOAD, so the calling app is not a suspect. Whenever the out parameter is populated with a longer value, it is giving the error. Even though the out parameter is typed with a column, and the column type is confirmed as Varchar2(2000).

I haven't precisely nailed down the threshold length for causing it to fail, but if I populate the out parameter with a 100 character-long string, it is fine, then if I use a 300 character-long string it blows up.

If I define another variable (that is not an OUT parameter) as a Varchar2(2000) then I can assign values to that variable just fine, it's the assignment to the OUT parameter that seems to be causing the error.

The specific error I'm getting is
"ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SCHEMA.PKG_NAME", line 99 ORA-06512: at line 11 ."

Any insights appreciated. Received on Thu Nov 22 2007 - 06:44:58 CST

Original text of this message