CODEWITHSUNDEEP
sqloracle-databaseora-00932
Martin08
Martin08

Reputation: 21450

dynamic declaration/query in oracle 9i

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.

Upvotes: 1

Views: 253

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52675

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;

Upvotes: 0

Craig
Craig

Reputation: 5820

Most datatypes will implicitly convert into a VARCHAR. Obviously there are exceptions, but if your tables are just varchars, dates, and numbers then you should be fine.

Upvotes: 1

Related Questions