1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Forms: how to restrict special characters in text field

Discussion in 'Oracle Forms and Reports' started by sambuduk, Jan 19, 2012.

  1. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    hi Friends

    i have developed a form. This form having Bill No as field which is char datatype. so it wil accept any value to enter.
    As per client requirement it has to accept Alphanumeric value only But not special characters. So how to restrict special characters in this field.

    Thanks and Regards
    Samba
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    When a user enters text in Bill No field you have got to have a function that validates the input. On that function you will have to have a range for invalid values. That range is made in ASCII.
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Thank You Sadik.

    But It is difficult to findout ASCII values for all special characters.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Ascii value range in 65 to 90 and 97 to 122. if the character is not in this range then throw error. write the code in when-validate-item trigger. All the best if you are not succeeded then present your code. Any of us will help you.
     
  5. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Thanks Kiran.

    I approached another way.It is working fine.
    But I dont know whether it is right way or not.

    the below procedure i am calling from WHEN-VALIDATE-ITEM .
    Code (SQL):

    PROCEDURE val_bill_no
      IS
      ln_bill_no VARCHAR2(100);
      ln_length        NUMBER := 0;
       
      BEGIN        
          ln_bill_no := :XX_RECEIPT_ALLOCATION.COURIER_BILL_NO;
          IF(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO IS NOT NULL) THEN
           ln_length  := LENGTH(REPLACE(translate(UPPER(ln_bill_no),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','000000000000000000000000000000000000'),'0',''));
              IF( NVL(ln_length,0) > 0) THEN
                  fnd_message.set_string('Courier bill No. should be alphanumeric only');
                  fnd_message.error;      
              RAISE form_trigger_failure;
              END IF;
          END IF;
      END val_bill_no
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    DECLARE
    ln_length NUMBER := 0;
    BEGIN
    IF(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO IS NOT NULL) THEN
           ln_length  := LENGTH(REPLACE(translate(UPPER(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','000000000000000000000000000000000000'),'0',''));
              IF( NVL(ln_length,0) > 0) THEN
                  fnd_message.set_string('Courier bill No. should be alphanumeric only');
                  fnd_message.error;      
              RAISE form_trigger_failure;
              END IF;
          END IF;
    END;

    Perfectly Correct.

    If you are doing through pl/sql we use loop and extract character by character with substring and check each character fall in range or not. That is all
     
    sambuduk likes this.
  7. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi All,

    REGEXP_REPLACE(String,'[^a-zA-Z0-9]') will simply remove all non alphanumeric characters.

    So this can be done using another way as well, just simplifying the function.

    Code (SQL):
    BEGIN

    IF(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO IS NOT NULL) THEN
       
        IF  :XX_RECEIPT_ALLOCATION.COURIER_BILL_NO <>   REGEXP_REPLACE(:XX_RECEIPT_ALLOCATION.COURIER_BILL_NO,'[^a-zA-Z0-9]')   THEN
                  fnd_message.set_string('Courier bill No. should be alphanumeric only');
                  fnd_message.error;      
                RAISE form_trigger_failure;
        END IF;
       
    END IF;

    END;
    Thanks,
    Yowan Cristo