OraQA

The following two SQL patterns can be used to display the contents of a column of ANYDATA data type in a single SQL statement.

create table test (input anydata);
insert into test values (anydata.convertnumber(10));
insert into test values (anydata.convertvarchar2('testing'));
insert into test values (anydata.ConvertDate(sysdate));

——————-SQL Solution 1 ——————————–

SELECT CASE WHEN anydata.gettypename(input) = 'SYS.VARCHAR2'
	    THEN anydata.AccessVarchar2(input)
	    WHEN anydata.gettypename(input) = 'SYS.NUMBER'
            THEN CAST(anydata.accessnumber(input) as varchar2(4000))
	    WHEN anydata.gettypename(input) = 'SYS.DATE'
            THEN CAST(anydata.AccessDate(input) as varchar2(4000))
       END AS data
FROM test;

DATA
---------------------
10
testing
07-SEP-07

——————-SQL Solution 2 ———————

SELECT CASE WHEN anydata.gettypename(input) = 'SYS.VARCHAR2'
	    THEN  anydata.AccessVarchar2(input)
       END  str,
       CASE WHEN anydata.gettypename(input) = 'SYS.NUMBER'
            THEN anydata.accessnumber(input)
       END as num ,
       CASE WHEN anydata.gettypename(input) = 'SYS.DATE'
            THEN anydata.AccessDate(input)
       END  day
FROM test;

STR             NUM          DAY
---------      ----------    -----------
               10
testing
                             07-SEP-07

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.