Selecting Literal Over Dblink in 9.2 Results In The Literal Concatenated With Blank Spaces (Doc ID 1077804.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 9.2.0.3 to 9.2.0.8 - Release: 9.2 to 9.2
Information in this document applies to any platform.

Symptoms

Selecting a CHAR literal from a remote table, inside a pl/sql procedure, returns the literal itself concatenated
with blank spaces, example 'AGE     ' instead of 'AGE'.

This problem occurs in Oracle Server 9.2 when NLS_CHARACTERSET is set to UTF8, and the
NLS_LENGTH_SEMANTICS is set to CHAR.

Code example:
DECLARE
  CURSOR c1 IS SELECT 'AGE' str1 from dual@dblink;
BEGIN
  for r1 in c1 LOOP
    dbms_output.put_line ('####'||r1.str1||'####');
    dbms_output.put_line ('Length of this string = '||length(r1.str1));
  END LOOP;
END;
/

Result:
####AGE ####
Length of this string = 9

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms