Re: Oracle OUT Parameters

On Nov 6, 4:10 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> m..._at_mtekusa.com 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