3798: A column or character expression is larger than the max size.

Database
Teradata Employee

3798: A column or character expression is larger than the max size.

Hi ,


I am in TD 15.10.05.03 AND I am getting 3798 error while running the SP.
SP definition:
REPLACE PROCEDURE DEV42_1_DB_RDM.ad()
BEGIN
DECLARE l_outmsg VARCHAR(16500) CHARACTER SET Unicode DEFAULT '';
END
;

But it doubles the size of VARCHAR:
USING ( _spVV0 VARCHAR(33000) CHARACTER SET UNICODE ) SELECT '' INTO :_spVV0 ;

Could you please let me know solution for this .

1. Please tell what's maximum allowed size of VARCHAR variable in UTF8 charset?
2. How to use other string variables with bigger amount of characters?

Thanks in Advance.

Best Regards,
Sunder

6 REPLIES
Senior Supporter

Re: 3798: A column or character expression is larger than the max size.

unicode and varchar is a bit tricky because its definition is sometimes mixing the number of charcters and the number of bytes to represent the chars.

In latin this matches 1:1 in unicode it is 1 char : 2 bytes.

so the varchar(16500) will require up to 33000 bytes.

Where I am not sure that the created statement is correct - to my understanding it should be still a varchar(16500)

the max row size is still 64kb. So it might be that the varchar(33000) is considered as 66000 bytes which would be too big...

I didn't find a specific SP limitation in the documentation on unicode - but I might have missed it.

 

try 16000 (assuming this is the only column in the table) and afterwards 16001 if the first one works and the second one not you might get in contact with CS and open a ticket.

 

Senior Supporter

Re: 3798: A column or character expression is larger than the max size.

I just validated this on TD 16.00 Express version

 

 

create set table msg (msg varchar(32000)) primary index (msg);

REPLACE PROCEDURE sp_msg()
Begin

DECLARE l_outmsg VARCHAR(16001) CHARACTER SET Unicode DEFAULT '';
set l_outmsg = 'this is just a test';

insert into msg (msg) values (:l_outmsg);
end;

delete from msg;

call sp_msg();

select * from msg;

select top 10 * 
from dbc.qrylogv 
where username = user
order by starttime desc
;

drop table msg;
drop procedure sp_msg;

DECLARE l_outmsg VARCHAR(16001) CHARACTER SET Unicode DEFAULT '';

results in an error and

DECLARE l_outmsg VARCHAR(16000) CHARACTER SET Unicode DEFAULT '';

works

where you can see that the executed SQL is

USING ( _spVV0 VARCHAR(32002) CHARACTER SET UNICODE )  SELECT '' INTO :_spVV0 ; 

or

USING ( _spVV0 VARCHAR(32000) CHARACTER SET UNICODE )  SELECT '' INTO :_spVV0 ; 

check the DBQL...

So the error is based on the implementation of the set variable comment which runs a select.

 

The Insert is generated correctly...

USING 
      _spVV0 (VARCHAR(16000), CHARACTER SET UNICODE, NOT CASESPECIFIC)INSERT INTO msg (msg = :_spVV0 );

I would suggest to open an incident for this...

 

Teradata Employee

Re: 3798: A column or character expression is larger than the max size.

 

Thanks ulrich ,

So  i just want to know is it  working as per design .

Senior Supporter

Re: 3798: A column or character expression is larger than the max size.

Final call is on Teradata to answer.

Where I would have some trouble to understand a design which would limit your abiliy to add data into a well defined column.

To me it's a bug not a feature :->

Teradata Employee

Re: 3798: A column or character expression is larger than the max size.

The documentation covers this subject under: Teradata Database, SQL Data Manipulation Language, Release xx.xx, Chapter 3, using request modifier, usage notes, Character String Definitions in a USING Request Modifier. Review this topic. Note that the USING modifier character length is in bytes. Also it is best to remove the character set clause in USING as it is ignored and misleading. I guess we have never returned an error for this. Maybe we should return a warning? Under 'data_type_attribute' we state: "The server character set attribute (for example CHARACTER SET LATIN) cannot be part of data_type_attribute."

 

Dave

Senior Supporter

Re: 3798: A column or character expression is larger than the max size.

two comments/questions:

1. if it is bytes why does

USING ( _spVV0 VARCHAR(32002) CHARACTER SET UNICODE )  SELECT '' INTO :_spVV0 ; 

fail? As bytes it should be OK.

2. this SQL is generated by the Stored procedure. Therefore the user/developer can't influence this.