5 Replies Latest reply on Oct 4, 2014 6:25 PM by Gary Graham-Oracle

    Why can I not see my data in the data pane when using a newly created schema?

    2766380

      I am trying to learn Oracle DB at home. I installed Oracle DB Express 11.2 and SQL Developer 4.0.3. I can connect to the database, so communication between SQL Developer and Oracle DB Express works well.

       

      This is probably an Oracle database problem, but since I see it in SQL Developer, I’ll ask it anyway.

       

      If I log into the demo HR schema (as per the 2-day developer document), and create a new table called OFFICERS with some fictional characters for data, and populate it with the data via the data pane for HR.OFFICERS, I can see it in the SQL Developer table detail table in the data pane tab. I see the columns headers for the columns I created on top, then row numbers for each row I created and the data in each row.

       

      Now I created my own personal schema, e.g. MYNAME, and then connected to that schema and attempted to repeat the process of above. I can create the table with the columns using the New Table wizard. But once the table exists, and I go to the data pane tab on the Table detail, I see nothing. No column headers. When I hit the Insert Row button in the data pane tab, I see a +1 appear, but I have nothing to click on to enter data like I did with HR.OFFICERS.

       

      Now if I go to the SQL Worksheet for the troublesome MYNAME schema, and do some INSERTs, COMMIT, and a query, e.g.


      INSERT INTO OFFICERS VALUES (‘Captain’, ‘Smith’, ‘Samuel’);
      INSERT INTO OFFICERS VALUES (‘Lieutenant Commander’, ‘Jones’, ‘Robert’);
      COMMIT WORK;
      SELECT * FROM PEOPLE;

       

      I get in the Query Result window:

       

      RANK                 LAST  FIRST
      -------------------- ----- ------
      Captain              Smith Samuel
      Lieutenant Commander Jones Robert

       

      But if I go to the Table detail tab for MYNAME.OFFICERS, and look at the data pane, all I see is two row numbers for 1 and 2. No column headers and no data. Just the row numbers.

      Why can I see the data in the data pane of the HR.OFFICERS table detail tab, but I cannot see the data or column headers in the data pane of MYNAME.OFFICERS? I can see the columns fine in the columns pane, but no data in the data pane.

       

      I've looked at all the privileges I know to look at, and MYNAME has at least the same privileges as HR. Any thoughts?

       

      Thank you in advance.

        • 1. Re: Why can I not see my data in the data pane when using a newly created schema?

          Why can I see the data in the data pane of the HR.OFFICERS table detail tab, but I cannot see the data or column headers in the data pane of MYNAME.OFFICERS? I can see the columns fine in the columns pane, but no data in the data pane.

          Did you COMMIT the data after you inserted it?

           

          A session can NOT see uncommitted data that belongs to another session.

          • 2. Re: Re: Why can I not see my data in the data pane when using a newly created schema?
            2766380

            Yes, I had committed the work, as shown above. But thank you for taking the time to look at the problem. Also, in my example above, the SELECT line was wrong, it should have read:

             

            INSERT INTO OFFICERS VALUES (‘Captain’, ‘Smith’, ‘Samuel’);

            INSERT INTO OFFICERS VALUES (‘Lieutenant Commander’, ‘Jones’, ‘Robert’);

            COMMIT WORK;

            SELECT * FROM OFFICERS;


            But this still made no difference.


            After having some time to play with it, I finally figured it out. I was going through the 2 day DBA document, and creating an example user “NICK”. When I had used “MYNAME” in the example above, I had actually made the user’s name in the format of “SMITH.NICK” (but actually another name). And I was not seeing data as detailed in my original post. When I created “NICK”, everything worked properly. So I tried creating the user “SMITH_NICK”, and lo and behold, it worked fine!

             

            Evidently SQL Developer 4.0.3 cannot show data in the data pane if the user name has periods in it. The SQL Worksheet can use the user name with the periods (as long as I put it in double quotes), just not the data pane.

             

            Is there a rule, recommendation, etc. somewhere documenting the format for a user/schema name?

             

            Thanks to all who looked at this, and hopefully it will help somebody else learning Oracle DB.

            • 3. Re: Why can I not see my data in the data pane when using a newly created schema?
              Jim Smith

              Periods are used to separate the components of objects names so they are not valid within names.

               

              e.g.

              schema.table

              table.column

              schema.table.column

              package.procedure

               

              Syntax rules for identifiers are in the SQL Language Reference Manual http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements008.htm#SQLRF00223

              • 4. Re: Why can I not see my data in the data pane when using a newly created schema?

                Then please mark the thread ANSWERED.

                • 5. Re: Why can I not see my data in the data pane when using a newly created schema?
                  Gary Graham-Oracle

                  When creating user SMITH.NICK you must have enclosed the name in double quotes, otherwise you will get...

                  SQL Error: ORA-01936: cannot specify owner when creating users or roles

                  01936. 00000 -  "cannot specify owner when creating users or roles"

                  *Cause:    Users and roles do not have owners.

                  *Action:   Don't specify an owner.

                  It's worthwhile repeating some advice from the doc that Jim Smith references above:

                  Note:

                  Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects.

                  So although what tripped you up was not best practice, it should work and I logged a bug.

                   

                  Regards,
                  Gary

                  SQL Developer Team