# Toad for Oracle Base Edition **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [March 13, 2014, 6:50am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298 "2014-03-13T06:50:26Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 6:50am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/1 "2014-03-13T06:50:26Z")
Hi, I’m new to this forum. I use Toad For Oracle Base Edition. I use it to query security info in our database. It allows me to query only up to 5000 securities at a time using Or statements. Is there a way to increase that limit? Are there more advanced versions of Toad that have higher limits? Thanks. ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [March 13, 2014, 6:53am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/2 "2014-03-13T06:53:39Z")
There is no limit on rows of data that are returned - so not sure what problem or issue you are having … ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 6:59am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/3 "2014-03-13T06:59:52Z")
Sorry, not data returned. I can only query up to 5000 securities at a time. The results can be thousands and thousands of rows, no issue there. The input data only takes 5k. Here’s an example of my query: SELECT a._, b._ FROM table1 a, table2 b WHERE a.type=b.type AND ((A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c = ‘NA’) OR (A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c = ‘NB’) OR (A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c = 'NC) OR (A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c = ‘ND’) OR (A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c = ‘NE’) . . . . ) ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [March 13, 2014, 7:04am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/4 "2014-03-13T07:04:36Z")
wow - not the best way to write SQL - here’s an exmaple of one potential cleanup: SELECT a._, b._ FROM table1 a, table2 b WHERE a.type=b.type AND ((A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)) … Another question -if the values are in another table then you’re hard coding a join in syntax - why not use a join instead??? ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 8:09am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/5 "2014-03-13T08:09:01Z")
I’m only a novice user I guess because I’m not sure what you mean. I can’t use “IN” statement because the input limit on those is 1,000. ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [March 13, 2014, 8:12am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/6 "2014-03-13T08:12:07Z")
combine techniques to shorten the SQL - this is really a SQL question and should be on our SQL forum and not really toad per se … SELECT a._, b._ FROM table1 a, table2 b WHERE a.type=b.type AND ((A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’) OR (A.issuer\_id\_c = ‘XXXXX’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)) ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 13, 2014, 8:17am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/7 "2014-03-13T08:17:30Z")
Does that list of issuer\_id\_c’s and issue\_alpha\_c’s come from another table? If so, you can do, then assuming the table with all those codes is “Table3”, and the column names are the same, you can do something like this. you can add more where clause conditions on table3 if you need to. select a._, b._ from table1 a, table2 b, table3 c where a.type = b.type and a.issuer\_id\_c = c.issuer\_id\_c and a.issue\_alpha\_c = c.issue\_alpha\_c ---
**Author:** ![Bert\_Scalzo](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/bert_scalzo/32/274_2.png) [@Bert\_Scalzo](https://forums.toadworld.com/u/Bert_Scalzo)\ **Post date:** [March 13, 2014, 8:19am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/8 "2014-03-13T08:19:05Z")
that’s exactly what I asked/said in my first reply - seems to me that SQL code is hard coding a possible join … ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 13, 2014, 8:22am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/9 "2014-03-13T08:22:16Z")
I know, I was just spelling it out… **From:** Bert Scalzo [[mailto:bounce-Bert\_Scalzo@toadworld.com](mailto:bounce-Bert_Scalzo@toadworld.com)] **Sent:** Thursday, March 13, 2014 10:19 AM **To:** [toadoracle@toadworld.com](mailto:toadoracle@toadworld.com) **Subject:** RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition **[RE: Toad for Oracle Base Edition](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45264.aspx#45264)** Reply by Bert Scalzo that’s exactly what I asked/said in my first reply - seems to me that SQL code is hard coding a possible join … To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/21576.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10.aspx?subscribe=false) notifications altogether. Toad for Oracle - Discussion Forum [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45264.aspx?AbuseContentId=477c5ebd-c48d-4b59-b2eb-3847a0ecd08c&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/o/04j0/1cc801f5/33m8i93e.gif) ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 8:26am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/10 "2014-03-13T08:26:14Z")
Bert, I have done that in the past. SELECT a._, b._ FROM table1 a, table2 b WHERE a.type=b.type AND ((A.issuer\_id\_c = ‘62931Z’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’, … up to 1000) OR (A.issuer\_id\_c = ‘145258’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’) OR (A.issuer\_id\_c = ‘685774’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’) OR (A.issuer\_id\_c = ‘469807’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’) OR (A.issuer\_id\_c = ‘778910’ and A.issue\_alpha\_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)) And it only lets me do 5 IN statements (each with up to 1000 id’s) for a total of 5,000. Is this a Toad limit or is my company’s system preventing me from doing this? I would like to be able to query up to 20k. Thanks for the help. ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 8:27am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/11 "2014-03-13T08:27:05Z")
John, The issuer\_id\_c’s and issue\_alpha’s are in table1, along with other info. Thanks for the help ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 8:35am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/12 "2014-03-13T08:35:25Z")
When I try to add more than 1,000 id’s into an IN statement, I get an error about 1,000 being the max. But when I try to query more than 5,000 id’s (ets say 6 IN statements of 1,000 each) my Toad crashes. I don’t get an error messages, just crashes. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 13, 2014, 8:48am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/13 "2014-03-13T08:48:17Z")
Right, they are in table1, but how do you come up with the list of the thousands that you want to select? Bert and I are trying to get you to think of a way to make the creation of that list as part of the query (either from data in another table, or a different where clause condition that doesn’t involve listing out thousands of values). The 1000 “In list” limit is Oracle’s. I am not sure offhand where the 5000 character limit is coming from. **From:** orange99 [[mailto:bounce-orange99@toadworld.com](mailto:bounce-orange99@toadworld.com)] **Sent:** Thursday, March 13, 2014 10:27 AM **To:** [toadoracle@toadworld.com](mailto:toadoracle@toadworld.com) **Subject:** RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition **[RE: Toad for Oracle Base Edition](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45267.aspx#45267)** Reply by orange99 John, The issuer\_id\_c’s and issue\_alpha’s are in table1, along with other info. To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/21576.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10.aspx?subscribe=false) notifications altogether. Toad for Oracle - Discussion Forum [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45267.aspx?AbuseContentId=f5f04cc0-3a83-4e7e-b684-8b58762c1f53&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/o/04j0/cb488ba5/3320vp4e.gif) ---
**Author:** ![avergison](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/avergison/32/66_2.png) [@avergison](https://forums.toadworld.com/u/avergison)\ **Post date:** [March 13, 2014, 10:47am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/14 "2014-03-13T10:47:04Z")
I thought of a parser limit, but that’s not the case: 15 times 1100 entries in the IN( ) parse and format well. So that canbe excluded. ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 13, 2014, 11:02am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/15 "2014-03-13T11:02:45Z")
I just made a very long query (about a half million characters) trying to get an error, but never got one. It did start to slow down at that point but it still worked. So it doesn’t seem like there is any kind of hard coded limit anywhere in Toad. When you say “it crashes”, can you be more specific? Toad does disappear? Does it show an error dialog of any kind? If the error dialog says “to see what the error report contains, click here”, then click on the words “click here”, go to the “call stack” tab in the next dialog, check “Copy to Clipboard”, and then click OK. Then post a message with the contents of your clipboard. ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 11:07am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/16 "2014-03-13T11:07:06Z")
John, No error dialg window. The application just shuts off. One minute it’s running the query, next minute application is closed. Are you using Toad Base Edition or Pro? thanks ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [March 13, 2014, 11:23am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/17 "2014-03-13T11:23:17Z")
Pro, but the license just turns on and off certain windows or features within windows. We don’t limit stuff like this. When an application disappears like that, it usually means that there is a stack overflow somewhere. What numeric version of Toad are you using? **From:** orange99 [[mailto:bounce-orange99@toadworld.com](mailto:bounce-orange99@toadworld.com)] **Sent:** Thursday, March 13, 2014 1:08 PM **To:** [toadoracle@toadworld.com](mailto:toadoracle@toadworld.com) **Subject:** RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition **[RE: Toad for Oracle Base Edition](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45278.aspx#45278)** Reply by orange99 John, No error dialg window. The application just shuts off. One minute it’s running the query, next minute application is closed. Are you using Toad Base Edition or Pro? thanks To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/f/10/t/21576.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - General](http://www.toadworld.com/products/toad-for-oracle/f/10.aspx?subscribe=false) notifications altogether. Toad for Oracle - Discussion Forum [Flag](http://www.toadworld.com/products/toad-for-oracle/f/10/p/21576/45278.aspx?AbuseContentId=1daa9e36-03b9-442a-8816-4221c8dde3d4&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/o/04j0/53f7699f/3342o76e.gif) ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [March 13, 2014, 11:24am UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/18 "2014-03-13T11:24:55Z")
@orange, I’m curious, but how come such a query in real praxis … what is the business philosophy under that? Could you explain. Thx Damir ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 12:00pm UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/19 "2014-03-13T12:00:26Z")
Toad for Oracle Base 11.0.0.116 ---
**Author:** ![orange99](https://avatars.discourse-cdn.com/v4/letter/o/ba8739/32.png) [@orange99](https://forums.toadworld.com/u/orange99)\ **Post date:** [March 13, 2014, 12:01pm UTC](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298/20 "2014-03-13T12:01:11Z")
Hi Damir, I’m not sure what you mean. [Next page](https://forums.toadworld.com/t/toad-for-oracle-base-edition/29298.md?page=2)