please find the solution of this problem. ( in PL SQL ) ——–

I’m doing an assignment and the last question states that I call the procedure from question 2 and function from question 3 and then put them in an insert clause(question 4).

4. Write a PL/SQL Code for an anonymous block to do the following (in package):

a) Get the doctor name for a given doctor id (read from keyboard) using a function [FunGetDoctorName].

b) Get the department name for a given department id (read from keyboard) using a procedure [ProGetepartmentName].

c) Insert the doctor name and department name (which you got from the function and

procedure) by including serial number and current date into the visitings table.

put the answer in ( Package)

this is my table codes:

CREATE TABLE DEPT(
DeptNo      NUMBER PRIMARY KEY,
DeptName    VARCHAR2(30) NOT NULL);

CREATE TABLE DOCTORS(
DoctorID    NUMBER PRIMARY KEY,
DoctorNAME  VARCHAR2(30) NOT NULL,
DeptNo      NUMBER REFERENCES DEPT(DEPTNO),
Salary      NUMBER NOT NULL);

CREATE TABLE VISITINGS(
SlNo        NUMBER PRIMARY KEY,
DoctorName  VARCHAR2(30),
DepartmentName  VARCHAR2(30),
VisitDate   DATE);

INSERT INTO DEPT VALUES(10,'ENT');
INSERT INTO DEPT VALUES(20,'Orthopedic');
INSERT INTO DEPT VALUES(30,'Cardiology');
INSERT INTO DEPT VALUES(40,'Neurology');

INSERT INTO DOCTORS VALUES(101,'Abheer',20,2550);
INSERT INTO DOCTORS VALUES(102,'Zuwaina',10,2175);
INSERT INTO DOCTORS VALUES(103,'Sara',30,1985);
INSERT INTO DOCTORS VALUES(104,'Fatma',20,2200);
INSERT INTO DOCTORS VALUES(105,'Laila',10,2600);

INSERT INTO VISITINGS VALUES(1,'Sara','Cardiology','10-Nov-19');
INSERT INTO VISITINGS VALUES(2,'Abheer','Orthopedic','11-Nov-19');

My function

create or replace function FunGetDoctorName(Docid number) return varchar2 is
docname DOCTORS.DoctorName%type;
Begin
select DoctorName into docname from DOCTORS where DoctorID = Docid;
return docname;
End ;
/

My procedure

create or replace procedure ProGetDepartmentName is
depname dept.DeptName%type;
Begin
select DeptName into depname from dept where DeptNo =10;
dbms_output.put_line(depname);
End ;
/ 

here is the problem:

Create or replace package pkg1 is 
Function FunGetDoctorName(Docid Number) return varchar2 ;
procedure ProGetDepartmentName(DeptNo NUMBER);
end pkg1;
/
CREATE OR REPLACE PACKAGE BODY pkg1 AS

FUNCTION FunGetDoctorName(Docid Number)
RETURN varchar2 IS
   docname DOCTORS.DoctorName%type;
BEGIN
   select DoctorName into docname from DOCTORS where DoctorID = Docid;
return docname ;
END;
PROCEDURE ProGetDepartmentName(DeptNo NUMBER) IS
depname dept.DeptName%type;
BEGIN
 Select DeptName into depname from dept where DeptNo=10; 
dbms_output.put_line(depname) ;
END;
END pkg1 ;
/
declare
ProGetDepartmentName
(:DeptNo in dept.DeptNO%type,
 depname in dept.DeptName%type) 
FunGetDoctorName
(:Docid in DOCTORS.DoctorID%type ,
docname in DOCTORS.DoctorName%type);
docname varchar2(30);
depname varchar2(30);
Docid number;
serial number;
is 
Begin
dbms_output.put_line('Department Name: '||depname);
select count(slno) into serial from visitings;
serial :=serial+1;
insert into visitings(slno,doctorname,departmentname,visitdate) values(serial,docname,depname,sysdate);
End;
/

I keep getting errors :

SP2-0552: Bind variable “DOCID” not declared.

Answer

First of all, your procedure must have one in and one out parameter so that you can pass deptno and get deptname as the output.

procedure ProGetDepartmentName(p_depnum number,P_depname out varchar)
is
Begin
  select DeptName into P_depname from dept where DeptNo = p_depnum;
  dbms_output.put_line(p_depnum);
End ;
/ 

In your pl/sql block, you can use substitution variable to take an input from keyboard as follows:

declare
  V_DeptNo dept.DeptNO%type := &dept_no
  V_deptname in dept.DeptName%type;
  V_Docid in DOCTORS.DoctorID%type := &doc_id;
is 
Begin
  Pkg1.ProGetDepartmentName(v_deptno,  v_deptname);
  dbms_output.put_line('Department Name: '|| v_deptname);
  insert into visitings(slno,doctorname,departmentname,visitdate) 
    values((select count(slno) + 1 from visitings),FunGetDoctorName(v_docid),v_deptname,sysdate);
End;
/

Note:

1. Instead of fetching count from table and adding one into it for slno, you should use the sequence.
2. In procedure and function, use exception block to handle no row found or multiple record found or any other issues gracefully.
3. While inserting in to table, you should also use exceprion block to handle issues gracefully. (In your case it is needed because of the count(slno) + 1 logic as it can assign same number to different sessions executing simentaneously which my lead to primary key violation)