Search code examples
sqloracleoracle-sqldeveloper

How to retrieve data from object tables?

I created object table using SQL Developer:

CREATE TYPE PRODUCT  AS  OBJECT(
name        varchar2(40),
category    varchar2(30),
price       varchar2(10));

CREATE  TABLE  S_PRODUCTS  OF PRODUCT;

SELECT VALUE(e) from S_PRODUCTS e;

Also, inserted several values into that table. But when I am trying to retrieve this object table as a whole, I a getting weird results (see image in the attachments). As far as I understood, the first part, before the dot is my db username.

query results showing nine rows each containing [DB_231RDM023.PRODUCT]

I tried to recreate the object table and restart Oracle SQL Developer, but I am always getting the same results

Solution

  • If you want to see the columns then:

    SELECT * FROM s_products;

    or

    SELECT name, category, price FROM s_products;

    If you want to get the object for each row then use your query:

    SELECT VALUE(e) from S_PRODUCTS e;

    However, your SQL Developer client application is not configured to display object so it is just displaying the identifier for the object. If you want to see the contents of the object then:

    • click on the pencil icon for the cell;
    • double-click on the cell; or
    • Follow the ThatJeffSmith's instructions and check the "Display Struct in Grid" option in the preferences under "Datebase > Advanced".