Hi
Database Version: 11.2.0.2
create table emp
(id number,
name varchar2(100),
dept number,
sal number,
address varchar2(100));
insert into emp values (1,'abc',10,1000,'a avenue');
insert into emp values (2,'def',20,1000,'b avenue');
insert into emp values (3,'ghi',30,1000,'c avenue');
insert into emp values (4,'jkl',10,1000,'d avenue');
insert into emp values (5,'mno',40,1000,'d avenue');
insert into emp values (6,'xyz',50,1000,'e avenue');
commit;
I need some help with a procedure. Following are the requirements.
i) User can input parameter value for "EMPLOYEE" either as a NUMBER or a VARCHAR. For eg 1 or 'ABC'
ii) Based on the input value, procedure should return the desired record. For eg
select * from emp where id=1 or select * from emp where name like 'abc'
iii) All columns can be used as parameters to pass values for eg CREATE PROCEDURE p_emp(EMPLOYEE, DEPARTMENT, SALARY, ADDRESS,FILTER)
iv) Not passing some values for some parameters is acceptable for eg exec p_emp('',10,1000,'')
v) Result (OUT) shall be a table format (I am thinking maybe SYS_REFCURSOR can be used) For eg
1 ABC 10 1000 A Avenue
4 JKL 10 1000 D Avenue
vi) Result (OUT) can be controlled by ROWNUMBER for eg.
select * from emp where rownum between 1 and 3
If none is specified then first 2000 records are shown.
vii) An additional parameter(FILTER) shall determine what output result should look like. For eg. if 1 is passed then only show ID, NAME, if 2 is passed show ID and ADDRESS etc.
Your help is much appreciated
Message was edited by: saam001
Changes made after sol.beach reply. Capital Letters dropped and database version added