This is how the table is defined
CREATE TABLE "USER_PREFERENCE"
"USER_PREFERENCE_ID" NUMBER(38,0) NOT NULL ENABLE,
"USER_ID" NUMBER(38,0) NOT NULL ENABLE,
"PREFERENCE" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"VALUE" VARCHAR2(4000 BYTE) NOT NULL ENABLE
The first type of preference I needed to add was a URL link. I defined this in XML
I insert this xml into the value column and "CUSTOMLINK" into the preference column along with the USER_ID and primary key USER_PREFERENCE_ID sequence.
Here is the query I used to retrieve my custom url links. Notice that I'm not actually returning any column from the table. All of the columns returned are coming from the xml nodes in the value column. This query uses a feature of Oracle available since 10.2 (XMLTABLE). You can think of it as a table within a column that the row is being joined to. "url "is the alias for this table containing all of the xml elements as table columns.
FROM USER_PREFERENCE ,
-- XMLTable defines a join with contents of value column treating it like a table
label VARCHAR2(40) PATH 'label',
location VARCHAR2(1025) PATH 'location',
open VARCHAR2(40) PATH 'open',
sortorder VARCHAR2(4) PATH 'sortorder',
permission VARCHAR2(40) PATH 'permission'
) as url
WHERE PREFERENCE = 'CUSTOMLINK'
AND USER_ID = :UserIdBind
order by sortorder
I used a read only view object for this. I could have created a entity based view object and use expert mode to insert similar sql. For storing and committing data in the value column I would need to add custom code to the setters for the 4 xml based columns and there construct the xml containing the values from all of these columns and then set that xml into the value column.
This gives me great flexibility for adding additional columns to my query without making any changes to the data model. Querying XML will probably not perform well if you have a huge result sets but is a great solution for queries that return a small number of rows as this one does.