This discussion is archived
2 Replies Latest reply: Nov 28, 2012 6:06 AM by MLBrown RSS

How to Validate A number in a varchar field in forms?

DurgaSrinivas_886836 Newbie
Currently Being Moderated
Hi All,

I have a data block(Tabular type) from a table say , there is a columns "Attr Type"-->varchar2, "Default value" --> varchar2.

The Values entered in "Attr Type" column is TEXT,NUMBER,DATE.

Based on this values i have to validate "Default value" column.

Example

If Attr Type is TEXT --> then "Default Value" Field can Accept any free text(no validation required since it is a varchar field)

If Attr Type is NUMBER --> then "Default Value" Field Should Accept Only Numbers, but since it is a varchar filed How should i Validate it?
If i enter some characters in that field it should throw a message , or clear that value in that field.

Provide me some sample code or suggest me how i can do it?

If Attr Type is DATE--> then "Default Value" Field can Accept only 'SYSDATE', if user enters any thing other that 'sysdate' it should throw a message ?


Please provide me a solution or code to proceed further.

note : default value can also be NULL

Thanks,
Durga Srinivas
  • 1. Re: How to Validate A number in a varchar field in forms?
    AlexDiniasi Journeyer
    Currently Being Moderated
    Hello,

    Create the following program units:
    1.To check if a text is NUMBER

    CREATE OR REPLACE FUNCTION is_number
    (p_string IN VARCHAR2)
    RETURN VARCHAR2
    AS
    v_string NUMBER;
    BEGIN
    v_string := TO_NUMBER (p_string);
    RETURN 'Y';
    EXCEPTION
    WHEN VALUE_ERROR THEN
    RETURN 'N';
    END is_number;
    /

    2. CREATE OR REPLACE FUNCTION is_date (p_string in varchar2
    ,p_format in varchar2 default 'dd/mm/yyyy')
    return date is
    v_return date;
    BEGIN
    v_return := to_date(p_string,p_format);
    return 'Y';
    EXCEPTION
    when others then
    return 'N'l;
    END;
    /

    Then, to use them create a when-validate-item trigger for default value field :

    if is_number (Attr_Type)= 'Y' and is_number(Default_Value)='N' then
    message('Default value should be number');
    raise form_trigger_failure;
    end if;

    if is_date(Attr_Type)= 'Y' and (Default_Value)<>SYSDATE then
    message('Default value should be sysdate');
    raise form_trigger_failure;
    end if;

    Hope this helps.

    Kind regards,
    Alex

    If someone's answer is helpful or correct please mark it accordingly.
  • 2. Re: How to Validate A number in a varchar field in forms?
    MLBrown Journeyer
    Currently Being Moderated
    What I would probably do in this case is add something like this to my WHEN-VALIDATE-ITEM trigger (this is untested but I think you'll get the point):
    declare
      v_number        number;
    begin
      --
      -- ATTR_TYPE is a NUMBER - Is it really?  If you assign it to a number variable and it isn't a number it will throw a VALUE_ERROR.
      --
      if :BLOCK_NAME.ATTR_TYPE = 'NUMBER' then
        v_number := :BLOCK_NAME.DEFAULT_VALUE;
      end if;
    
      --
      -- ATTR_TYPE is DATE then is it the sysdate?  Instead of throwing an error, maybe you just set it to SYSDATE - that's your call.
      --
      if :BLOCK_NAME.ATTR_TYPE = 'DATE' then
        if to_date(:BLOCK_NAME.DEFAULT_VALUE,'MM/DD/YYYY') != to_date(to_char(sysdate, 'MM/DD/YYYY'), 'MM/DD/YYYY') then
          message('If the ATTR_TYPE is DATE you must enter the System Date.');
          raise form_trigger_failure;
        end if;
      end if;
    
      --
      -- If ATTR_TYPE is TEXT then accept anything - no validation
      --
    exception
      --
      -- Not a number even though ATTR_TYPE says it is.
      --
      when value_error then
        message('You must enter a number if the ATTR_TYPE is set to NUMBER.');
        raise form_trigger_failure;
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points