Home » SQL & PL/SQL » SQL & PL/SQL » String and Numeric Manipulation (Oracle Forms 10g, Windows )
String and Numeric Manipulation [message #403483] Fri, 15 May 2009 15:58 Go to next message
davydany
Messages: 11
Registered: September 2008
Location: New Hyde Park
Junior Member
I'm using Oracle Forms, but this is really a question on PL/SQL.

I can't figure out how to set this field in the DB. Should it be VARCHAR2 or NUMBER. But, my department has data that they want to enter into the system, which will have 5 numeric characters. It can have either one decimal point or no decimal at all.

So, 123.04 is valid, 123.40 is not. 12345 is valid, and so is 12304, but 00123 is not. Could you help me guys? I'd appreciate any help. Thanks!

Re: String and Numeric Manipulation [message #403485 is a reply to message #403483] Fri, 15 May 2009 16:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
if you want to be able to distinguish between 2 different values
"12345" and "12345." then number datatype wont be able to distinguish between them. In a case like this, you'd have to use a character datatype (say varchar2) rather an numeric one. to ensure that the value stored is sill a valid number, create a function.

-- 12345
-- 12345.
-- 12.345
-- 0.1234
-- 1.2.3.4.5

CREATE OR REPLACE FUNCTION to_num (p_value IN VARCHAR2)
   RETURN NUMBER
   --RETURN BOOLEAN
IS
BEGIN
   RETURN TO_NUMBER (p_value);
   --RETURN TRUE;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
      --RETURN FALSE;
END;
/

select to_num('1.2.3.4.5') from dual;

Previous Topic: count hours between a date range
Next Topic: Statement execution speed depends on single statement or stored procedure?
Goto Forum:
  


Current Time: Mon Dec 05 05:04:41 CST 2016

Total time taken to generate the page: 0.07838 seconds