6 Replies Latest reply on Nov 17, 2017 9:37 PM by JimatNOAA

    Find Database Object - string search

    JimatNOAA

      I have one ID for production and a separate ID for development.  A string search (Code  - Search for %pattern%' checked - All Source Lines checked) works fine using my development ID and connection but produces nothing with my production ID and connection on the very same set of packages.  Is there some permission that I am missing on my production ID?  I'll have to get the systems DBA to compare permission settings if no one knows the answer off hand.  Thanks.

        • 1. Re: Find Database Object - string search
          Gary Graham-Oracle

          Permissions are exactly what control what you can see so, yes, ask your DBA to do the comparison.

          • 2. Re: Find Database Object - string search
            Gary Graham-Oracle

            And, based on past experience, my favorite privileges are

            SELECT_CATALOG_ROLE

            SELECT ANY DICTIONARY

            If the DBA decides to grant you one or both of those in Production, you should be good.

             

            And here is a primer on the difference between the two": http://arup.blogspot.com/2011/07/difference-between-select-any.html

            The Arup Nanda Blog: Difference between Select Any Dictionary and Select_Catalog_Role

            • 3. Re: Find Database Object - string search
              Gary Graham-Oracle

              Update:

              Even when a user is granted both SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE, the Find Database Object feature will sometimes use the ALL_ views rather than the DBA_ views, so that you will not be able to locate an arbitrary string in the code source and the view source across multiple or all schemas.  I will request that the developer look at this.  Here is the query for everything except the view source...

              select NULL ENTRY, inner.* from (
              select 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 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
              ) inner
              where rownum <= 500
              order by case when OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') then ' ' else OWNER end, type, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col, name
              
              • 4. Re: Find Database Object - string search
                JimatNOAA

                Thank you.  

                Separately, awhile back I suggested putting a filter on the schema list or a way to save a sub list.  We have thousands and thousands of schemas but only use three for searching.  Your help is appreciated.   Jim Snyder

                jim.snyder44@yahoo.com

                 

                      From: community-admin <community-prod-admin_ww@oracle.com>

                To: JimatNOAA <jim.snyder44@yahoo.com>

                Sent: Thursday, November 16, 2017 1:24 PM

                Subject: Re: - Find Database Object - string search

                    

                 

                 

                Find Database Object - string search

                created by Gary Graham-Oracle in SQL Developer - View the full discussionUpdate:Even when a user is granted both SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE, the Find Database Object feature will sometimes use the ALL_ views rather than the DBA_ views, so that you will not be able to locate an arbitrary string in the code source and the view source across multiple or all schemas.  I will request that the developer look at this.  Here is the query for everything except the view source...select NULL ENTRY, inner.* from (

                select 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 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

                ) inner

                where rownum <= 500

                order by case when OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') then ' ' else OWNER end, type, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col, name

                Reply to this message by replying to this email -or- go to the message on Oracle Community Start a new discussion in SQL Developer at Oracle CommunityTo Unsubscribe: This message contains information according to the preferences you set in Oracle Community. To modify your settings or to turn off this automated message, log into Oracle Community (http://community.oracle.com), click on the drop down next to your username in the upper right hand corner, and choose preferences. This will allow you to set your email preferences

                 

                  |

                • 5. Re: Find Database Object - string search
                  Gary Graham-Oracle

                  awhile back I suggested putting a filter on the schema list

                  I see that you (or someone) has added this suggestion as a feature request on SQL Developer Exchange (sqldeveloper.oracle.com). No one had voted for it, but I just did with this comment:

                  Implementing a Filter Schemas dialog similar to Schema Browser would be great, or even sorting the connection drop-down list like all other places in 17.3: alphabetically, but with open connections at the top, most recently opened ones first.

                  In fact, it looks like the connection drop-down list sorting actually does work that way -- sometimes.  Not sure what is causing the inconsistency, but when it does work perhaps you can take advantage of it while hoping and waiting for Filter Schema enhancement.

                  • 6. Re: Find Database Object - string search
                    JimatNOAA

                    Thanks, Gary.  Very, very helpful.  I'll use the text part of the union with SYS.DBA_SOURCE to do my searches.  ALL_SOURCE just doesn't get it.  

                     

                    Jim Snyder

                    jim.snyder44@yahoo.com

                     

                          From: community-admin <community-prod-admin_ww@oracle.com>

                    To: JimatNOAA <jim.snyder44@yahoo.com>

                    Sent: Thursday, November 16, 2017 1:24 PM

                    Subject: Re: - Find Database Object - string search

                        

                     

                     

                    Find Database Object - string search

                    created by Gary Graham-Oracle in SQL Developer - View the full discussionUpdate:Even when a user is granted both SELECT ANY DICTIONARY and SELECT_CATALOG_ROLE, the Find Database Object feature will sometimes use the ALL_ views rather than the DBA_ views, so that you will not be able to locate an arbitrary string in the code source and the view source across multiple or all schemas.  I will request that the developer look at this.  Here is the query for everything except the view source...select NULL ENTRY, inner.* from (

                    select 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 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

                    ) inner

                    where rownum <= 500

                    order by case when OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') then ' ' else OWNER end, type, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col, name

                    Reply to this message by replying to this email -or- go to the message on Oracle Community Start a new discussion in SQL Developer at Oracle CommunityTo Unsubscribe: This message contains information according to the preferences you set in Oracle Community. To modify your settings or to turn off this automated message, log into Oracle Community (http://community.oracle.com), click on the drop down next to your username in the upper right hand corner, and choose preferences. This will allow you to set your email preferences

                     

                      |