VARCHAR2 datatype for very long string data

[manush]

Hi,
I declared a variable in my stored procedure as
strSQL VARCHAR2(8000);
Because I have a very long string with select query using 4-5 UNIONs. The length of my string is about 7500

But I am not able to assign it in strSQL.

If we can't declare VARCHAR2 with such a long length, can what is the other solution.

Please help ..

Manoj

 

[sem]

Check your lengths again, you should be able to assign. Note, that if you use UTF some characters may occupy more that 1 byte.

Regards, Dima

http://seminihin.narod.ru

 

[manush]

Hi Sem,

Thanks for your reply.
But i am not able to assignt this value. Someone is saying that I can't assign value > 4000.

Can you please tell me any alternate sol.

Manoj

 

[SantaMufasa]

Manoj,

Here are results of my findings, showing that there should be no problem manipulating a varchar2 field up to 32K in PL/SQL:

set serveroutput on
declare
	big_varchar2	varchar2(32767);
begin
	dbms_output.enable(1000000);
	big_varchar2	:= rep('x',32767);
	dbms_output.put_line('Length of big_varchar2 = '||length(big_varchar2));
end;
/
Length of big_varchar2 = 32767

Let us know of this helps.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:34 (19Aug04) UTC (aka "GMT" and "Zulu"), 10:34 (19Aug04) Mountain Time)

 

[dbtoo2001]

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/02_funds.htm#16763

VARCHAR2
You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_size)


You cannot use a constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

The VARCHAR2 datatype involves a trade-off between memory use and efficiency. For a VARCHAR2(>= 2000) variable, PL/SQL dynamically allocates only enough memory to hold the actual value. However, for a VARCHAR2(< 2000) variable, PL/SQL preallocates enough memory to hold a maximum-size value. So, for example, if you assign the same 500-byte value to a VARCHAR2(2000) variable and to a VARCHAR2(1999) variable, the latter uses 1499 bytes more memory.

Remember, you specify the maximum size of a VARCHAR2 variable in bytes, not characters. So, if a VARCHAR2 variable stores multi-byte characters, its maximum size is less than n characters. The maximum width of a VARCHAR2 database column is 4000 bytes. Therefore, you cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 column.

You can insert any VARCHAR2 value into a LONG database column because the maximum width of a LONG column is 2**31 bytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2 variable.