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;
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Lewis Cunningham
I need a step-by-step procedure for importing text to an Oracle database using a stored procedure. Continue Reading
I need to somehow log the execution time for two procedures every time they are called and save that data (in a table?). Do you know of a way to ... Continue Reading
Is it possible that we can hide some objects in a particular schema, so that users can't see them or I have control in some way that if users can see... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.