Help - Search - Members - Calendar
Full Version: how to solve this problem
Oracle DBA Forums > Oracle > Oracle Forum
nur08
/* 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:
Please-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>
dry.gif dry.gif


Modify the procedure so that it satisfies the additional requirements for:

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.
Thnak you for you are help
SteveC
Permission granted. Go ahead and make the changes, let us know how it turns out for you. Thanks.
burleson
QUOTE
Modify the procedure so that it satisfies the additional requirements for:

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.


Asking people to do your homework for you is dishonest.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.