Re: Oracle OUT Parameters

From: <mtek_at_mtekusa.com>
Date: Tue, 06 Nov 2007 07:36:57 -0800
Message-ID: <1194363417.095448.68820@50g2000hsm.googlegroups.com>

On Nov 6, 9:27 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On 6 nov, 14:51, 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.
>
> > Does anyone know why it would limit to 200 characters? It is very
> > frustrating.
>
> > John
>
> Show us the error message, the code of the procedure and calling
> routine and maybe we can help you...
>
> BTW which debugger are you using?
>
> Cheers.
>
> Carlos

I am using the debugger in TOAD. However, when I run the procedure from the PHP code, and print messages to a file, I get the same results.

The error message is basically: String buffer too small.

The calling line from PHP looks like this:

$query = "begin ".
$g_array['FOR_SCR'].".awi_report_php('$p_site_id', :P_I, :P_O, :P_V, :P_COL, :data, :p_str, :p_save_out, '', '', ''); end;";

  $stmt = ociParse($conn ,$query) or die ('Can not parse query');

  OCIBindByName($stmt, ':P_I', $pi_array, 32,OCI_B_NTY) or die ('Can not bind 1');
  OCIBindByName($stmt, ':P_O', $po_array, 32,OCI_B_NTY) or die ('Can not bind 2');
  OCIBindByName($stmt, ':P_V', $pv_array, 32,OCI_B_NTY) or die ('Can not bind 3');
  OCIBindByName($stmt, ':P_COL', $pcol_array, 32,OCI_B_NTY) or die ('Can not bind 4');

  ocibindbyname($stmt, ':data' ,&$curs, -1,OCI_B_CURSOR);
  OCIBindByName($stmt, ':p_str',&$p_str, 1024);
  OCIBindByName($stmt, ':p_save_out',&$p_save_out, 1024);

The parameter declaration in the package procedure looks like this:

PROCEDURE awi_report_php (

   p_site_id      NUMBER,
   p_i            IN OUT IN_STR_ARR,
   p_o            IN OUT IN_STR_ARR,
   p_v            IN OUT IN_STR_ARR,
   p_columns      IN OUT IN_STR_ARR,
   line       OUT TEST_CURSOR,
   p_save_head      OUT VARCHAR2,
   p_save_out OUT VARCHAR2,
   p_save_in      VARCHAR2  DEFAULT NULL,
   p_comp_id      VARCHAR2  DEFAULT NULL,

   p_customer_id VARCHAR2 DEFAULT NULL) IS

And, the IN_STR_ARR type looks like this:

TYPE "IN_STR_ARR" AS VARRAY (500) OF VARCHAR2(4000) Is there anything else you might need? I am watching the variable in TOAD. When the variable p_save_head basically reaches 1000 characters, it says "<Value too Large>". Then is basically aborts.......and the PHP code fails since the query is incomplete.

Thanks again Carlos.

John Received on Tue Nov 06 2007 - 09:36:57 CST