Hi All,
When I try to update a column value separated by tab delimiter , the tabs are replaced by 4 spaces in the database. I have used CHR(9) as my separator.
Kindly help at the earliest.
Thanks
Ajith
" the tabs are replaced by 4 spaces"
Toad options|Editor|Behavior|Tabs (lower left)
Use spaces and place 2 as tab stops
Maybe they should be defined as Mode (use tabs)
Hi [mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05] ,
Thanks a lot for your reply.
But the settings mentioned by you seems to be only for toad editior and not for the dataset.
Kindly reply
Regards,
Ajith V
How are you editing the data? When I press tab within the data grid a tab character is not inserted, focus is moved to the next control. If you are editing the data in the popup editor then check Damir’s suggestion because that setting affects the popup editor as well.
Michael
Hi,
let us go again.
If you double click on field you are in “Grid popup Editor”.
You have Text and Hex representation of your content, so tab should be visible only in Hex as “09” (00 is dot after each sign as some kind of delimiter.
According previous post (define Mode (use tabs)) I was able to post Tab sign in VARCHAR2 field.
Brg
Damir Vadas
Hi [mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05]
Let me describe you the problem once again.
I’m have a column in a table having records separated by tab. I’m using a package to update this
column with values and identified that the tab is getting replaced with exact 4 spaces.The stub for calling
this package is being executed from Toad. Will there be any setting in client which will replace the tab with
space?
The part of the package where the coloumn is getting updated as given below.
OPEN abc_data_cur;
LOOP
FETCH abc_data_cur INTO abc_data_rec;
EXIT WHEN abc_data_cur%NOTFOUND;
adj_pomm_data := adj_pomm_data || abc_data_rec.abc_band || CHR(9) || abc_data_rec.det_count|| CHR(9)
|| ‘USD’ || CHR(9) || to_dec_tbms(abc_data_rec.abc_dur) || CHR(9) ||
to_dec_tbms(abc_data_rec.abc_pulse) || CHR(9) || to_dec_tbms(abc_data_rec.abc_price ) || CHR(9) ||
to_dec_tbms(abc_data_rec.abc_charged_price)||CHR(10);
tot_rec_count := tot_rec_count + abc_data_rec.det_count;
END LOOP;
IF abc_data_cur%ISOPEN
THEN
CLOSE abc_data_cur;
END IF;
UPDATE vary_data set pomm_DATA = adj_pomm_data where pomm_cud_rec_id =
ip_abc_rec_id;
Request your kindness to help me to find out the reason for the issue…
Regards,
Ajith V
You should read this blog: www.toadworld.com/…/good-database-design-it-39-s-never-an-accident.aspx
Having a single column store multiple values separated by a special character like this is very poor relational database design. You cannot join on such a column, you cannot search it with the benefits of an index, and you must do procedural logic on it to access any single value. This is the single worst mistake anyone can make in database design. You really should do it some other way.
Hi [mention:228f8d2504734c859820ea0da5a1084e:e9ed411860ed4f2ba0265705b8793d05] ,
Thanks for your reply. I agree to the reply.
But I’m keenly interested to know which setting has replaced my tab in database as 4 spaces.Kindly revert if you find any reason.
Regards,
Ajith
There’s really no way Toad could convert these tabs to spaces in code like your example. So I have a dumb question - what are the data types of the substrings being concatenated such as adj_pomm_data. I ask because there is a similar case ask tom oracle site (asktom.oracle.com/…/f) where maybe user had CHAR data types and those get spaces added as they are resolved to be concatenated.
How are you confirming that 4 spaces are used in the database? Are you selecting from that table using DUMP on that column? If not, try that. I’d take Toad completely out of the equation to verify the actual data in the database if you’ve not done so already.
Michael
Hi [mention:228f8d2504734c859820ea0da5a1084e:e9ed411860ed4f2ba0265705b8793d05] ,
The datatypes includes varchar and number.
Regards,
Ajith V
There is no way that TOAD is physically causing a TAB to be stored as spaces in a table. Your concatenation code is being executed exclusively by ORACLE.
It is more likely that the method you are using to view the data is converting the tabs into spaces (for viewing only). The TAB is still present in the database. Use the DUMP function in a SELECT to see what is actually in the database:
SELECT DUMP(pomm_DATA) FROM vary_data WHERE ROWNUM < 3 ;
Stephen Miller - email: miller_stephen at usa.net
How are you editing the data? When I press tab within the data grid a tab character is not inserted, focus is moved to the next control. If you are editing the data in the popup editor then check Damir's suggestion because that setting
affects the popup editor as well.
Michael
On 11/04/2013 06:49 AM, vgajith004 wrote:
Reply by vgajith004
Hi
damir.vadas_531 ,
Thanks a lot for your reply.
But the settings mentioned by you seems to be only for toad editior and not for the dataset.
Kindly reply
Regards,
Ajith V
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
You should read this blog:
http://www.toadworld.com/platforms/oracle/b/weblog/archive/2009/10/22/good-database-design-it-39-s-never-an-accident.aspx
Having a single column store multiple values separated by a special character like this is very poor relational database design. You cannot join on such a column, you cannot search it with the benefits of an index, and you must do procedural
logic on it to access any single value. This is the single worst mistake anyone can make in database design. You really should do it some other way.
How are you confirming that 4 spaces are used in the database? Are you selecting from that table using DUMP on that column? If not, try that. I'd take Toad completely out of the equation to verify the actual data in the database if
you've not done so already.
Michael
On 11/04/2013 12:41 PM, vgajith004 wrote:
Reply by vgajith004
Hi
Bert Scalzo ,
Thanks for your reply. I agree to the reply.
But I'm keenly interested to know which setting has replaced my tab in database as 4 spaces.Kindly revert if you find any reason.
Regards,
Ajith
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
“Will there be any setting in client which will replace the tab with space?”
I know that translate oracle function can replace any character with other…
But this has nothing with Toad
8(
Thanks [mention:f5e669462ed84c2185832f0ffc80c5b8:e9ed411860ed4f2ba0265705b8793d05] [mention:905b97f1ee7042c9a1453fdeea16c698:e9ed411860ed4f2ba0265705b8793d05] for your replies
I confirmed that the characters are space using the DUMP function. From all your comments, I feel toad has nothing to do with it.
Kindly let me know if character set issue can lead to this . If yes , throw the possible chances.
Kindly share all your suggestions and comments.
Regards,
Ajith
hi [mention:905b97f1ee7042c9a1453fdeea16c698:e9ed411860ed4f2ba0265705b8793d05] ,
Thanks for your reply.
Kindly clarify the working of operator dump.
If my characterset is say ‘A’ while posting a record and now my character set is ‘B’,
what character set would DUMP function retrieve.
Regards,
Ajith
Hi All,
Please reverl