sql - dynamic declaration/query in oracle 9i -

in oracle, given list of table names, want perform 'select column1 var1 table' statements on mass number of tables. , want columns of table. cannot declare type of var1 until query user_tab_columns returns column's type. tried declare var1 sys.anytype got ora-00932 error message such "inconsistent datatypes: expected char got char".

so how can past error or how can dynamically declare variable? many thanks.

craig right should declare varchar2 instad of anytype.

this article jeff hunter has nice function makes easy populate variable in way won't break if data can't converted.

create or replace function getdata(data in sys.anydata)     return varchar2           l_varchar2   varchar2(4000);       l_rc         number;     begin        case data.gettypename         when 'sys.number'           l_rc := data.getnumber(l_varchar2);         when 'sys.date'           l_rc := data.getdate(l_varchar2);         when 'sys.varchar2'           l_rc := data.getvarchar2(l_varchar2);         else           l_varchar2 := '** unknown **';       end case;        return l_varchar2;      end;