create table request_details(req_id number(10),
e_num number(10),
e_name varchar2(30),
e_env varchar2(30),
e_member varchar2(30),
constraint pk_request_details primary key(req_id));
insert into request_details values(1,1,'A','SIT','SAP1');
insert into request_details values(2,1,'A','SIT','SAP1');
insert into request_details values(3,1,'A','SIT','SAP1');
insert into request_details values(4,1,'A','SIT','SAP1');
create or replace procedure sp_request_details(
iv_env IN varchar2,
iv_team IN varchar2,
iv_enum IN number,
ov_err_msg OUT varchar2
)
AS
lv_count number(10);
BEGIN
/*Here I need to count the row number of the table for particular e_num*/
for i in(select * from request_details where e_env = iv_env and e_member = iv_team and e_num = iv_enum)LOOP
select s*, rownum from request_details;--It is giving error
/*Basically I want to find the row count of the table request_details
Suppose if there are 4 records for the same e_num then there would be one column which will give me numbering from 1 to 4
If one record gets inserted for the same e_num then the count will be 5*/
end loop;
END sp_request_details;
Expected output:
I need to count the records row wise and need to give that count into the column (ROWCOUNT) but got stuck in the stored procedure. Can someone help with this?