Finding the datatype of a cursor or table column in a block

Question

Is is possible to find out the datatype of a column of a cursor or variable within block without using system tables? While I understand that I can use the system tables to find out this information it would be a lot slower.

Something like,

declare
   my_column_data_type varchar2(30);
begin
  my_column_data_type := all_tables.table_name%type;
  dbms_output.put_line(my_column_data_type);
end;

I can't find any way of doing it without resorting to dbms_sql, which would be overkill for my eventual purpose.

But, Oracle already has all the information to hand. If I were to try to assign a varchar2 to a number then it would complain instantly so it knows what the datatypes are.

And, yes I know the number of versions of Oracle are ridiculous but that's the amount we've got at the moment... 9i is dying shortly in favour of 11 but this code'll run on 9i immediately if I can find an answer! But I included 11 as I can wait for a better solution if needs be,

Answer 1

It sounds as if you want a self describing object. Meaning programmatically find the type of a variable without selecting from some metadata view. Just ask the object, what are you?

It seems unnecessary for most situations as in most cases we already know the type (strongly typed). For example, a procedures parameters will typically specify the type (number, varchar2, whatever). Local variables will typically specify the type or tie themselves to a database object type via %type notation.

There are some situations where weakly typed objects are needed or useful, such as a weakly typed cursor variable that can be used for any query. An overly simplistic example:

create or replace procedure get_data(o_cur OUT SYS_REFCURSOR) as
begin
  OPEN o_cur FOR
  -- without changing parameter, this could select from any table
  select * from emp;
end;

Now the problem is that you may have errors (at runtime) if someone codes the cursor to be used with another table (I chose a terrible procedure name on purpose). Something like:

declare
  l_cur sys_refcursor;
  l_row dept%rowtype;
begin
  get_data(l_cur);
  -- oops, I thought this was dept data when I coded it, Oracle didn't complain at compile time
  LOOP
    fetch l_cur
    into l_row;
    exit when l_cur%notfound;
    -- do something here
  END LOOP;
  close l_cur;
end;

This is also why I prefer strongly typed cursors and avoid this situation.

Anyway, in the case of a self-describing object, you can use SYS.ANYDATA built in type (similarly, SYS.ANYDATASET for generic collection types). This was introduced with 9i I believe. For example, this procedure takes some data and branches logic based on the type:

CREATE OR REPLACE procedure doStuffBasedOnType(i_data in sys.anydata) is
  l_type         SYS.ANYTYPE;
  l_typecode     PLS_INTEGER;
begin
  -- test type
  l_typecode := i_data.GetType (l_type);

  CASE l_typecode
  when Dbms_Types.Typecode_NUMBER then
    -- do something with number
    dbms_output.put_line('You gave me a number');

  when  Dbms_Types.TYPECODE_DATE then
    -- do something with date
    dbms_output.put_line('You gave me a date');

  when  Dbms_Types.TYPECODE_VARCHAR2 then
    -- do something with varchar2
    dbms_output.put_line('You gave me a varchar2');

  else
    -- didn't code for this type...
    dbms_output.put_line('wtf?');

  end case;
end;

Here you have your programatic branching based on the type. And to use it:

declare
  l_data sys.anydata;
begin
  l_data := sys.anydata.convertvarchar2('Heres a string');
  doStuffBasedOnType(l_data);
end;

-- output: "You gave me a varchar2"

Hope that wasn't too long winded a response ;)