Prev: VARCHAR2 Length
Next: "Business Logic / Rules should never be in the database or storedprocedures"
From: The Magnet on 11 Dec 2009 13:58
This one is kind of confusing. The max length of a VARCHAR2 is
4000. So, I call a stored procedure passing is a value that is 3278
characters long, basically text. I receive an error:

SP2-0027: Input is too long (> 2499 characters)

If I code that into a variable inside some code, same thing.

Breaking it up is not really an option since it is being called some a
PHP application. But, if 4000 is the max, why is it complaining at
3000????
From: joel garry on 11 Dec 2009 14:09
On Dec 11, 10:58 am, The Magnet <a...(a)unsu.com> wrote:
> This one is kind of confusing.   The max length of a VARCHAR2 is
> 4000.  So, I call a stored procedure passing is a value that is 3278
> characters long, basically text.  I receive an error:
>
> SP2-0027: Input is too long (> 2499 characters)
>
> If I code that into a variable inside some code, same thing.
>
> Breaking it up is not really an option since it is being called some a
> PHP application.  But, if 4000 is the max, why is it complaining at
> 3000????

Documented sqlplus command line limit:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apa.htm#i635278

Note you can find stuff like this in MOS, simply by searching the
knowledge base for the error number.

Does kinda point up that you need to understand the whole tech stack -
the clue that it is sqlplus is the SP2 in the error message.

jg
--
@home.com is bogus.
http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudoku-using-recursive-subquery-factoring
From: The Magnet on 11 Dec 2009 14:19
On Dec 11, 1:09 pm, joel garry <joel-ga...(a)home.com> wrote:
> On Dec 11, 10:58 am, The Magnet <a...(a)unsu.com> wrote:
>
> > This one is kind of confusing.   The max length of a VARCHAR2 is
> > 4000.  So, I call a stored procedure passing is a value that is 3278
> > characters long, basically text.  I receive an error:
>
> > SP2-0027: Input is too long (> 2499 characters)
>
> > If I code that into a variable inside some code, same thing.
>
> > Breaking it up is not really an option since it is being called some a
> > PHP application.  But, if 4000 is the max, why is it complaining at
> > 3000????
>
> Documented sqlplus command line limit:http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apa.ht...
>
> Note you can find stuff like this in MOS, simply by searching the
> knowledge base for the error number.
>
> Does kinda point up that you need to understand the whole tech stack -
> the clue that it is sqlplus is the SP2 in the error message.
>
> jg
> --
> @home.com is bogus.http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudo...


Does not make sense, unless I am confusing 2 things.

I have plenty of stored procedure where say I create a dynamic query
which is like 3500 characters long and there is no problem there.

Why is this an issue? Is it because it is getting "passed" into the
stored procedure?

From: Malcolm Dew-Jones on 11 Dec 2009 13:20
The Magnet (art(a)unsu.com) wrote:
: This one is kind of confusing. The max length of a VARCHAR2 is
: 4000. So, I call a stored procedure passing is a value that is 3278
: characters long, basically text. I receive an error:

: SP2-0027: Input is too long (> 2499 characters)

: If I code that into a variable inside some code, same thing.

: Breaking it up is not really an option since it is being called some a
: PHP application. But, if 4000 is the max, why is it complaining at
: 3000????

--
I would google this error, there are lots of hits on it. Note the error
is not an oracle error per-se (ORA-) but something else.

I assume that php uses some kind of interface to get to oracle, it is
possible that the maximum is something defined in that. One googlee was
using sqlplus and their line was too long for that tool (and they got that
error), so I might guess something similar for you with php.

Also, perhaps the php code is sending multiple bytes per character
(utf-8). Then the number of characters and bytes will not be the same, so
(for example, these are not your numbers) a 2000 character string will be
greater than 2000 bytes (and so not fit into a varchar2(2000).

Also max length of a VARCHAR2 is not 4000 except as a column in the
database, in pl/sql it is much longer, as just discussed in another
thread.


From: ddf on 11 Dec 2009 14:27
On Dec 11, 2:19 pm, The Magnet <a...(a)unsu.com> wrote:
> On Dec 11, 1:09 pm, joel garry <joel-ga...(a)home.com> wrote:
>
>
>
>
>
> > On Dec 11, 10:58 am, The Magnet <a...(a)unsu.com> wrote:
>
> > > This one is kind of confusing.   The max length of a VARCHAR2 is
> > > 4000.  So, I call a stored procedure passing is a value that is 3278
> > > characters long, basically text.  I receive an error:
>
> > > SP2-0027: Input is too long (> 2499 characters)
>
> > > If I code that into a variable inside some code, same thing.
>
> > > Breaking it up is not really an option since it is being called some a
> > > PHP application.  But, if 4000 is the max, why is it complaining at
> > > 3000????
>
> > Documented sqlplus command line limit:http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apa.ht...
>
> > Note you can find stuff like this in MOS, simply by searching the
> > knowledge base for the error number.
>
> > Does kinda point up that you need to understand the whole tech stack -
> > the clue that it is sqlplus is the SP2 in the error message.
>
> > jg
> > --
> > @home.com is bogus.http://technology.amis.nl/blog/6404/oracle-rdbms-11gr2-solving-a-sudo...
>
> Does not make sense, unless I am confusing 2 things.
>
> I have plenty of stored procedure where say I create a dynamic query
> which is like 3500 characters long and there is no problem there.
>
> Why is this an issue?  Is it because it is getting "passed" into the
> stored procedure?- Hide quoted text -
>
> - Show quoted text -

Yes. You're trying to pass a single line with more than 2499
characters through the SQL*Plus interface and that's not possible.
It's not the VARCHAR2 length it's complaining about, it's the line
length.


David Fitzjarrell
 |  Next  |  Last
Pages: 1 2
Prev: VARCHAR2 Length
Next: "Business Logic / Rules should never be in the database or storedprocedures"