CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Stefan
Stefan

Reputation: 3859

How do I check a column type with a cursor?

I need to query an Oracle database table and check the datatype of a column. I got as far as the below script but TYPE_NAME is not recognised. I can check the contents of the column but I need to check the actual type of the table.

Anyone know how I can do that?

set pagesize 1000;
variable errorCode number;

DECLARE
    cursor c1 is
        select theColumn from myDatabaseTable;
    colRec c1%ROWTYPE;
BEGIN
    :errorCode := 0;
    OPEN c1;

    FETCH c1 INTO colRec;
    IF colRec.TYPE_NAME <> 'CHAR' THEN
        :errorCode := 1;
    END IF;


    CLOSE c1;
END;
/
exit :errorCode;

Upvotes: 1

Views: 2517

Answers (2)

TurtlesAllTheWayDown
TurtlesAllTheWayDown

Reputation: 406

You can check in one of the data dictionary views: dba_tab_cols, all_tab_cols, or user_tab_cols by table_name and column_name.

For example,

select data_type
from dba_tab_cols
where table_name = 'MYTABLE'
and column_name = 'MYCOL'

If you use the dba_tab_cols or all_tab_cols views you'll also want to include owner in the query:

select data_type
from dba_tab_cols
where owner = 'SCHEMA' 
and table_name = 'MYTABLE'
and column_name = 'MYCOL'

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You could use the procedure DBMS_SQL.describe_columns by parsing your query as a CURSOR. The procedure gives you a collection containing columns, datatypes and sizes.

col_type - gives you the datatypes.
col_name - gives you the column_names.
.. and so on.

Refer this link for details.

DBMS_SQL.DESCRIBE_COLUMNS

Here is an example.

declare
    cur number;
    cnt number;
    rec_t dbms_sql.desc_tab;
begin
    cur := dbms_sql.open_cursor;

    dbms_sql.parse(cur, 'select 1 id, ''Stefan'' Name from DUAL', dbms_sql.native); 

    dbms_sql.describe_columns(cur, cnt, rec_t );

     FOR i IN 1..rec_t.COUNT
     LOOP
        dbms_output.put_line(rec_t(i).col_name || ' - TYPE ' || rec_t(i).col_type);
    END LOOP;

    dbms_sql.close_cursor(cur);
end;
/

The type is a number representing a particular datatype. Here is the list which provides the numbers for important datatypes.

Datatype       Number

VARCHAR2        1   
NVARCHAR2       1   
NUMBER          2   
INTEGER         2   
DATE            12  
CHAR            96  
NCHAR           96  
CLOB            112 
NCLOB           112 
BLOB            113 
BFILE           114

So, You can either do a comparison such as

IF rec_t(i).col_type <> 96 THEN ...

or write a case block such as

CASE WHEN rec_t(i).col_type = 96 THEN 'CHAR' and compare on the output of the case block.

Upvotes: 2

Related Questions