Returning Partial Results
When selecting the results of a large column, you might want to retrieve only a portion of the result and create a URL to the column instead. For example, consider the case of a travel story Web site. If all the travel stories are stored in a table, and users search for a set of relevant stories, you do not want to list each entire story in the result page. Instead, you show the first 100 characters or gist of the story and then return a URL to the full story.This can be done as follows:
Example 12-19 Returning a Portion of the Results By Creating a View and Using SYS_DBURIGEN()
Assume that the travel story table is defined as follows:
CREATE TABLE travel_story (
story_name varchar2(100), story clob
INSERT INTO travel_story values ('Egypt',"This is my story of how I spent my time in Egypt, with the pyramids in full view from my hotel room');
Now, you create a function that returns only the first 20 characters from the story:
create function charfunc(clobval IN clob ) return varchar2 is res varchar2(20); amount number := 20; begin dbms_lob.read(clobval,amount,1,res); return res; end;
Now, you create a view that selects out only the first 100 characters from the story and then returns a DBUri reference to the story column:
CREATE VIEW travel_view as select story_name, charfunc(story) short_story,
SYS_DBURIGEN(story_name,story,'text()/) story_link FROM travel_story;
Now, a SELECT from the view returns the following: SELECT * FROM travel_view;
STORY NAME SHORT STORY STORY LINK
Egypt This is my story of h
SYS.DBUriType(//PUBLIC/TRAVEL_STORY/ROW[STORY_NAME='Egypt/]/STORY/text()/)
Post a comment