Re: Oracle OUT Parameters

On 6 nov, 16:36, m..._at_mtekusa.com wrote:
> 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

Hi John.

I Don't know much about PHP, but I'd try calling your Stored Procedure from a pl/sql script in (say) sql*plus with variables resembling the PHP ones...

I don't like TOAD very much...

HTH. Cheers.

Carlos. Received on Tue Nov 06 2007 - 10:37:25 CST