Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Connecting to an Oracle database causes Designer to get stuck in "Not Responding"

Alteryx
Alteryx
Created

Environment Details


When trying to make a new Oracle connection using the Input Data tool, Designer freezes up and says, "Not Responding".

 

  • Alteryx Designer
    • All versions
  • Oracle
    • 12c +
    • E-Business Suite


Cause


If the schema being connected to is large with many objects, for example the Oracle APPS schema in the E-Business Suite, the Visual Query Builder will try to pull all tables and views metadata. It's too much overhead for Designer, which will cause Designer to freeze. The issue is more prominent while on VPN and/or working remote.



Resolution


Connect to a different Oracle schema that is smaller or a different database that will not make Designer freeze. In the Visual Query Builder window, click the SQL Editor button and check the box "Open SQL Editor view by default" and click OK. Then make a connection to the large schema and type or paste a SQL query to gather the data needed.

idea Skyscrapers
 

Additional Information

  • For more information on how to build queries without using the Visual Query Builder, click here.
  • How to check how big the Oracle schema is? The query below will list the count of Objects in currently connected schema in Oracle.

 

SELECT owner,
MAX( decode( object_type, 'TABLE', cnt, NULL ) ) TABLES,
MAX( decode( object_type, 'VIEW', cnt, NULL ) ) views,
MAX( decode( object_type, 'TRIGGER', cnt, NULL ) ) TRIGGERS,
MAX( decode( object_type, 'PACKAGE', cnt, NULL ) ) PACKAGES
FROM ( SELECT owner, object_type, count(*) cnt
FROM dba_objects
GROUP BY owner, object_type )
GROUP BY owner