Right Padding Spaces
Moderators: chulett, rschirm, roy
Right Padding Spaces
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
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
I assume you have VarChar2() columns. What load method for Oracle are you using?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The length of CHAR(10) is always 10, regardless of the contents. Convert your string to VarChar, use PadString, then convert back to Char.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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"?
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"?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
I'm trying to get "Hello " out of "Hello"
Thanks
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).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.
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:
Replace Char10Column and TestTable as required.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
I'm not sure how you keep a CHAR field from padding with spaces, even by trying very hard.ArndW wrote:If you write Varchar(10) "Hello" to an Oracle CHAR(10) you are going to get "Hello " unless you try very hard not to.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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 wrote:...I'm not sure how you keep a CHAR field from padding with spaces, even by trying very hard.
Last edited by ArndW on Tue Nov 06, 2007 1:42 am, edited 1 time in total.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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.