Thank you very much. I finally got the procedure to be executed without errors. However I have a different problem which I will describe after I explain what I am trying to do.
The database has the following tables
1. EMP(emp_ID, Ename, deptID),
2. DEPT(deptID, deptName).
The calling envirnoment takes as the argument, emp-ID, ename and
deptName but to insert into the table EMP, we need emp_ID, ename and dept_ID. A select statement written within the INSERT statement fetches the dept_ID after the input deptName from the calling routine is matched.
My confusion is how to write the calling routine such that it takes deptName as the argument and not the dept_ID. The conflict is that I get an error as the arguments of the calling envirnoment and that of the INSERT statement in the store procedure are different. The INSERT statement by itself works very well with hard coded data taking ename and deptName as the argument. This is what I have till now.
CREATE or REPLACE procedure INPUT_RECORDS(
p_ename in varchar2,
p_deptID in number,
p_dname in varchar)
INSERT into emp(emp_id, ename, deptID)
dept where dname = v_dname)');
INPUT_RECORDS('EmpSeq.nextval', 'Robert', Mktg);
PLS-00306: Wrong number or types of arguments in call to 'INPUT_RECORDS'