IBM Support

CHAR parameters of a PL/SQL procedure default to a maximum length of 254 if no length is specified which differs from Oracle

Troubleshooting


Problem

CHAR parameters of a PL/SQL procedure default to a maximum length of 254 if no length is specified which differs from Oracle

Symptom

In a PL/SQL procedure that has a parameter type that is CHAR, DB2 will default to the maximum length of CHAR(254) if no length is specified. The following VARCHAR2 and CHAR value will not match as the CHAR value will have spaces appended after the text up to the maximum length of 254 characters.

create table T2 (STR1 varchar2(50));
insert into T2 values('test1');

create procedure TEST_PROC(CHARPARAM char)
is
cursor CRSTEST
is select count(1) from T2 where STR1 = CHARPARAM;

..

call TEST_PROC('test1');


This differs from Oracle which acquires the length of the CHAR argument such that no spaces are padded. In Oracle, the above query will be able to match the CHAR value to the VARCHAR2 value.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Performance - Admin views and table functions","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

To view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use the link to actual document below to access the full document. You will be asked to log on if you are not already logged in. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

link to actual document

Document Information

More support for:
Db2 for Linux, UNIX and Windows

Software version:
9.7

Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows

Document number:
433885

Modified date:
30 April 2025

UID

swg21514949

Manage My Notification Subscriptions

Page Feedback