2
/* Note: default for serveroutput is OFF.
Therefore no 'Insert attempted' as I did not switch it on by
set serveroutput on; */
SQL> CREATE OR REPLACE PROCEDURE add_vacc (pat_id in char, vis_vdate in date, vis_act in number, vac
_vacc in char)
2 AS
3 BEGIN
4 insert into vaccinations(pid,vdate,action,vaccinated) values(pat_id,vis_vdate,vis_act,vac_vacc)
;
5 DBMS_OUTPUT.PUT_LINE ('Insert attempted');
6 END;
7 /
Procedure created.
SQL> execute add_vacc('2','16=dec-1999',3,'cholera');
PL/SQL procedure successfully completed.
SQL> select * from vaccinations
2 where pid = '2' and action = 3;
PID VDATE ACTION VACCINATED
------ --------- ---------- --------------------
2 06-AUG-91 3 polio
2 16-DEC-99 3 cholera
SQL> commit;
Commit complete.
/* Commit saves current state of database */
SQL> execute add_vacc('2','16-dec-1999',1,'cholera');
BEGIN add_vacc('2','16-dec-1999',1,'cholera'); END;
*
ERROR at line 1:
ORA-00001: unique constraint (CGNR1.PKVAC) violated
ORA-06512: at "CGNR1.ADD_VACC", line 4
ORA-06512: at line 1
/* violates primary key constraint for vaccination */
SQL> execute add_vacc('2','17-dec-1999',1,'cholera');
BEGIN add_vacc('2','17-dec-1999',1,'cholera'); END;
*
ERROR at line 1:
ORA-02291: integrity constraint (CGNR1.SYS_C0080698) violated - parent key not found
ORA-06512: at "CGNR1.ADD_VACC", line 4
ORA-06512: at line 1
/* foreign key violation */
SQL> execute add_vacc('2','16-dec-1999','4','cholera');
PL/SQL procedure successfully completed.
/* Note: action entered as char with quotes but type cast to number */
SQL> select * from vaccinations
2 where pid = '2' and action = 4;
PID VDATE ACTION VACCINATED
------ --------- ---------- --------------------
2 16-DEC-99 4 cholera
SQL> execute add_vacc('2','16-dec-1999','4',cholera);
BEGIN add_vacc('2','16-dec-1999','4',cholera); END;
*
ERROR at line 1:
ORA-06550: line 1, column 38:
PLS-00201: identifier 'CHOLERA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
/* cholera not in quotes is taken as (undeclared) variable, not as value */
SQL> execute add_vacc('2','16-maz-1999','4','cholera');
BEGIN add_vacc('2','16-maz-1999','4','cholera'); END;
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1
/* month is maz, should presumably be mar or may */
SQL>
1. transaction commands (commit/rollback);
2. a business rule that no more than two vaccinations are allowed per day
3. a business rule that the vaccination date must be no earlier than 1st January 2003
4. an exception handler that catches any error and displays the error code.
