Hello,
I have Toad for Oracle 12.1 and my install has a flaw in that I can only retrieve a maximum of 1143 characters per cell when I execute a query. I have had two other users execute the exact same query and they retrieve the full results as do I when I use SQL Developer to run the query. I was originally limited to 215 characters but I un-installed and re-installed the software. Now it limits me to 1143 characters on re-install.
I know the limit because when I export the data to excel and verify that everything in Toad has transferred to Excel and then do a length function on the cells, the largest is 1143 and there are several of them.
Does anyone have a suggestion on how to resolve the issue? I have reviewed all of the preferences/options and have not had any luck with making the size unlimited. Some of the retrievals I execute will exceed 30,000 characters.
Thank you.
First, let’s isolate where the problem is. You say you are checking the length of the data in Excel - is it correct in Toad? If so, the problem lies in transferring the data to Excel. If not, then the problem lies in transferring the data from the database to Toad.
Hi John,
The Toad results are being limited. Currently Toad will not extract more than 1143 characters from the database. It is not an issue with Excel limits.
Thanks.
Joe
OK, let’s take Excel out of the picture then.
So, if you select data in the Editor, then all of the data is not present in the grid. Can you provide an example of what’s in the database that isn’t appearing in the grid?
If there are any ASCII 0 characters present, that is interpreted as an “end of string” identifier by many windows programs, including Toad. That could be the cause of the problem.
You could test for it this way:
select instr(column_of_interest, chr(0))
from your_table
where instr(column_of_interest, chr(0)) > 0
if that query comes back with any rows at all, the ‘0’ characters (not the number 0 but the ascii value 0) is causing Toad to cut off some of your data.
If the query comes back with no rows at all, then the problem is something else, and the next thing I would do is take a close look a the values that are getting cut off and see if they are getting cut off in Toad’s data grid. If this is the case, then can you provide an example that demonstrates the problem?
Hi John,
As I said, other employees have used the exact same query and extracted the full code. In the first example below, it shows where Toad cuts off the retrieval.
In the second example, I show the same query with SQL Developer and use a red font to show additional data that is retrieved from the database using SQL Developer.
-----------------------End ot Toad Retieval (4 in 364 is the 1143rd character--------------------------------------
AND :G_PERSON_TYPE IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 402’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND comp_class = ‘CC01’
AND (bc || ‘.’ || rc = '364
-------------------SQL Developer retrives all data--------------------------------------------------------------------------
AND :G_PERSON_TYPE IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 402’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND comp_class = ‘CC01’
AND (bc || ‘.’ || rc = ‘364.653’)
AND :G_PERSON_TYPE IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 403’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND comp_class = ‘CC01’
AND (bc || ‘.’ || rc <> ‘364.653’)
AND :G_PERSON_TYPE IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 404’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND (bc || ‘.’ || rc = ‘423.651’)
AND :G_PERSON_TYPE IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 405’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND comp_class IN (‘CC02’, ‘CC03’)
AND (bc || ‘.’ || rc = ‘364.141’) THEN ‘US.FRI.CPG 406’ WHEN payroll_id = 118 AND grade_type = ‘OFFC’
AND (bc || ‘.’ || department = ‘364.1413002’) THEN ‘US.FRI.CPG 407’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND (bc || ‘.’ || rc <> ‘364.653’)
AND :G_PERSON_TYPE NOT IN (‘SUPV’, ‘SUPV_EXT’) THEN ‘US.FRI.CPG 504’ WHEN payroll_id = 118 AND grade_type = ‘PROF’
AND (bc || ‘.’ || rc = ‘364.653’)… there are more than 28,000 characters retrieved
------------------------------------------------------------------------------------------------------------------------------------------------
There must be a parameter or something limiting the retrieval but I cannot find it.
Toad has no option or parameter to limit results like this. I guess that is a CLOB column. There are some CLOB related options under Options -> Data Grids -> Data -> (RHS) Behavior, but I don’t think any of them will cause this.
You might make sure that you are using the same Oracle client version as your co-workers. Maybe there is some kind of compatibility problem there.
What about what I said in the grid? Are these values getting cut off in the grid, or just when you send to Excel? I am trying to help you but it’s difficult when I don’t get straight answers.
Hi John,
Yes, this is a CLOB and I have been through all the options you referred to. Yes, we are all using the same Oracle client version. Yes the values are getting cut off in the Toad grid. Everything that is in the grid gets exported to Excel. I only use Excel for the function =(len) to determine what the length is of each cell created from the Toad grid and all appear to be limited in Toad to 1143 characters since that is the maximum length of any cell.
I apologize if I do not appear to be giving straight answers but I don’t know how to explain the issue any more plainly. Thank you for your assistance.
I had another thought during lunch. Are you executing the query “as a script” in the Editor (using F5)? If so, execute the query with F9.
Thank you John. That did the trick. In the grid it is labeled as (HUGECLOB) and when I export it to Excel, I can see the entire contents. Thank you very much.
You’re welcome. Sorry it took me so many tries to get to the right answer! 
