Max Length of PL/SQL Varchar Parameter

[SirDrinksalot]

Hi there,

I have written a procedure which takes a delimited list of values as a
varchar2 datatype. Only problem is that apparenty the length of the
list exceeds that permitted by the varchar2 datatype - a measly 200
bytes according to the definition i am receiving. Surely this isn't
right, here is a copy of the procedure definition:

PROCEDURE modifydata (tableName IN VARCHAR2, updatetype IN VARCHAR2,
v_values IN VARCHAR, v_delimiter IN VARCHAR2, v_status OUT RC)

It is the v_values variable, how do i get the size of the variable
increased?

Cheers

Dan

[SR]

You have v_values declared as varchar instead of varchar2.
I am not sure if that has anything to do with it. But try changing
that.

SR

[fitzj...@cox.net]

Comments embedded.
SirDrinksalot wrote:
> Hi there,
>

And a bodacious howdy-do to you.

> I have written a procedure which takes a delimited list of values as a
> varchar2 datatype.

No, you've written it to take a VARCHAR datatype if that which you've
posted is to be believed.

> Only problem is that apparenty the length of the
> list exceeds that permitted by the varchar2 datatype - a measly 200
> bytes according to the definition i am receiving.

The problem is, apparently, one of your reaching the maximum value for
a single line in SQL*Plus.

> Surely this isn't
> right, here is a copy of the procedure definition:
>
> PROCEDURE modifydata (tableName IN VARCHAR2, updatetype IN VARCHAR2,
> v_values IN VARCHAR, v_delimiter IN VARCHAR2, v_status OUT RC)
>
> It is the v_values variable, how do i get the size of the variable
> increased?
>

You don't, and there is absolutely no need to do so. The variable,
when properly declared, will accept strings up to and including the
maximum declarable length for a VARCHAR2. Try changing the errant
definition and calling the procedure with another PL/SQL block with a
variable declared as varchar2(4000) and sufficient text to exceed your
'200 byte' limit. You should find this 'limit' lifted.

> Cheers
>
> Dan

David Fitzjarrell

[SirDrinksalot]

Hi guys,

Thanks for that but the problem turned out to be nothing to do with my
code. I was using TOAD to debug the proc and it seems that varchar
parameters are restricted by the app to 200 characters, when i ran the
code through SQL*Plus or a small VB app i created for testing it worked
fine. Annoying thing is I tried using SQL Developer and that has the
same restriction!

[Herod]

I just tried Toad and SQL Developer debugging a package... I can find
no such limitation calling or stepping through a procedure.
I passed 2089 characters from one procedure to another inside of a
package and Toad and SQLD were both able to watch the output.

Can you elaborate?

Herod

http://yaodba.blogspot.com

[SirDrinksalot]

Sure, within TOAD I loaded the PL/SQL package into the Procedure
Editor. I then click on the Parameters button to supply input
parameters for the proc in question and i notice that in the type
column, where there are VARCHAR2 variables, the number in brackets says
200. I did not set it to 200, and in SQL*Plus and from within the VB
App the proc works fine, but from TOAD / SQL Developer the error
message reports ORA-06502: PL/SQL: numeric or value error: character
string buffer too small ORA-06512: at line 11 . It may be that there
is a preference option to remove this limit and i haven't come across
it yet?

Dan

[Harry Houghtaling]

use a varchar2 type, which has a max len of 2000.

[SirDrinksalot]

Sorry Harry, the old varchar was just me testing to see if changing to
straight varchar would sort the problem, which it didn't, they both
came back as varchar(200) or varchar2(200)...