Q

Detect if a column is NUMBER or VARCHAR2 in PL/SQL

How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this?

How can I tell if a column is a NUMBER or VARCHAR in PL/SQL? Is there a function to do this?

When you write PL/SQL you should use the %TYPE declaration anchor, i.e.

  var1 table.row%TYPE;
Declaring your variables that way ensures that you have the right data type.

Detecting it at runtime really depends on what you are trying to do. I would guess you're doing something with dynamic SQL and there are two approaches you can take.

The first approach is that you can DUMP the column and determine the data from that. DUMP is a SQL function. Look in the SQL reference manual for the version of database that you are using for the syntax. You can find the documentation at http://tahiti.oracle.com.

If you just want a very simple function to do a is_number check, you can use this one:

CREATE OR REPLACE FUNCTION is_number( p_input IN VARCHAR2 ) 
  RETURN BOOLEAN AS
  
  v_return_number NUMBER;
  
BEGIN

  v_return_number := TO_NUMBER( p_input , '9999');

  RETURN TRUE;
    
EXCEPTION

  WHEN value_error THEN
     
    RETURN FALSE;
    
END;
/
You would call this function like this:
BEGIN
  IF is_number('10') THEN
    dbms_output.put_line('It''s a number!');
  END IF;
  
  IF NOT is_number('ABC') THEN
    dbms_output.put_line('It is NOT a number!');
  END IF;
END;
This was first published in March 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close