Drop by to ask for help

[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(pi d,vdate,action, vaccinated) values(pat_id,v is_vdate,vis_ac t,vac_vacc)
;
5 DBMS_OUTPUT.PUT _LINE ('Insert attempted');
6 END;
7 /

Procedure created.

SQL> execute add_vacc('2','1 6=dec-1999',3,'choler a');

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','1 6-dec-1999',1,'choler a');
BEGIN add_vacc('2','1 6-dec-1999',1,'choler a'); 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','1 7-dec-1999',1,'choler a');
BEGIN add_vacc('2','1 7-dec-1999',1,'choler a'); END;

*
ERROR at line 1:
ORA-02291: integrity constraint (CGNR1.SYS_C008 0698) 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','1 6-dec-1999','4','chol era');

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','1 6-dec-1999','4',chole ra);
BEGIN add_vacc('2','1 6-dec-1999','4',chole ra); 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','1 6-maz-1999','4','chol era');
BEGIN add_vacc('2','1 6-maz-1999','4','chol era'); 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.

[debasisdas]

Do you have any questions ?

Why you have posted all that code here ?

[nur08]

Do you have any questions ?

Why you have posted all that code here ?

I need to know the step how to used the business rules in this code please help
I am new to oracle

[amitpatel66]

I need to know the step how to used the business rules in this code please help
I am new to oracle

if y ou are looking for any PLSQL standards then you can refer some documents for this purpose.