SQL>
SQL> create or replace function f_makeAddress (i_address VARCHAR2,i_city VARCHAR2,i_state VARCHAR2,i_zip VARCHAR2)
2 return VARCHAR2
3 is
4 e_badZip EXCEPTION;
5 pragma EXCEPTION_init(e_badZip,-20998);
6 v_out VARCHAR2(256);
7 begin
8 p_validateZip (i_zip);
9 v_out:= i_address||', '||i_city ||', '||i_state ||', '||i_zip;
10 return v_out;
11 exception
12 when e_badZip then
13 return i_zip || ': Invalid zip code.';
14 end;
15 /
Function created.
SQL>
SQL> create or replace procedure p_validateZip (i_zipCode VARCHAR2)
2 is
3 e_tooShort EXCEPTION;
4 e_tooLong EXCEPTION;
5 e_badZip EXCEPTION;
6 pragma exception_init(e_badZip, -20998);
7 v_tempZip NUMBER;
8 Begin
9 if length(i_zipCode)< 5 then
10 Raise e_tooShort;
11 elsif length(i_zipCode)> 6 then
12 Raise e_tooLong;
13 end if;
14
15 v_tempZip := to_number(i_zipCode);
16
17 exception
18 when e_tooLong then
19 DBMS_OUTPUT.put_line('long zip');
20 raise e_badZip;
21 when e_tooShort then
22 DBMS_OUTPUT.put_line('short zip');
23 -- raise e_badZip SHOULD be here
24 when value_error then
25 DBMS_OUTPUT.put_line('non-numeric zip');
26 raise; -- re-raising the same exception
27 end;
28 /
Procedure created.
SQL> --Scenario 1: No rule violations
SQL>
SQL> declare
2 v_out VARCHAR2(2000);
3 begin
4 v_out:=f_makeAddress('AA','City','CA','94061');
5 DBMS_OUTPUT.put_line(v_out);
6 end;
7 /
AA, City, CA, 94061
PL/SQL procedure successfully completed.
SQL>
SQL> --Scenario 2: Short ZIP code
SQL>
SQL> declare
2 v_out VARCHAR2(2000);
3 begin
4 v_out:=f_makeAddress('A','City', 'CA','940');
5 DBMS_OUTPUT.put_line(v_out);
6 end;
7 /
short zip
A, City, CA, 940
PL/SQL procedure successfully completed.
SQL> --Scenario 3: Non-numeric ZIP code
SQL>
SQL> declare
2 v_out VARCHAR2(2000);
3 begin
4 v_out:=f_makeAddress('A','City' , 'CA','9406A');
5 DBMS_OUTPUT.put_line(v_out);
6 end;
7 /
non-numeric zip
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "RNTSOFT.P_VALIDATEZIP", line 26
ORA-06512: at "RNTSOFT.F_MAKEADDRESS", line 8
ORA-06512: at line 4
SQL> -- Long ZIP code
SQL>
SQL> declare
2 v_out VARCHAR2(2000);
3 begin
4 v_out:=f_makeAddress('A','City','CA','940612345');
5 DBMS_OUTPUT.put_line(v_out);
6 end;
7 /
long zip
940612345: Invalid zip code.
PL/SQL procedure successfully completed.
SQL>