DBA Tips Archive for Oracle



by Jeff Hunter, Sr. Database Administrator


  1. Overview
  2. A Simple Example


With the introduction of Oracle9i, developers can now take advantage of three new special SQL datatypes that enable applications to store generic attributes - attributes for which you do not know what the datatypes are until you actually execute the code. Here is a brief description of these new SQL Datatypes:

In the past, developers would have simply stored their generic data in a VARCHAR2 - dates, numbers, everything. Now, with the introduction of ANYDATA, developers can put in a date and it stays a date. The system will enforce that it is, in fact, a valid date and allow you to perform date operations on it.

Using the previous scenario where the developer decides to store the same date in the VARCHAR2, there is no way to stop another developer from storing the string "Linux" in your "date" field.

A Simple Example

The following is a simple example of how to get started in using the SYS.ANYDATA type:

  create table test_any(id number, value sys.anydata);

  SQL> desc test_any 
  Name              Null?    Type 
  ----------------- ----- ---------------------------- 
  ID                      NUMBER 
  VALUE                   ANYDATA

  INSERT INTO test_any VALUES(1, sys.anydata.convertNumber(5));
  INSERT INTO test_any VALUES(2, sys.anydata.convertVarchar2('Oracle / Linux Application'));
  INSERT INTO test_any VALUES(3, sys.anydata.convertDate('14-FEB-1994'));

There are many SYS.ANYDATA type has many methods, one of which is getTypeName. This method provides the ability to see what kind of data is actually being stored in the SYS.ANYDATA type:

      id Id
    , t.value.getTypeName() TypeName
  FROM test_any t;

  -----  ------------
      2  SYS.NUMBER
      3  SYS.VARCHAR2
      4  SYS.DATE

Using SQL*Plus to display the contents of ANYDATA in a query is not so easy. The following PL/SQL function (getData) rectifies this problem and also gives you an insight into several more methods provided with the SYS.ANYDATA type.

      l_varchar2   VARCHAR2(4000);
      l_rc         NUMBER;

      CASE data.getTypeName
        when 'SYS.NUMBER' then
          l_rc := data.getNumber(l_varchar2);
        when 'SYS.DATE' then
          l_rc := data.getDate(l_varchar2);
        when 'SYS.VARCHAR2' then
          l_rc := data.getVarchar2(l_varchar2);
          l_varchar2 := '** unknown **';
      END CASE;

      RETURN l_varchar2;


  SELECT id, getData(value) GETDATA FROM   test_any;

       ID  GETDATA
  -------  -----------------------------------------------------
        2  5
        3  Testing Application
        4  14-FEB-94

Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 11-Mar-2006 17:19:41 EST
Page Count: 13120