Ora-20000 Oracle Text Error Drg-51030
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Fixing wildcard expansion resulting in too many terms error (DRG-51030) up vote 2 down vote favorite How can I resolve the wildcard_maxterm problem in Oracle 10g? My index creation syntax is: begin ctx_ddl.drop_preference('SUBSTRING_PREF'); end; / begin ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE'); end; / begin ctx_ddl.drop_preference('bus_obj1_lexer'); end; / begin ctx_ddl.create_preference('bus_obj1_lexer','BASIC_LEXER'); ctx_ddl.set_attribute('bus_obj1_lexer', 'base_letter', 'YES'); ctx_ddl.set_attribute('bus_obj1_lexer', 'mixed_case', 'YES' ); ctx_ddl.set_attribute('bus_obj1_lexer','printjoins', ';,@_'); ctx_ddl.set_attribute('bus_obj1_lexer','skipjoins', '-'); end; / create index BUS_OBJ_FTS_Name on BUSINESS_OBJECTS1(name) indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 128M DATASTORE CTXSYS.DEFAULT_DATASTORE SECTION GROUP CTXSYS.AUTO_SECTION_GROUP STOPLIST CTXSYS.EMPTY_STOPLIST LEXER bus_obj1_lexer SYNC (ON COMMIT)'); And my query is: select * from BUSINESS_OBJECTS1 where contains(name,'%WIN%')>0 and rownum<=100; There are 15 million rows in that table, and more than 50000 rows match that query. I have set wildcard_maxterm=50000 and default=20000 but I'm still getting this error: DRG-51030: wildcard query expansion resulted in too many terms. Can anybody hep me how to solve this error? oracle oracle-10g full-text-search share|improve this question edited Jul 24 '13 at 10:14 Mat 6,57622234 asked Jul 24 '13 at 10:04 riadkhan 1114 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote The wildcard_maxterm is set to the maximum. Actually, the maxim
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Oracle indexes and selects with special characters give ORA-29902 Want to Advertise Here? Solved Oracle indexes and selects with special characters give ORA-29902 Posted on 2011-03-18 Oracle Database 1 Verified Solution 25 Comments 3,040 Views http://dba.stackexchange.com/questions/46913/fixing-wildcard-expansion-resulting-in-too-many-terms-error-drg-51030 Last Modified: 2013-12-07 Hi, I need help to build the correct index for a table. Simplified scenario - we have MySchema.MyTable - MyTable has an index on the field PRODUCT_DESCR, which is a VARCHAR2(2000) field. The index is: CREATE INDEX MYSCHEMA.IDX_PRODUCT_DESCR ON MYSCHEMA.MYTABLE (PRODUCT_DESCR) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC(ON COMMIT)') NOPARALLEL; - the following select: SELECT * FROM MyTable WHERE CONTAINS( PRODUCT_DESCR, '%shoes*%', 1) > 0 leads https://www.experts-exchange.com/questions/26896563/Oracle-indexes-and-selects-with-special-characters-give-ORA-29902.html to this error: ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: Oracle Text error: DRG-50901: text query parser syntax error on line 1, column 6 - the following select: select * from MyTable where contains( short_desc, '%sko,%', 1)>0 leads to this error: ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: Oracle Text error: DRG-51030: wildcard query expansion resulted in too many terms ORA-06512 Question How should I modify IDX_PRODUCT_DESCR so that the selects above work correctly? A stupid question maybe, but I am newbie in PL/SQL, and index seems difficult to me. Thank you in advance. 0 Question by:kathysmith Facebook Twitter LinkedIn Google LVL 76 Active today Best Solution byslightwv (䄆 Netminder) The DRG-51030: wildcard query expansion is caused my not setting the wildcard max terms. I forget the exact parameter but it's in the docs. I still also think the printjoins will not do what you want Go to Solution 25 Comments LVL 51 Overall: Level 51 Oracle Database 13 Message Expert Comment by:HainKurt2011-03-18 try SELECT * FROM MyTable WHERE CONTAINS( PRODUCT_DESCR, 'shoes', 1) > 0 0 LVL 51 Overall: Level 51 Oracle Database 13 Message Expert Comment by:HainKurt2011-03-18 have a look at th
This Site Careers Other all forums Forum: Oracle/OAS Oracle Error Ram Sudheer Mandava Greenhorn https://coderanch.com/t/508063/Oracle-OAS/Oracle-Error Posts: 22 posted 6 years ago Dear All, I am getting following error while I try to connect to the Oracle database through my web application Exception Debug: failed sql: insert into view_elements (view_id, element_type, element_id, position, session_id) select 2605459, 'UOI', uoi_id, rownum, 1107006 from ora-20000 oracle ( select ui.uoi_id from ( select distinct t0.uoi_id, t0.import_dt b0 from uois t0, ( select uoi_id, s from ( select uoi_id, score(0) s from uois_fulltext_metadata where contains (all_metadata_index, ?, 0) > 0)) t1, wml_images_skus_vw t2, wml_images_skus_vw t3, security_policy_permissions t4, security_policy_uois t5, user_group_users t6 where (t0.IS_LATEST_VERSION = ora-20000 oracle text ? and (t0.CONTENT_STATE != ? or t0.CONTENT_STATE is null) and (t2.PRODCAT != ? or t2.PRODCAT is null) and t3.BRAND = ? and (t4.user_group_id = t6.user_group_id AND t6.user_id = '381' and t4.asset_view_perm = 'Y' )) and t1.uoi_id = t0.uoi_id and t2.uoi_id = t0.uoi_id and t3.uoi_id = t0.uoi_id and t5.sec_policy_id = t4.sec_policy_id and t5.uoi_id = t0.uoi_id ) ui order by ui.b0 desc) where rownum <= 2000 Exception Message Id: message.error.search.database.operation Exception Message: Database error processing search. Exception Data: none Exception Failed IDs: none Exception Stack Trace: com.artesia.common.exception.BaseTeamsException: Database error processing search. -------- ------ Caused by: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: Oracle Text error: DRG-51030: wildcard query expansion resulted in too many terms at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2321) at oracle.jdbc.oci8.OCIDBAccess.executeFetch(OCIDBAccess.java:1741) at oracle.jdbc.oci8.OCIDBAccess.parseExecuteFetch(OCIDBAccess.java:1902) at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047) at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589) at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:83) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:316) at com.artesia.server.search.entity