2 Replies Latest reply on Oct 10, 2017 5:46 PM by Raj Jamadagni

    V17.2 Find database object fails with ORA-942

    Raj Jamadagni

      When i invoke a "Find Database Object" with a table that know exists, it fails to return any data. The 'logging page' shows this ora-942 error.

       

      SEVERE1440oracle.dbtools.parser.plsql.DictionaryQueryORA-00942: table or view does not exist
      SEVERE14310218oracle.dbtools.parser.plsql.DictionaryQueryselect null name, -1 LINE, -1 COL, null USAGE, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end TYPE, OWNER, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end OBJECT_TYPE, obj.OBJECT_NAME from sys.dba_objects obj where rownum <= 500 and obj.object_type != 'TABLE PARTITION' and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS' and object_name like :object_name union all select null name, -1 LINE, -1 COL, null USAGE, 'MATERIALIZED VIEW LOG' TYPE, log_owner OWNER, 'MATERIALIZED VIEW LOG' OBJECT_TYPE, master||'('||LOG_TABLE||')' OBJECT_NAME from all_snapshot_logs where rownum <= 500 and master like :master union select null name, -1 LINE, -1 COL, null USAGE, 'DATABASE LINK' TYPE, owner OWNER, 'DATABASE LINK' OBJECT_TYPE, db_link OBJECT_NAME from dba_db_links where rownum <= 500 and db_link like :db_link union all select name, LINE, COL, USAGE, TYPE, OWNER, OBJECT_TYPE, OBJECT_NAME from sys.all_identifiers where rownum <= 500 and name like :name union all select c.column_name name, -1 LINE, -1 COL, null USAGE, 'COLUMN' TYPE, c.OWNER, o.object_type OBJECT_TYPE, c.table_NAME OBJECT_NAME from sys.all_tab_columns c, all_objects o where c.table_name=o.object_name and c.owner = o.owner and rownum <= 500 and o.object_type != 'TABLE PARTITION' and o.object_type != 'TABLE SUBPARTITION' and c.column_name like :colname union all select text name, LINE, instr(upper(text),upper(:text)) COL, null USAGE, TYPE, OWNER, type OBJECT_TYPE, name OBJECT_NAME from sys.all_source where rownum <= 500 and upper(text) like upper(:text1) and name not like 'BIN$%' union all select DBMS_LOB.SUBSTR(source,32000,1) name, 1 LINE, instr(upper(source),upper(:text)) COL, h.method USAGE, 'ORDS_SRC' TYPE, s.parsing_schema OWNER, source_type OBJECT_TYPE, t.uri_template ||'->'|| m.name OBJECT_NAME from ords_metadata.user_ords_handlers h, ords_metadata.user_ords_templates t, ords_metadata.user_ords_modules m, ords_metadata.user_ords_schemas s where rownum <= 500 and t.id = h.template_id and t.module_id = m.id and m.schema_id = s.id and upper(source) like upper(:text1) union all select null name, -1 LINE, -1 COL, null USAGE, case when obj.type like 'JAVA%' then 'JAVA' else obj.type end TYPE, OWNER, case when obj.type like 'JAVA%' then 'JAVA' else obj.type end OBJECT_TYPE, obj.name OBJECT_NAME from sys.dba_dependencies obj where rownum <= 500 and obj.type != 'TABLE PARTITION' and obj.type != 'TABLE SUBPARTITION' and obj.type != 'JAVA CLASS' and referenced_name like :referenced_name

       

      The error is on the highlighted line when i try to run this sql in worksheet. I am logged in with DBA role, any idea how to suppress this ORDS part? if ORDS part is removed, it work fine in the worksheet.

       

      Useful info

      Oracle SQL Developer 17.2.0.188
      Version 17.2.0.188
      Build 188.1159
      IDE Version: 13.0.0.1.42.170225.0201
      Product ID: oracle.sqldeveloper
      Product Version: 17.2.0.188.1159
      Component Version
      Oracle IDE 17.2.0.188.1159
      Java(TM) Platform 1.8.0_131
      Versioning Support 17.2.0.188.1159
      DB Version: 12.2.0.1.0