dynamic declaration/query in oracle 9i

Question

In Oracle, given a list of table names, I want to perform 'select column1 into var1 from table' statements on a mass number of tables. And I want to do this for all columns of a table. I cannot declare the type of var1 until the query with user_tab_columns returns the column's type. I tried to declare var1 as sys.anytype but got ORA-00932 with error message such as "inconsistent datatypes: expected CHAR got CHAR".

So how can I get past this error or how can I dynamically declare a variable? Many thanks.

Answer 1

Craig is right you should probably declare it as a VARCHAR2 instad of an anytype.

This article by Jeff Hunter has a nice function that makes this easy to populate your variable in way that won't break if your data can't be converted.

CREATE OR REPLACE FUNCTION getData(data IN SYS.ANYDATA)
    RETURN VARCHAR2
    AS
      l_varchar2   VARCHAR2(4000);
      l_rc         NUMBER;
    BEGIN

      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);
        else
          l_varchar2 := '** unknown **';
      END CASE;

      RETURN l_varchar2;

    END;