CREATE OR REPLACE PROCEDURE proc_emp(mgr in number)
is
sqlstr varchar2(800);
v_mgr emp.mgr%type;
type cur_test is ref cursor;
emp_cur cur_test;
v_empno number(4);
v_ename varchar2(10);
--temp_tbl table%type (EMPNO number(4),ENAME VARCHAR2(10));
--tbl_temp table(empno emp.empno%type,ename emp.ename%type);
BEGIN
--create table tbl_temp(empno emp.empno%type,ename emp.ename%type);
--create table temp_tbl(EMPNO number(4),ENAME VARCHAR2(10));
--select distinct mgr from emp where mgr like '7%';
v_mgr :=mgr;
sqlstr:='select empno,ename From emp where mgr='|| v_mgr|| '';
OPEN emp_cur FOR sqlstr;
FETCH emp_cur INTO v_empno,v_ename;
insert into temp_tbl(empno,ename) values(v_empno,v_ename);
--If (emp_cur%NOTFOUND) Then
--EXIT;
--End if;
--Result := Result || name_temp || ',';
close emp_cur;
END;
