Using
the ANYDATA syntax
This trick deals with
using ANYDATA for
non-formatted data
retrieval. Let me first
say that the methods I’m
about to show you are
NOT useful for where
clauses or in normal
database design.
However, they can be
very useful when you
need a reference table
that can store anything
in a simple column.
If we were trying to
duplicate a
non-columnar-format
search (like Google)
with Oracle, we’d
somehow need to create a
table with an ID and a
"token", the token being
the word that was parsed
out of a complete
document. However, what
if we want that column
to be able to store
numbers, characters,
dates…and we want them
to act natively?
Using
Oracle ANYDATA
ANYDATA is a “self
describing data instance
type.” All this means is
that the type not only
holds a value, but it
also holds its own
datatype within it. It
also employs many
methods that allow us to
perform various
functions with it.
So, back to our
parser! We’ll start off
with a very simple
table:
create table
search_words
(
token_id number not null primary key,
token sys.anydata
);
Now we must populate
the table, but normal
inserts wont work. We
must use ANYDATA’s
built-in Convert methods
to perform our inserts.
We’ll use the phrase
“Make 50 dollars on
12/01/06″ as our ANYDATA
tokens, with an integer
for each word in the
phrase.
Note that each row is
assigned a different
datatype upon insert, as
follows:
Make
= varchar2
50
= number
dollars
= varchar2
on
= varchar2
12/01/2006 = date
Hence, we have a
self-defining datatype
column, where any rows
can be it's own
datatype. Of
course, we could also
create our own unique
datatypes, making this
an amazing technique for
free-form tables:
INSERT INTO SEARCH_WORDS
VALUES (1, sys.anydata.convertVarchar2('Make');
INSERT INTO SEARCH_WORDS
VALUES (2, sys.anydata.convertNumber(50);
INSERT INTO SEARCH_WORDS
VALUES (3, sys.anydata.convertVarchar2('dollars');
INSERT INTO SEARCH_WORDS
VALUES (4, sys.anydata.convertVarchar2('on');
INSERT INTO SEARCH_WORDS
VALUES (5, sys.anydata.convertDate(to_date('12/01/2006', 'MM/DD/YYYY');
commit;
Now that we have our
tokenized our data in
the Oracle table, let’s
query the ANYDATA table
and see our "token"
column. Note that
we use the token()
syntax:
SQL> select * from search_words; TOKEN_ID TOKEN()
---------- --------------------
1 ANYDATA()
2 ANYDATA()
3 ANYDATA()
4 ANYDATA()
5 ANYDATA()
Oops! That’s not how
we want our data to
look. Unfortunately,
Oracle is not yet
sophisticated enough to
understand which
datatype it is querying.
Because of this, we have
to make a function
called getTokens to get
the datatype name and
transform it into it's
proper "traditional"
datatype.
CREATE OR REPLACE FUNCTION
getTokens
(pv_token in sys.anydata)
RETURN VARCHAR2 IS
lv_number NUMBER;
lv_date DATE;
lv_varchar2 VARCHAR2(4000);
BEGIN
CASE pv_token.getTypeName
WHEN 'SYS.NUMBER' THEN
IF (pv_token.getNumber(lv_number) = dbms_types.success) THEN
lv_varchar2 := lv_number;
END IF;
WHEN 'SYS.DATE' THEN
IF (pv_token.getDate(lv_date) = dbms_types.success) THEN
lv_varchar2 := lv_date;
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (pv_token.getVarchar2(lv_varchar2) = dbms_types.success) THENa
NULL;
END IF;
ELSE
lv_varchar2 := 'unknown datatype';
END CASE;
RETURN lv_varchar2;
END getTokens;
Notice that we used
the ANYDATA methods
again, but this time
with getVarchar2,
getNumber, and getDate.
Finally, let’s go ahead
and query the table
using the getTokens
datatype translation
function that we
created.
SQL> column token format a20
SQL> select token_id, getTokens(token) token from search_words;
TOKEN_ID TOKEN
---------- --------------------
1 Make
2 50
3 dollars
4 on
5 01-DEC-06
And we have our
data! Since we have to
use a function, you can
see why this would not
be very good in a
standard SQL WHERE
clause. However, for
storing multiple
datatypes in a single
table, it’s tops.
You can find more
information about
ANYDATA in the
Database Application
Developer’s Guide,
and information about
its methods in the
PL/SQL Packages and
Types Reference.
