2 Replies Latest reply: Nov 28, 2012 8:06 AM by MLBrown RSS

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

    DurgaSrinivas_886836
      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
          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
            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;