Database Resources
- RDBMS Server
- Database Administration
- Performance Tuning
- Backup and Recovery
- Oracle Utilities
- SQL and PLSQL
- Network Administration
- Client Applications
- Miscellaneous Stuff

Oracle's technology Sites

http://technet.oracle.com

http://metalink.oracle.com

http://asktom.oracle.com/

http://www.oramag.com

Category     : Database Resources -> SQL and PLSQL

DB Version  : Oracle 9i

OS Details    : Sun Solaris9

Oracle9i has introduced a new datatype called anydata which represents dynamically Typed Data.

This new feature is useful for applications that do not know the actual datatypes until runtime or to check the datatype for an input value. It can also be used when building common PLSQL routines an example of which can be a partition maintenance routine that handles all partition related tasks.

Example:

-- Create temporary table to hold data

create global temporary table partition_values(list_values sys.anydata);

-- Populate the temporary table

insert into t values ( sys.anyData.convertNumber(16));

insert into t values (sys.anyData.convertVarchar2('ABCDEFGH') );

-- Function to interpret the data

create or replace function getcolumnData( inputcolumn in sys.anyData ) 
return varchar2
as
l_num number;
l_date date;
l_varchar2 varchar2(4000);
begin
   case inputcolumn.gettypeName
        when 'SYS.NUMBER' then
                if ( inputcolumn.getNumber( l_num ) = 
dbms_types.success )
                  then
                        l_varchar2 := l_num;
                end if;
        when 'SYS.DATE' then
                if ( inputcolumn.getDate( l_date ) = dbms_types.success 
)
                  then
                        l_varchar2 := l_date;
                end if;
        when 'SYS.VARCHAR2' then
                if ( inputcolumn.getVarchar2( l_varchar2 ) = 
dbms_types.success )
                  then
                        null;
                end if;
        else
                l_varchar2 := 'unknown datatype';
        end case;
return l_varchar2;
end;
/

-- Use the function to return the data as appropriate data types

select distinct getcolumndata(list_values) from partition_values;