Right Padding Spaces

[bonds]

Hello,
To maintain referential integrity, I need to Right Pad my variable Length source field to make it of size 10 Chars.
Both my source and target is Oracle.
I have used various combinations of
1) Padstring(Link.XXX," ",10) in Transform
2) Oracle_Load_Delimited = ,
3) Oracle_Preserve_Blanks = True
4) APT_Pad_String = 0X00 / 0X20

None of the option is able to right pad spaces to my field. I have seen even concatenating (link.XXX : " " ) is not working.

Thank in advance.
Satish

[ArndW]

I assume you have VarChar2() columns. What load method for Oracle are you using?

[bonds]

Thanks for quick response.

Write Method is Load => Replace.

Size is Char(10) in both source and Target.

[ArndW]

The length of CHAR(10) is always 10, regardless of the contents. Convert your string to VarChar, use PadString, then convert back to Char.

[ray.wurlod]

Bring $APT_STRING_PADCHAR into your job as a job parameter and set its value to " " (a space character).

[bonds]

Hi,

I tried reading them as Varchar(10) and applying Padstring() in Transformer and writing it to Char(10). I'm still getting the same problem.

Using $APT_String_Padchar as " " will add spaces behind all Char and Varchar fields. i.e. if a field is Varchar(100) and I pass only 'ABC' to it; in Database it will be shown as 'ABC ...( 97 Spaces)...' (Length 100) . I believe it is not a good practice.

Converting to Char-Varchar-Char solution looks right to me, wonder why it is not working.

Thanks

[ArndW]

Bonds - a CHAR(1000) field will use up 1000 characters regardless of contents.

I'm a bit confused about what you are trying to do, is it

1. "Hello" stored as "Hello ", or
2. "Hello" stored as " Hello"?

[bonds]

Sorry about not being clear. My previous comment was more concerned with varchar's. Using $APT_String_Padchar = " " will add spaces to its max length for both Char and Varchar Types. Unfortunately I have a Field of Varchar (400) and I cannot afford to use this Environment Variable.

I'm trying to get "Hello " out of "Hello"

Thanks

[ArndW]

If you write Varchar(10) "Hello" to an Oracle CHAR(10) you are going to get "Hello " unless you try very hard not to. Can you do a SEQ(In.Col[9,1]) and output that to a peek stage? It should be 32 (ASCII Space).

[bonds]

Yes It should be "Hello " in char field in Database. Even if you do Length on it, it is showing 10.
Even the data i'm loading is always "Hello " not "Hello", but when I view data in Toad or PL/SQl Developer it shows as "Hello" instead of "Hello ".

I'm having this issue because it have a foreign key constraint and in Referencing table it is "Hello " and when I load into my Table (which is char(10) same as the referenced table field) it is failing to enable the constraints; because data in there is 'Hello" not "Hello ".

One more strange experience with DataStage. Not surprised any more.

However it will be great if this could be resolved.

Thank You.

[ArndW]

I don't know where your issue is coming from. A char(10) field will always have exactly 10 characters, no more, no less. Take DataStage out of the equation for a minute and just use your PL/SQL. What results do you get for:

Code: Select all

Select LENGTH(Char10Column) from TestTable;

Code: Select all

Select count(*) from TestTable where substr(Char10Column,8,1) = ' ';

Replace Char10Column and TestTable as required.

[chulett]

If you write Varchar(10) "Hello" to an Oracle CHAR(10) you are going to get "Hello " unless you try very hard not to.

I'm not sure how you keep a CHAR field from padding with spaces, even by trying very hard.

And keep in mind the fact that Toad has an option to automatically 'Trim string data in CHAR and NCHAR columns', make sure that's not turned on. It being enabled would, however, explain why the length check shows as 10 but the display grid has removed the trailing spaces.

[ArndW]

...I'm not sure how you keep a CHAR field from padding with spaces, even by trying very hard.

The only way I could think of was to change the pad char to something else, like 0x000 I've learned never to state absolutes here unless I have proof and recent test cases; and even then Ray will come along and prove me wrong...

[chulett]

Right, was wondering if that was what you meant - by explicitly padding with another character. However, an unpadded string on the other hand..

[bonds]

Yes It should be "Hello " in char field in Database. Even if you do Length on it, it is showing 10.
Even the data i'm loading is always "Hello " not "Hello", but when I view data in Toad or PL/SQl Developer it shows as "Hello" instead of "Hello ".

I'm having this issue because it have a foreign key constraint and in Referencing table it is "Hello " and when I load into my Table (which is char(10) same as the referenced table field) it is failing to enable the constraints; because data in there is 'Hello" not "Hello ".

One more strange experience with DataStage. Not surprised any more.

However it will be great if this could be resolved.

Thank You.