Dealing with a complex data type?
Say, XML?
Or, a nested table?
If you want to see a ‘flat’ view of the data for that bit or a row when browsing a data set that contains that TYPE of data, you’ll want to check this preference.
Using the code sample from OraFaq – thanks folks! – here’s what I mean.
But, if we double-click on the cell…
And after that click…
If we ran this as a SCRIPT (SQL*Plus) Mode…
From the SQL Developer HELP:
Display Struct Value in Grid: Controls how unstructured data types are displayed in the table Data grid. For example, if this option is checked, Oracle Spatial and Graph geometry data is displayed with the full SDO_GEOMETRY definition; but if this option is not checked, each Oracle Spatial and Graph geometry object is displayed as just [MDSYS.SDO_GEOMETRY]
Structure Data In Grid Option ON
Just one less click, we’ll show you the data, in-line, in the cell. It might be hard to read, or go off-screen, so you can still use the single record viewer.
And SQLcl?
Again, don’t use ANSICONSOLE, and you’ll be fine.
3 Comments
Hi Jeff,
I do like Object Types. But I actually also do like the way Pl/Sql developer (from Allround Automation) handles them. If I had a table with an object type column, Pl/Sql Developer noticed this and split-out the attributes of the object type and showed each attribute as a separate grid-column.
I could type a select statement that does this (with the dot notation) but it would be nice if SQLDeveloper could do this for me by default when issueing a select * from table.
Regards,
Martien
Thanks Jeff.
Unfortunately not quite what I had in mind.
That preference just changes what you see in the grid from:
“ERO.ADDRESS_TAB([ERO.ADDRESS_T],[ERO.ADDRESS_T])”
into:
“ERO.ADDRESS_TAB(ERO.ADDRESS_T(‘101 First’,’Redwood Shores’,’CA’,’94065′),ERO.ADDRESS_T(‘123 Maple’,’Mill Valley’,’CA’,’90952′))”
And when you hit the pencil the stuff just looks like:
ERO.ADDRESS_T(‘101 First’,’Redwood Shores’,’CA’,’94065′)
ERO.ADDRESS_T(‘123 Maple’,’Mill Valley’,’CA’,’90952′)
My issue with the above is: What the *** is that ‘90952’ value?
Of course, given the type of data, and knowing a bit about addresses in the US, in this case I can guess.
But suppose what you get is this:
“ERO.MY_TAB(ERO.MY_OBJ(‘43214123443′,7534241617,’hdbh-52461/099022’))”
Now please tell me what that third value is?
The only way to find out is to look up the attributes of the object type.
It’s like doing a select from a table, getting a comma separated list of values for every record and then having to look up the table definition to figure out which value belongs to which column by counting the values and the columns.
For my current client I have built a pipelined table function returning levels of nested collections.
The testers need to check if the function returns the correct data, so they execute this function by doing a select * from table(myfunction);
Now they’re looking at these strings of csv-data and need to learn about nested tables and object types and have to count attributes and….
Disclaimer: the next remark is not because I think you should make your tool look or behave like some other tool, it’s just to point out a different way of presenting this data.
In plsql developer a field in the grid that is in fact a collection just shows ” and an edit button.
When you hit the edit button, just like sql developer, a new window opens.
Only in plsql developer this window contains a new grid with a column for each attribute of the object type including attribute names as column headers.
In my humble opinion that is a more user friendly way of displaying this kind of data.
you’re describing why i’m not a fan of this data structure to begin with