net.snowflake.client.jdbc.SnowflakeSQLException: The result set size exceeded the max number of rows(10000) supported for SHOW statements:

 

net.snowflake.client.jdbc.SnowflakeSQLException: The result set size exceeded the max number of rows(10000) supported for SHOW statements. Use LIMIT option to limit result set to a smaller number.

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:144)

at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:77)

at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:501)

at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:407)

at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:489)

at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:206)

at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:140)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:776)

at net.snowflake.client.core.SFStatement.execute(SFStatement.java:684)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:267)

at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:137)

at net.snowflake.client.jdbc.SnowflakeDatabaseMetaData.executeAndReturnEmptyResultIfNotFound(SnowflakeDatabaseMetaData.java:3437)

at net.snowflake.client.jdbc.SnowflakeDatabaseMetaData.getTables(SnowflakeDatabaseMetaData.java:1503)

at com.sunopsis.dwg.reverse.Reverse.getTables(Reverse.java:3293)

at com.sunopsis.dwg.reverse.Reverse.getNewTables(Reverse.java:2855)

at com.sunopsis.graphical.frame.edit.EditFrameSnpModel.reinitializeTable(EditFrameSnpModel.java:3642)

at com.sunopsis.graphical.frame.edit.EditFrameSnpModel.jCheckBoxSelectTable_ActionEvents(EditFrameSnpModel.java:3445)

at com.sunopsis.graphical.frame.edit.EditFrameSnpModel$IvjEventHandler.actionPerformed(EditFrameSnpModel.java:355)

at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)

at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)

at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)

at javax.swing.JToggleButton$ToggleButtonModel.setPressed(JToggleButton.java:343)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:262)

at oracle.bali.ewt.olaf2.OracleButtonListener.mouseReleased(OracleButtonListener.java:86)

at java.awt.Component.processMouseEvent(Component.java:6539)

at javax.swing.JComponent.processMouseEvent(JComponent.java:3318)

at java.awt.Component.processEvent(Component.java:6304)

at java.awt.Container.processEvent(Container.java:2239)

at java.awt.Component.dispatchEventImpl(Component.java:4889)

at java.awt.Container.dispatchEventImpl(Container.java:2297)

at java.awt.Component.dispatchEvent(Component.java:4711)

at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4904)

at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4535)

at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4476)

at java.awt.Container.dispatchEventImpl(Container.java:2283)

at java.awt.Window.dispatchEventImpl(Window.java:2746)

at java.awt.Component.dispatchEvent(Component.java:4711)

at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:760)

at java.awt.EventQueue.access$500(EventQueue.java:97)

at java.awt.EventQueue$3.run(EventQueue.java:709)

at java.awt.EventQueue$3.run(EventQueue.java:703)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)

at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:84)

at java.awt.EventQueue$4.run(EventQueue.java:733)

at java.awt.EventQueue$4.run(EventQueue.java:731)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)

at java.awt.EventQueue.dispatchEvent(EventQueue.java:730)

at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)

at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)

at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)

at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)

at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

 

Cause of the Error

• Snowflake has a hard limit of 10,000 rows for SHOW statements
• ODI is trying to retrieve metadata for more than 10,000 tables at once
• The error is triggered by net.snowflake.client.jdbc.SnowflakeSQLException

Solution

To address this issue, follow these steps:

1. Immediate Solution for ODI Reverse Engineering:

— Method 1: Use Information Schema instead of SHOW command

SELECT * FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_SCHEMA = ‘<your_schema_name>’;

 

— Method 2: Filter tables by specific patterns

SHOW TABLES LIKE ‘<pattern>%’ LIMIT 10000;

 

2. Divide the Query Using Filters

     Open your Snowflake model

1. Right-click and select “Properties”
2. Go to “Reverse Engineer” tab
3. Add name filters to limit table selection
4. Break reverse engineering into smaller batches

 

3. Using Table Filters:

a) Add a Name Filter like:

   – TABLE_NAME LIKE ‘A%’ 

   – TABLE_NAME LIKE ‘B%’

   etc.

b) Use Schema Filters:

   – Specify exact schema names

   – Avoid using wildcards for schemas

 

4. Modified Reverse Engineering Process:

1. Select specific schemas instead of entire database
2. Use “Selected Tables” option instead of “All Tables”
3. Reverse engineer in batches of less than 10,000 tables
4. Create multiple models if needed for large databases

 

Long-term Best Practices:

• Organize tables into logical folders
• Create separate models for different business areas
• Use naming conventions to easily filter tables
• Document your reverse engineering strategy

 

Conclusion

This error highlights the importance of managing large result sets in Snowflake and optimizing queries during integration with tools like Oracle Data Integrator. By implementing the solutions above—such as using the LIMIT clause, filtering, and optimizing reverse engineering processes—you can resolve the issue and prevent it from reoccurring. Properly organizing Snowflake objects and setting best practices for query design will ensure smoother operations in the future.

 

For more reference :

LIMIT / FETCH

RESULTSET