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

Asked 9/1, 2012 at 14:44 Answered 10/1, 2012 at 12:46

Solved oracle-database plsql oracle11g oracle9i oracle11gr2

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,

Persimmon answered 9/1, 2012 at 14:44 Comment(8)

Not sure where the value comes from this. You know the column name, so how hard would it be to also know the column data type? – Retardant 9/1, 2012 at 16:31

unless i misunderstand the question, isn't that what the %type is for? So your local variable declaration would be: l_var my_table.my_column%type; Now when I select my_column into l_var from my_table where... I don't have an issue, will be same type – Doubling 9/1, 2012 at 16:39

@tbone, you do. I'm looking to find the actual data type; not create another variable with the same type. It sounds weird, I know, but I need to do different things depending on whether it's a char or a number and this would simplify changing the package enormously ( I'd barely have to ). – Persimmon 9/1, 2012 at 17:46

+1 for tbone.. Couldn't agree more. Use the %type for all the variables and then you don't have to care. As a side benefit, when the column is changed from a VARCHAR(30) to a VARCHAR(50) your code automagically adjusts instead of throwing a "character string buffer too small" error when it comes across the new longer data – Paregoric 9/1, 2012 at 17:48

@Persimmon - If you are just looking to see if the value is a Number or Varchar, it might be easier to test if the value is actually a number. Something like: IF translate(v_column_data, 'a0123456789', 'a') is null THEN /* is number logic / ELSE / is varchar logic */ END IF; – Paregoric 9/1, 2012 at 17:57

@Craig, i do use %type for all the variables; this springs from that, it's a %rowtype of a cursor. Either way using %type doesn't return the data type of that particular type. You are of course right about testing; I'd try to convert to a number and catch the exception but I wondered if there was an in-built way of doing it as Oracle does know the data type so why should I have to test for it again? – Persimmon 9/1, 2012 at 18:10

@Persimmon can you give an example where you don't know the type at the time you code your procedure/function? Are u using weakly typed sys_refcursor maybe? (and then want strongly typed when u use it?). Pls give more detail in form of code – Doubling 9/1, 2012 at 19:37

@tbone; I don't have one. This is, most importantly, pure interest. I couldn't find any references on t'internet and no-one at work knew. Both fairly unusual. Less importantly, I'm merging a few packages into 1 and I wanted to make them as maintenance free and functionless as possible and this was an idea I had. Not sure it would work... but I can't implement an idea until I know it's possible. – Persimmon 9/1, 2012 at 19:49

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 ;)

Doubling answered 10/1, 2012 at 12:46 Comment(2)

Not at all, you just went a little bit lecturey on sys_refcursors :-), which I don't particularly like either for the record :-). – Persimmon 10/1, 2012 at 17:3

true, it comes out that way sometimes. Glad this helped tho. – Doubling 10/1, 2012 at 17:19

Use the dump function and compare the result with this code.

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr.

Angiology answered 9/1, 2012 at 14:58 Comment(5)

+1 that's a find indeed. One point for future users this can only be called in a select so you have to select dump(...,...) into ... in PL\SQL block. The output is disgusting though so I'm going to wait to see if anyone comes up with something better :-) – Persimmon 9/1, 2012 at 15:24

@Persimmon it's a PL/SQL function, you don't need to put it in a SELECT statement. – Angiology 9/1, 2012 at 15:27

if you don't use it in a select statement it raises PLS-00204. – Persimmon 9/1, 2012 at 15:32

@Persimmon woops - my bad! Though I had used it as a standalone function – Angiology 9/1, 2012 at 16:25

Good idea. But be careful, the documentation does not list all possible data type codes. For example, SYSDATE and SYSTIMESTAMP are actually different data types, codes 13 and 188. I'm not sure if there are others not listed. – Battleax 10/1, 2012 at 6:22