14 Replies Latest reply on May 31, 2019 3:27 PM by Olafur T

    Find database objects - ORDS - SQL Developer

    Olafur T

      Hi,

       

      I can't figure out how to get the ORDS section of "Code" in "Find database Objects" in SQL Developer. I'm guessing it's something really simple that I'm missing.

      I'm using version 19.1.0.094 (but had this problem also in previous versions)

       

      So when I search I only get these options:

      Thanks,

      Olafur,

       

      ps. I know this probably belongs in the SQL Developer forum, but I'm guessing I'll get a quicker response here

        • 1. Re: Find database objects - ORDS - SQL Developer
          thatJeffSmith-Oracle

          SQL Developer doesn't think ORDS is installed on your database.

           

          When you connect, we run this to see what's what

           

           

           

          declare

             l_schema      varchar2(128);

             l_part1       varchar2(128);

             l_part2       varchar2(128);

             l_dblink      varchar2(128);

             l_part1_type  number;

             l_objid       number;

          begin

          DBMS_UTILITY.NAME_RESOLVE (

             name          => :obj_name,

             context       => 2,  -- interested in dba_ views only; 0 doesn't work in 10g -- bug 19528375

             schema        => l_schema, 

             part1         => l_part1,

             part2         => l_part2,

             dblink        => l_dblink,

             part1_type    => l_part1_type,

             object_number => l_objid );

          end;

           

          "obj_name"="user_ords_repoversions"

           

          Do the RESTful Services items show up in your connection tree?

          • 2. Re: Find database objects - ORDS - SQL Developer
            Olafur T

            Yes, I get the REST services for all the databases (4 in total)

             

            I ran your code and checked the output.

             

            declare
              l_schema     varchar2(128);
              l_part1      varchar2(128);
              l_part2      varchar2(128);
              l_dblink     varchar2(128);
              l_part1_type number;
              l_objid      number;
            begin
              DBMS_UTILITY.NAME_RESOLVE(name          => 'user_ords_repoversions',
                                        context       => 2,
                                        schema        => l_schema,
                                        part1         => l_part1,
                                        part2         => l_part2,
                                        dblink        => l_dblink,
                                        part1_type    => l_part1_type,
                                        object_number => l_objid);
            
              dbms_output.put_line(l_schema);
              dbms_output.put_line(l_part1);
              dbms_output.put_line(l_part2);
              dbms_output.put_line(l_dblink);
              dbms_output.put_line(l_part1_type);
              dbms_output.put_line(l_objid);
            

             

            ORDS_METADATA

            ORDS_REPVERSION

             

             

            4

            2995575

            Same for others (except for the object id)

             

            Oli

             

            edit:

             

            From v$version

            BANNER                                                                           CON_ID

            -------------------------------------------------------------------------------- ----------

            Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production          0
            PL/SQL Release 12.1.0.2.0 - Production                                                0
            CORE    12.1.0.2.0    Production                                                              0
            TNS for Solaris: Version 12.1.0.2.0 - Production                                      0
            NLSRTL Version 12.1.0.2.0 - Production                                               

            0

             

            select ords.installed_version 
              from dual
            

             

            18.4.0.r3541002

            • 3. Re: Find database objects - ORDS - SQL Developer
              thatJeffSmith-Oracle

              something else is afoot - the ORDS bits show up in the UI no matter what...have you disabled any features?

              • 4. Re: Find database objects - ORDS - SQL Developer
                Olafur T

                Yes,

                Just to speed up the loading and reducing memory footprint. (stuff I don't use day to day)

                 

                • 5. Re: Find database objects - ORDS - SQL Developer
                  Olafur T

                  I enabled all features and restarted SQL Developer. Still no ORDS..

                  • 6. Re: Find database objects - ORDS - SQL Developer
                    thatJeffSmith-Oracle

                    try renaming your system19.1 folder, this will give you a clean config...if that doesn't work I'll ask the developer to come up with some ideas

                    • 7. Re: Find database objects - ORDS - SQL Developer
                      Olafur T

                      Renamed the system19.1 folder and said "No" to migrate settings from 18.4.

                      Clean setup, connected to one of the databases (I'm using number of schemas, just chose one)

                       

                      Still no ORDS in find database objects

                       

                      • 8. Re: Find database objects - ORDS - SQL Developer
                        Olafur T

                        Should I open an SR for this?

                         

                        Regards

                        Oli

                        • 9. Re: Find database objects - ORDS - SQL Developer
                          thatJeffSmith-Oracle

                          I pinged the developer for ideas, stay tuned

                          • 10. Re: Find database objects - ORDS - SQL Developer
                            Vadim Tropashko-Oracle

                            When switching connection Db Search repopulates the list of schemas via the query:

                             

                            SELECT USERNAME FROM SYS.ALL_USERS ORDER BY USERNAME

                             

                            If ORDS_METADATA is present, then the Code->ORDS checkbox is added.

                             

                            Olafur, is ORDS_METADATA listed under the All Schemas tree node?

                            • 11. Re: Find database objects - ORDS - SQL Developer
                              Olafur T

                              Hi,

                               

                              Yes, it's there, but still no ORDS in search.

                              So it must be some other check.

                               

                              Regards

                               

                              ps. These databases are pretty old, having been upgraded over the years from version 7, so this could be a legacy dictionary issue,

                              • 12. Re: Find database objects - ORDS - SQL Developer
                                Vadim Tropashko-Oracle

                                Two more things to check.

                                 

                                1. Is it really

                                 

                                SELECT USERNAME FROM SYS.ALL_USERS ORDER BY USERNAME

                                 

                                that DB Search is executing? Please check the statement log as described here: https://www.thatjeffsmith.com/archive/2014/12/sql-developer-4-1-log-all-the-queries/ .

                                 

                                2. Is there ORDS_METADATA when you expand All Schemas tree node at the DB Search panel:

                                 

                                finddbobject.png

                                • 13. Re: Find database objects - ORDS - SQL Developer
                                  Olafur T

                                  Hi,

                                  Looks like it.

                                   

                                  There is ords_metadata under "All schemas" search and not under code

                                   

                                  One thing to add, I was running into constant "Your Database Connection has been reset....", so much that SQL Developer was totally unusable.  I managed to fix this by adding "AddVMOption -Doracle.net.disableOob=true" to a few .conf files and forcing OCI usage:( Doc ID 2367186.1 )

                                  It's the only configuration that allows me to use SQL Developer.

                                  Regards

                                  Oli

                                  • 14. Re: Find database objects - ORDS - SQL Developer
                                    Olafur T

                                    Could it be connected to number of schemas? There are quite a few

                                     

                                     

                                    Other thing, I ran over the statements run when connecting to a database.

                                    This one breaks since that view doesn't have the column "all_shard": select user_id from user_users where all_shard = 'YES'

                                    This one breaks also: select service_id from (select sys_context('USERENV', 'CLOUD_SERVICE') as service_id from dual) where service_id = 'DWCS'  "invalid userenv parameter".

                                     

                                    Then a few obvious missing rights queries like "select 1 from sys.obj$ where 1=0".

                                     

                                    I connected to around 5 different databases with different versions and the only one that gave me the "ORDS" search was my 18c Express Edition for Linux x64 instance.

                                     

                                    Oli