SQLExec PeopleCode function trims string with leading and trailing tab character (Doc ID 1638946.1)

Last updated on FEBRUARY 23, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.53 and later
Information in this document applies to any platform.

Symptoms

On : 8.53.09 version.

ACTUAL BEHAVIOR
---------------
SQLExec function trims leading and trailing TAB character from a string column. This behavior can be observed in PeopleTools 8.51, 8.52 and 8.53, but not in PeopleTools 8.50.

EXPECTED BEHAVIOR
-----------------------
SQLExec function should only trim spaces, not tabs.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Use the following code to fetch a TAB character using SQLExec:

Local File &filetest;
Local string &delimiter;
Local string &text;

&filetest = GetFile("c:\temp\tabtest.txt", "W", %FilePath_Absolute);

/* Delimeter set to comma */
&delimiter = ",";
&text = "column1";
&text = &text | &delimiter | "column2";
&text = &text | &delimiter | "column3";
&text = &text | &delimiter | "column4";
&filetest.WriteLine(&text);

/* Delimeter set to tab character */
&delimiter = Char(9);
&text = "column1";
&text = &text | &delimiter | "column2";
&text = &text | &delimiter | "column3";
&text = &text | &delimiter | "column4";
&filetest.WriteLine(&text);

/* Delimeter set to tab selected from SQL */
SQLExec("SELECT CHR(9) FROM DUAL", &delimiter);
&text = "column1";
&text = &text | &delimiter | "column2";
&text = &text | &delimiter | "column3";
&text = &text | &delimiter | "column4";
&filetest.WriteLine(&text);

&filetest.Close();


The resulting file is the following:

column1,column2,column3,column4
column1 column2 column3 column4
column1column2column3column4


if the tab character is encapsulated in two dots it does display:

...

/* Delimeter set to tab selected from SQL */
SQLExec("SELECT '.'||CHR(9)||'.' FROM DUAL", &delimiter);
&text = "column1";
&text = &text | &delimiter | "column2";
&text = &text | &delimiter | "column3";
&text = &text | &delimiter | "column4";
&filetest.WriteLine(&text);


See the result below:

column1,column2,column3,column4
column1 column2 column3 column4
column1. .column2. .column3. .column4

PeopleBooks 8.53 the following remark is found regarding the SQLExec function:

Selecting Columns with Leading Spaces
When you execute a select SQL statement that returns data from a column that has leading spaces, the leading spaces will be removed from the column in the resulting text.

It seems that PeopleTools 8.53 treats a tab as a leading/trailing space.

References:

PeopleSoft PeopleTools 8.53 > PeopleCode Language Reference > PeopleCode Built-in Functions and Language Constructs.

http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tpcl/langref_PeopleCodeBuilt-inFunctionsandLanguageConstructs-073e6a.html#SQLExec-073c44__pt0087865

Changes

 PeopleTools upgraded from 8.50 to 8.53.

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