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!

format of dutch zipcode

Discussion in 'SQL PL/SQL' started by 4731809, Jun 25, 2011.

  1. 4731809

    4731809 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    hiya guys,

    I checked the internet over and over and over and... well u get the point.
    My question:
    how do I make sure, my end-user can only insert or update custPostcode in the table HB_Customers if the format equals '0000 XX' (so four numbers and two letters (a-z))?¿?¿
    preferabel in pl/sql (if there's any choice at all :p)
    Greets Stan!
     
  2. 4731809

    4731809 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I am working with oracle apex, if it's possible to change in apex, then that's okay too, please I need help quickly on this matter
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    The first question is how will your end user insert or update in the table? Does he have access to the raw database and run an insert/update statement through SQL Plus/Toad etc or does he use some application?

    Basically your requirement can be met through forcing a regular expression check. I won't go into the details of what regular expressions are, a quick Google Search will tell you that. The important point is where can you implement a regex check? In the application or at the database level is what you have to consider.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    At least two ways come to mind (maybe more but I haven't time to investigate them):

    Code (SQL):
    SQL> CREATE TABLE hb_customer(
      2        custid NUMBER,
      3        custname varchar2(40),
      4        custzip varchar2(7)
      5  );
    TABLE created.
    SQL>
    SQL> --
    SQL> -- Enforce zip code format with a trigger
    SQL> --
    SQL>
    SQL> CREATE OR REPLACE TRIGGER hb_zip_trg
      2  BEFORE INSERT OR UPDATE OF custzip ON hb_customer
      3  FOR each ROW
      4  DECLARE
      5        zipfmt exception;
      6        pragma exception_init(zipfmt, -20987);
      7        ziperrtxt varchar2(80):='Zip code submitted '||:NEW.custzip||' must have format ''NNNN XX'' (N=number, X=letter)''';
      8        ziperr NUMBER:=-20987;
      9  BEGIN
     10        IF NOT regexp_like(:NEW.custzip, '^[0-9][0-9][0-9][0-9] [A-Z][A-Z]$')
     11        THEN
     12         raise zipfmt;
     13        END IF;
     14  
     15  exception
     16        WHEN zipfmt THEN
     17         raise_application_error(ziperr, ziperrtxt, TRUE);
     18        WHEN others THEN
     19         dbms_output.put(dbms_utility.format_error_stack);
     20         dbms_output.put_line(dbms_utility.format_error_backtrace);
     21  END;
     22  /
    TRIGGER created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> SET linesize 132 trimspool ON
    SQL>
    SQL> INSERT INTO hb_customer
      2  VALUES(1,'Smelnorf Gazebo','9875 AB');
    1 ROW created.
    SQL>
    SQL> INSERT INTO hb_customer
      2  VALUES(2,'Smeezo Plorf','9875 A7');
    INSERT INTO hb_customer
                *
    ERROR at line 1:
    ORA-20987: Zip code submitted 9875 A7 must have format 'NNNN XX' (N=NUMBER, X=letter)'
    ORA-06512: at "BING.HB_ZIP_TRG", line 14
    ORA-20987:
    ORA-04088: error during execution of trigger '
    BING.HB_ZIP_TRG'

    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> select * from hb_Customer;
        CUSTID CUSTNAME                                 CUSTZIP
    ---------- ---------------------------------------- -------
             1 Smelnorf Gazebo                          9875 AB
    SQL>
    SQL> drop trigger hb_zip_trg;
    Trigger dropped.
    SQL>
    SQL> truncate table hb_customer;
    Table truncated.
    SQL>
    SQL> --
    SQL> -- Enforce zip code format with PL/SQL
    SQL> --
    SQL>
    SQL> create or replace function dutch_zip_ck (p_zip in varchar2)
      2  return varchar2
      3  as
      4        zipfmt exception;
      5        pragma exception_init(zipfmt, -20987);
      6        ziperrtxt varchar2(80):='
    Zip code submitted '||p_zip||' must have format ''NNNN XX'' (N=NUMBER, X=letter)''';
      7        ziperr number:=-20987;
      8  begin
      9        if not regexp_like(p_zip, '
    ^[0-9][0-9][0-9][0-9] [A-Z][A-Z]$')
     10        then
     11         raise zipfmt;
     12        else
     13         return(p_zip);
     14        end if;
     15  
     16  exception
     17        when zipfmt then
     18         raise_application_error(ziperr, ziperrtxt, true);
     19        when others then
     20         dbms_output.put(dbms_utility.format_error_stack);
     21         dbms_output.put_line(dbms_utility.format_error_backtrace);
     22  end;
     23  /
    Function created.
    SQL>
    SQL> insert into hb_customer
      2  values(1,'
    Smelnorf Gazebo',dutch_zip_ck('9875 AB'));
    1 row created.
    SQL>
    SQL> insert into hb_customer
      2  values(2,'
    Smeezo Plorf',dutch_zip_ck('9875 A7'));
    values(2,'
    Smeezo Plorf',dutch_zip_ck('9875 A7'))
                            *
    ERROR at line 2:
    ORA-20987: Zip code submitted 9875 A7 must have format '
    NNNN XX' (N=number, X=letter)'
    ORA-06512: at "BING.DUTCH_ZIP_CK", line 18
    ORA-20987:

    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM hb_customer;
        CUSTID CUSTNAME                                 CUSTZIP
    ---------- ---------------------------------------- -------
             1 Smelnorf Gazebo                          9875 AB
    SQL>
     
    4731809 likes this.
  5. 4731809

    4731809 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    yes! thank you very much! this is the answer :D
    You are my hero!!