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

On 6 nov, 14:51, m...@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

On Nov 6, 9:27 am, Carlos <miotromailcar...@netscape.net> wrote:
> On 6 nov, 14:51, m...@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, _str, _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, '_str',&$p_str, 1024);
OCIBindByName($stmt, '_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

On 6 nov, 16:36, m...@mtekusa.com wrote:
> On Nov 6, 9:27 am, Carlos <miotromailcar...@netscape.net> wrote:
>
>
>
>
>
>
>
> > On 6 nov, 14:51, m...@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, _str, _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, '_str',&$p_str, 1024);
> OCIBindByName($stmt, '_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.

mtek@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, '_str',&$p_str, 1024); and
OCIBindByName($stmt, '_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...

On Nov 6, 4:10 pm, Frank van Bortel <frank.van.bor...@gmail.com>
wrote:
> m...@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, '_str',&$p_str, 1024); and
> OCIBindByName($stmt, '_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.