Skip to Main Content
  • Questions

  • 19c doesn't allow truncation of data that is longer in length of column's char(40) definition

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alan.

Asked: March 27, 2024 - 5:44 pm UTC

Last updated: April 22, 2024 - 1:08 am UTC

Version: 19..16.0.0

Viewed 1000+ times

You Asked

We have an application that has been written to insert a variable that is char(50) into a column that is defined as char(40). In Oracle 11g (I know this is very old) it would merely truncate the last 10 characters without issue. However, Oracle 19c doesn't allow this and raises an exception (which I believe should've always been the case). Where can I find documentation of this restriction and when it was changed and is there away around this other than changing the program code?

Oracle 11 truncated that extra 10 characters in the below statemt

ADBBGNX_ADDRESS_LINE_1 := agentrecord.producerrec.businessAddressLine1;


Oracle 19 throws an exception with a NULL error status.

and Connor said...

Can we get some more context - I can't reproduce on any version

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> variable x char(40)
SQL> exec :x := rpad('a',50,'a')

PL/SQL procedure successfully completed.

SQL> print x

X
------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

SQL> variable y char(50)
SQL> exec :y := rpad('a',50,'a')

PL/SQL procedure successfully completed.

SQL> print y

Y
------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

SQL> exec :x := :y

PL/SQL procedure successfully completed.




SQL> select banner from v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

SQL> variable x char(40)
SQL> exec :x := rpad('a',50,'a')

PL/SQL procedure successfully completed.

SQL> print x

X
----------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

SQL> variable y char(50)
SQL> exec :y := rpad('a',50,'a')

PL/SQL procedure successfully completed.

SQL> print y

Y
----------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

SQL> exec :x := :y

PL/SQL procedure successfully completed.
</code>

Rating

  (3 ratings)

Comments

Further Context for my question

Alan, April 08, 2024 - 10:55 am UTC

The exception is raised in 19c when attempting to insert a value that is 50 characters long into a table's column that is defined as only char(40).
Please try to insert a row into a table with a column defined as char(40) using a value that is full char(50) in length.
Connor McDonald
April 09, 2024 - 4:46 am UTC

We are going to need to see your test case.... I get the same on 11g and 19c

QL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 9 12:38:41 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Last Successful login time: Thu Apr 04 2024 11:28:57 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.23.0.1.0


SQL> create table testChar (x char(40));

Table created.

SQL> desc testchar;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------
 X                                                                                CHAR(40)

SQL> insert into testchar(x) values('Oracle Apex is great for building.');

1 row created.

SQL> insert into testchar(x) values('Oracle Apex is great for building Applications Fast.');
insert into testchar(x) values('Oracle Apex is great for building Applications Fast.')
                               *
ERROR at line 1:
ORA-12899: value too large for column "ADMIN"."TESTCHAR"."X" (actual: 52, maximum: 40)


SQL>
SQL> variable x char(100)
SQL> exec :x := 'Oracle Apex is great for building Applications Fast.'

PL/SQL procedure successfully completed.

SQL> insert into testchar(x) values(:x);
insert into testchar(x) values(:x)
                                *
ERROR at line 1:
ORA-12899: value too large for column "ADMIN"."TESTCHAR"."X" (actual: 100, maximum: 40)





SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create table testChar (x char(40));

Table created.

SQL> desc testchar;
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- -------------------------------------
 X                                                                                CHAR(40)

SQL> insert into testchar(x) values('Oracle Apex is great for building.');

1 row created.

SQL> insert into testchar(x) values('Oracle Apex is great for building Applications Fast.');
insert into testchar(x) values('Oracle Apex is great for building Applications Fast.')
                               *
ERROR at line 1:
ORA-12899: value too large for column "MCDONAC"."TESTCHAR"."X" (actual: 52, maximum: 40)


SQL>
SQL> variable x char(100)
SQL> exec :x := 'Oracle Apex is great for building Applications Fast.'

PL/SQL procedure successfully completed.

SQL> insert into testchar(x) values(:x);
insert into testchar(x) values(:x)
                                *
ERROR at line 1:
ORA-12899: value too large for column "MCDONAC"."TESTCHAR"."X" (actual: 100, maximum: 40)


I can't see any silent truncation

From LiveSQL - Not a NULL error though

pws, April 08, 2024 - 12:27 pm UTC 

create table testChar (x char(40));
desc testchar;
insert into testchar(x) values('Oracle Apex is great for building.');
insert into testchar(x) values('Oracle Apex is great for building Applications Fast.');


Table created.


Column Null? Type
X  -  CHAR(40)

1 row(s) inserted.

ORA-12899: value too large for column "SQL_FAQVPCHKBNROQTQHDXWMYSPFZ"."TESTCHAR"."X" (actual: 52, maximum: 40) ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-12899

The issues has been resolved

Alan, April 09, 2024 - 11:02 am UTC

The original issue was:

The address text received from an external application file had 50 positions of content in its address field. However, the target Oracle address column was defined as Char(40). The developer said that their MicroFucus COBOL program worked against the 11g database stating that it would insert just the first 40 characters of the 50 characters provided.

ADBBGNX_ADDRESS_LINE_1 := agentrecord.producerrec.businessAddressLine1;

However, when that program attempted to do the same against 19c databases it threw an exception with a NULL error status.

The solution to the issue was to modify the program to substr(fieldname,1,40). I'm not sure what his program was doing for it to have ever worked against 11g. The program now works without error against 19c after making that programming change.

I ran the same tests you did and I got the same results you did, so I have no idea how their program ever worked against the 11g database.

Thanks again for your time and support.

Best Regards,

Alan
Connor McDonald
April 22, 2024 - 1:08 am UTC

Glad you got to the bottom of it
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library