Re: VARCHAR2 Parameter Question

On Sep 26, 8:31 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> <a..._at_unsu.com> schreef in berichtnews:7a557046-61c2-4ddd-947c-fd00b9c82a46_at_2g2000hsn.googlegroups.com...
> On Sep 26, 1:15 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>
>
>
> > <a..._at_unsu.com> schreef in
> > berichtnews:f7df28cf-e38a-4e40-bee3-b55860c238a1_at_m73g2000hsh.googlegroups.com...
>
> > > Hi,
>
> > > Does a VARCHAR2 input parameter to a stored procedure have a max
> > > length? I pass a 465 byte string to a procedure receiving it as a
> > > varchar2 and it gives some error of the string byte too small or
> > > something.
>
> > > I cut it down to 195 and it works fine. I bring it up in TOAD to
> > > debug it and the parameter shows a value of 200.
>
> > > Why is that? I thought the max value was 4000.
>
> > John, art, arthur, artmerar, Mtek, exec:
>
> > No version, no code..
>
> > Could be something INSIDE the procedure (does it assign the parameter to a
> > variable?)
>
> > Shakespeare
>
> It is a simple procedure like this:
>
> PROCEDURE add_customer_info (
>   p_customer_id  NUMBER,
>   p_portfolio_id NUMBER,
>   p_tickers      VARCHAR2);
>
> That varchar2 seems to be limited to 200 characters.
>
> ============================================
> Not here. Just created your proc as
> create or replace PROCEDURE add_customer_info (
>   p_customer_id  NUMBER,
>   p_portfolio_id NUMBER,
>   p_tickers      VARCHAR2)
> is
> begin
>   null;
> end;
>
> and called it with a 210 and 420 length string. No problems.
> So without the code and/or version there's no help possible
>
> Shakespeare
>
> BANNER
> Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
> PL/SQL Release 10.1.0.5.0 - Production
> "CORE 10.1.0.5.0 Production"
> TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
> NLSRTL Version 10.1.0.5.0 - Production

Just for fun,

CREATE OR REPLACE PROCEDURE test_length

    ( p_param VARCHAR2 )
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Length of p_param is ' || LENGTH(p_param)); END test_length;
/

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 27 18:52:26 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serverout on
SQL> exec test_length(rpad('x',10000,'x')) Length of p_param is 10000

PL/SQL procedure successfully completed.

SQL>
SQL> exec test_length(rpad('x', POWER(2,15),'x')) BEGIN test_length(rpad('x', POWER(2,15),'x')); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

SQL> exec test_length(rpad('x', POWER(2,15) -1,'x')) Length of p_param is 32767

PL/SQL procedure successfully completed. Received on Sat Sep 27 2008 - 12:58:20 CDT