CHAR Datatype and ConCat in Oracle and SQL Server.

Char is a Fixed length data type, provided by database to stored Character Data.
It will allocate fix bytes as per length specified for CHAR datatype and will pad with Space for remaining chunks.

With Variable declare as CHAR(20), Byte allocated will be always 20.

DECLARE @Var CHAR(20)='Hello'

Output :

Now lets see how it behave in case of CONCAT for Oracle and SQL Server.

SQL Server:
We are declaring a CHAR Datatype with Length(20), and trying to ConCat.

DECLARE @Var CHAR(20)='Hello-'
SET @Var= @Var+'SQL Server'

OUTPUT: "Hello- "

In Case of SQL Server, even after Concat it only displaying previous assign Data due to initial padded space.
With we Declare CHAR(20) and assign it “Hello-“, it is actualy Stored as


0 represent Space and total DataLength is 20.

So any Concat is Beyond length specified for @Var i.e. 20, Hence Concat is not possible.
But it wont throw any Exception or Error, it will whitewash any ConCat.


SET serveroutput ON;
var_1 CHAR(20000) := 'HELLO-';
var_1 := var_1 || 'ORACLE';

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
06502. 00000 - "PL/SQL: numeric or value error%s"

Oracle will Throw exception for such Concat with CHAR Datatype.
Unless we Use TRIM, Oracle will throw exception for any Concat with CHAR Datatype.


About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s