Search for data type of cursor column or table in block

Question

Is it possible to find out the data type of a cursor column or variable in a block without using system tables? Although I understand that I can use system tables to find out this information, it would be much slower.

Sort of,

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 cannot find a way to do this without resorting to dbms_sql , which would be redundant for my ultimate goal.

But Oracle already has all the information. If I tried to assign varchar2 a number , then he would complain instantly so that he knew what data types are.

And, yes, I know that the number of Oracle versions is ridiculous, but the amount that we have at the moment ... 9i quickly dies in favor of 11, but this code will work on 9i if I can find the answer! But I turned on 11, because I can wait for a better solution, if necessary,

Answer 1

It sounds as if you need an object that describes itself. The value programmatically finds the type of the variable without selecting metadata from some representation. Just ask an object, what are you?

This seems unnecessary in most situations, since in most cases we already know the type (strongly typed). For example, procedure parameters usually indicate a type (number, varchar2, whatever). Local variables will usually indicate the type or bind themselves to the type of the database object via notation of type%.

There are situations where weakly typed objects are required or useful, such as a weakly typed cursor variable, which can be used for any query. Too simplified 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 encodes a cursor that will be used with another table (I specifically chose the name of the terrible procedure). Sort of:

 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 why I prefer strongly typed cursors and avoid this situation.

In any case, in the case of a self-describing object, you can use the built-in type SYS.ANYDATA (similar to SYS.ANYDATASET for general collection types). This was introduced with 9i, I reckon. For example, this procedure uses some data and branch 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 type based software branch. And 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" 

I hope that the answer didn’t take too long;)

Answer 2

Use dump and compare the result with this code.

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