Hi All,
I've the below function :
create or replace
FUNCTION F_STATUS_TEST(P_STS_NAME VARCHAR2,P_State VARCHAR2)
RETURN number IS
t_number number:=0;
BEGIN
select count into t_number from BTC_weekly_streams where tsk_status=P_State
and STS_NAME=P_STS_NAME;
RETURN t_number;
Exception
WHEN NO_DATA_FOUND then
t_number:=0;
RETURN t_number;
END;
**********************************************************************************************
I'm invoking the above function from the below proc :
create or replace procedure prc_sts
as
v_b number(10);
cursor c_sts is select * from btc_streamstages where sts_id=32; --- This will fetch id's along with respective names from a base table.
type t1 is table of c_sts%rowtype;
tab1 t1;
begin
open c_sts;
fetch c_sts into tab1
for i in 1..tab1.count
loop
F_STATUS_TEST(tab1(i).STS_NAME,'Running'); --- Function invoked with the parameters passed. ( 1st parameter is fetched from nested table & the 2nd parameter is hard-coded ) .
v_b := F_STATUS_TEST.t_number;
insert into btc_weekly_task_report(STS_NAME,T_RUNNING)
values(tab1(i).sts_name,v_b);
commit;
end loop;
close c_sts;
end;
/
Errors faced while compiling the procedure :
Error(13,1): PL/SQL: SQL Statement ignored
Error(13,18): PLS-00597: expression 'TAB1' in the INTO list is of wrong type
Error(19,1): PLS-00221: 'F_STATUS_TEST' is not a procedure or is undefined
Error(19,1): PL/SQL: Statement ignored
Error(20,1): PL/SQL: Statement ignored
Error(20,22): PLS-00225: subprogram or cursor 'F_STATUS_TEST' reference is out of scope
Experts pl help ... I'm unable to debug it .
Thanks in advance