Hi,
[please notice that I have used "_" as blanks in the following text]
Can anyone tell me a simple question I am aksing to myself ?
When I run the following query in Toad :
SELECT 'Test' FROM DUAL
I get a result grid with [Test____________________________] as a result. The size of the cell data is 32 chars and 28 blanks have been added in the end of the string. If I do the same using another database client, I don't get those blanks.
I saw the following option in Toad : "Trim string data in CHAR and NCHAR columns". I tried to activate it and when I run the query the blanks are removed. But when I explicitely add a blank in the end of the string, it is also removed. Example 
SELECT 'Test______' FROM DUAL
I get a result grid with [Test] as a result. But I don't want blanks to be removed.
I don't understand why Toad has such a behaviour.
Is there a way to tell Toad not to change results and keep the original string I have in my query ?
Thank you for your help.
Best regards.
MS
That option doesn't know the difference between oracle's automatically added spaces and the ones in your query.
For me, that column comes back as a VARCHAR2, not CHAR, and I have no extra spaces at the end of it.
But you can try explicitly casting it to varchar2, maybe that will help.
select cast('Test ' as varchar2(10))
from dual;
Thanks for your response.
Of course, when I trim or cast my column it works. But my purpose is to understand why I get the behaviour of getting extra spaces because I have lots of queries that I run that are concerned and I don't want to rewrite them because of the use of Toad. The main problem is that when I extract data from Toad result grid, I get the extra spaces too. And also visually, I see spaces that don't exist in the database, wich complicates data reading and debugging.
Can you confirm that Oracle is giving you varchar2s or chars?
Do a CTRL+F9 on your query in the editor and it will give you the datatypes.
When I do that, I get
CHAR(4) for select 'Test' from dual and
CHAR(5) for select 'Test ' from dual
Is there more to your test that you're not telling us?
This is what I get doing a CTRL+F9 on my query
CHAR (32) for select 'Test' from dual
Weird
You can find out if the extra size is happening on the server or client side by:
create table test_table
as select 'test' test_column from dual;
then look in user_tab_columns to see how big oracle created the column
select *
from user_tab_columns
where table_name = 'TEST_TABLE';
I get '4' in the DATA_LENGTH column. If you get 4... then something is going on in the oracle client or Toad to make the size come back as 32. If you get 32...then it's something on the server side.
Hi,
I tried it. I get CHAR (4 Byte) as a data type. There is something wrong in Toad I am effraid.
Regards,
MS
It could be Toad or it could be the Oracle client.
Please connect to the database that you have the problem with, then in Toad, go to Help -> Support Bundle. That will collect data about your environment.
You can email it to me: John.Dorlon@quest.com.
Also please send me your user files folder. This is where your Toad settings are stored. To find that:
- Go to Main Menu -> View -> Toad Options.
- Along the left side, click Options -> General
(or, in Toad 17.0 or newer, Options -> Files and Folders -> App Folders)
- Click the "Open Folder" button on the right. A Windows Explorer window will open.
- Close Toad before you continue.
- In Explorer, Zip up the "User Files" folder.
- email it to me.
Hopefully then I will be able to repeat the problem and tell you what you can do differently to avoid it, or if there is a bug I can fix it.
Thank you.
It is not a server side problem, it is a client side configuration that can be fixed by checking below option:
View->Toad Options->Data Grids->Data
Actually I saw that setting. But when I activate it I get 4 byte for the following :
SELECT 'test ' FROM DUAL
Because it trims my string.
I would like Toad not to change my data at all
Check Options -> Oracle General.
In the top right, "newline format for character data", set that to the first option (it says "no change" in newer versions, "Windows style" in older versions, but the effect is the same). For me, if this was on the 2nd option, it adds a bit of white space.
