OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to display the contents of a column of ANYDATA data type in SQL

September 9th, 2007 By Frank Zhou

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  

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question