Home » SQL & PL/SQL » SQL & PL/SQL » find value or datatype of a varriable
find value or datatype of a varriable [message #216968] Wed, 31 January 2007 01:45 Go to next message
jaydeep.desai
Messages: 4
Registered: January 2007
Location: Mumbai
Junior Member

how can i check the value or datatype of a varriable in program.
that is is there any function or functionality available which tells me that this is number or date or char.
there is function in VB isnumber(), like this is it any in oracle?
Re: find value or datatype of a varriable [message #216983 is a reply to message #216968] Wed, 31 January 2007 02:57 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
You have to write some SQL or a function. If you want to see if the variable is a number, you can use:
select length(translate(trim(your_variable),' +-.0123456789',' ')) from dual;


this returns a positive number if your_variable is NON-numeric. Or you can use functions returning booleans that use Oracle's conversion functions:

function ISDATE (your_variable long) return boolean is
 dt date;
begin
 dt := to_date(your_variable,'dd-mon-yyyy'); 
 RETURN true;
exception
 when others then RETURN false; 
end;

function ISNUMBER (your_variable long) return boolean is
  num number;
begin
  num := to_number(your_variable);
 RETURN true; 
exception
 when others then RETURN false; 
end;


Re: find value or datatype of a varriable [message #223455 is a reply to message #216968] Fri, 09 March 2007 00:22 Go to previous message
ddkdhar
Messages: 68
Registered: February 2007
Member

well this is the answer for your question check this link
Previous Topic: How can i calla function with dynamic SQL on ORACLE 7.3
Next Topic: Index for PL/SQL Table out of Range for Host Language
Goto Forum:
  


Current Time: Tue Dec 06 15:57:13 CST 2016

Total time taken to generate the page: 0.12967 seconds