Menu
I have a stored procedure in Oracle Database 10g where my input is a varchar2 but I'm having issues getting it to run when the input string is long (not sure of exact length maybe > 8000).
My thought is the 'intext varchar2' (as below) is by default is too small. In other cases where I need a longer string I might define a varchar2 as "intext2 VARCHAR2(32767);" I tried to define the size similarly in the code below but my syntax is incorrect.
create or replace PROCEDURE TESTPROC ( intext IN VARCHAR2
) AS ....
What is the (default) size of the intext varchar2?
Can that size be defined (increased)?
thanks
409
Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2 . Fixed-length character data of length size characters. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes.
Introduction to Oracle VARCHAR2 data type A VARCHAR2 column can store a value that ranges from 1 to 4000 bytes. It means that for a single-byte character set, you can store up to 4000 characters in a VARCHAR2 column. By default, Oracle uses BYTE if you don't explicitly specify BYTE or CHAR after the max_size .
VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. If we declare datatype as VARCHAR then it will occupy space for NULL values , In case of VARCHAR2 datatype it will not occupy any space.
The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum column length (in bytes, not characters) between 1 and 2000 for the VARCHAR2 column.
You cannot specify a size for a VARCHAR2 parameter to a procedure.
The procedure should happily accept strings up to 32k in size (the maximum size of a VARCHAR2 in PL/SQL). If it were a function that was being called from SQL rather than PL/SQL, the limit would be 4k because the maximum size of a VARCHAR2 in SQL is only 4k.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
