Toad World® Forums

ORA-01026: multiple buffers of size > 4000 in the bind list

Hi,

I’m getting the above error when trying to do SQL rewrites in SQL Optimizer 8.6. The query in question runs fine in TOAD which suggests the issue with the Optimizer. It is a particularly complex query but SELECT * FROM dual; also leads to the same error.

EDIT: I was not getting this message with version 8.0 of the Optimizer.

Google has not turned up anything particularly useful so any help would be appreciated.

Thanks,
Nic

Message was edited by: tootricky

Hi,

Can you use SQL Tracker to monitor what SQL have been executed in SQL Optimizer when the error occurred? Note that you will need to first connect in SQL Optimizer in order to monitor it in SQL Tracker. Try repeat the problem using a simple SQL and send us the log in SQL Tracker.

If you need more help, please contact Quest Support so we can send you more information.

Thanks,
Alex

Hi,

thanks for the prompt response. I have re-run the SQL with the tracker running and have attached the output. I also ran a couple of simple queries and the same error occurred and exactly the same messages were logged in the tracker.

The logs also indicate that an expected plan table cannot be found so I’m hoping I have just missed something in the setup.

Thanks,
Nic
Simple SQL_a.txt (3.36 KB)

Hi,

thanks for the prompt response. I have re-run the SQL with the tracker running and have attached the output. I also ran a couple of simple queries and the same error occurred and exactly the same messages were logged in the tracker.

The logs also indicate that an expected plan table cannot be found so I’m hoping I have just missed something in the setup.

Thanks,
Nic
Simple SQL_b.txt (3.35 KB)

Hi,

thanks for the prompt response. I have re-run the SQL with the tracker running and have attached the output. I also ran a couple of simple queries and the same error occurred and exactly the same messages were logged in the tracker.

The logs also indicate that an expected plan table cannot be found so I’m hoping I have just missed something in the setup.

Thanks,
Nic
SQL Rewrite.txt (3.35 KB)

Hi,

What is the locale of your Windows? Can you please check your registry the NLS_LANG setting under your Oracle Home? If it is not matching with your current locale setting in your Widnows, try set the NLS_LANG to match with the corresponding locale and try again.

Also, you may want to check if this environment variable %NLS_LANG% is set in your system as this environment variable will override the registry setting if set.

Let me know if it helps.

Thanks,
Alex

Not sure if this is what you meant but Windows is UK/English (UK). NLS_LANG is set to AMERICAN_AMERICA.WE8ISO8859P1 for all homes as this is what is required when accessing our EBS systems(certain views will not return data if USERENV(‘LANG’) != ‘US’). Although I have had a new PC built which may have been since I last used the optimizer, I would say that this would not have changed. No %NLS_LANG% environment variable exists.

The query in question runs no problem in TOAD so I would not expect this to be the issue.

Thanks,
Nic

Hi,

Please contact support to setup a case for further follow up on this issue.

Thanks,
Cliff

Hi!

I’m facing the same error. I have to present this tools (SQL Optimizer) and on the first step I get a problem which I do not understand.

Is there any written manual about this pretty nasty and known issue?

Rg
Damir Vadas
Prouno

Hi,

Have you checked you NLS_LANG to make sure it matches with your Windows locale setting as mentioned in the thread above? And use SQL Tracker to track the SQL with problem and run it in Toad to see if the problem is in the environment level or in the Optimizer level?

If that doesn’t help, I will suggest to contact Support to open a support case to help you.

Thanks,
Alex

Hi,

THX for quick response.

I’m working in company (Prouno, www.prouno.hr) that sell Toad in Croatia. And will plan to have education on Toad possibilities. So SQL Optimization is one of then I’d like to accent more.
This is why I’d like to have deeper knowledge then regular Toad user.
NLS_LANG is set to “CROATIAN_CROATIA.EE8MSWIN1250” in all ORACLE_HOME.
Mine Windows Regional setting is set to Croatia in all places…so this should work fine … but it is not.
When I attack local database (not on server but on mine computer) this error is not shown …

So let me ask something more on this subject to make clear.
This error is connected only with local settings or I have to follow server side NLS_LANG settings.

Please make a wider post to close this case down.

Rg,
Damir

Hi,

The NLS_LANG should be set according to your client machine so in case your client machine uses a different character set than the one used in your database, Oracle will be aware of the difference by the NLS_LANG setting and hence do the necessary conversion.

The error message you encountered seems to be an environmental issue or Oracle problem. If you google the error, you will see different users reported this problem and one of the common possible cause is an invalid NLS_LANG used. That is why I asked you to check the NLS_LANG setting.

Your NLS_LANG seems to be a correct one for Croatian Windows. However to make sure that it is used please check if there is a NLS_LANG environment variable defined in your system as the environment variable will take precedence over the registry setting. To do so, open a command prompt and run this:
echo %NLS_LANG%

If you see a value returned, then there is an environment variable set and you should make sure the environment variable NLS_LANG is using the correct character set.

Next is to check your system if the character set is supported. To do so, go to registry and check under this key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
Locate the value for ACP and check if it is 1250.

If nothing helps, try change the NLS_LANG of your Oracle Home (in registry) to use CROATIAN_CROATIA.UTF8 and try again. SQL Optimizer is Unicode-support so using UTF8 character set should work for you also.

If it still doesn’t work after all the changes, then may be the problem is not NLS_LANG related or I have missed something I don’t know. Oracle Support would be another choice then.

Thanks,
Alex

Alex,

THX for very comprehensive explanation.
I found a problem (multi home, one has wrong NLS_LANG)
Now I see this is customer related error
Cheers,
Damir Vadas

P.S.
I find this issue as answered but cannot change status because I’m not original poster …
8(