Hi,
When I tried to run this program I am getting below error:
ORA-01007 - variable not in select list.Please help to resolve.
Code:
create or replace procedure "XX_BPM_DATA_P" (P_PROCESS_ID IN VARCHAR2)
is
TYPE l_entity_type IS TABLE OF xx_BPM_data.ENTITY%TYPE INDEX BY PLS_INTEGER;
TYPE l_data_type IS TABLE OF XX_BPM_DATA.DATA%TYPE INDEX BY PLS_INTEGER;
TYPE l_count_type IS TABLE OF XX_BPM_DATA.count%TYPE INDEX BY PLS_INTEGER;
l_Entity_v l_Entity_type;
l_data_v l_data_type;
l_count_v l_count_type;
l_security_group_id number;
app_id number(20);
l_Actual_value XX_BPM_DATA.DATA%TYPE;
cursor BPM_CUR is select id,process_id , sequence, to_char(query) query,report_num from xx_test_bpm_dynamic
where
process_id = p_process_id
and report_num=1
order by process_id, sequence;
BEGIN
--delete xx_bpm_data where process_id = p_process_id;
for bpm_rec in bpm_cur
loop
delete xx_bpm_data
where process_id = bpm_rec.process_id
and sequence = bpm_rec.sequence
and report_num = bpm_rec.report_num;
l_security_group_id := apex_custom_auth.get_session_id_from_cookie;
--dbms_output.put_line(l_security_group_id);
execute immediate bpm_rec.query BULK COLLECT INTO l_ENTITY_v,l_DATA_v,l_count_v;
if (bpm_rec.report_num=2) then
app_id:= 108;--NV('APP_ID');
FORALL i IN l_ENTITY_v.FIRST..L_ENTITY_V.LAST
INSERT INTO XX_BPM_DATA
(EnTITY,
value,data,count,
Process_ID,
Sequence,report_num)
VALUES(l_entity_v(i),
l_data_v(i),
'<A HREF="f?p='||app_id||':301:'||':APP_SESSION'||'::::P301_process_id,p301_sequence,p301_id,p301_entity:'||bpm_rec.process_id||','||bpm_rec.sequence||','||bpm_rec.id||','||l_entity_v(i)||':">'||l_data_v(i)||'</A>',
l_count_v(i),bpm_rec.process_id,
BPM_rec.sequence,
bpm_rec.report_num);
else
FORALL i IN l_ENTITY_v.FIRST..L_ENTITY_V.LAST
INSERT INTO XX_BPM_DATA(EnTITY,data,
count,
Process_ID,
Sequence,report_num)
VALUES(l_entity_v(i),
l_data_v(i),
l_count_v(i),
bpm_rec.process_id,
BPM_rec.sequence,
bpm_rec.report_num);
end if;
select round(avg(value),2) into l_actual_value from xx_bpm_data where process_id=bpm_rec.process_id and sequence=bpm_rec.sequence and report_num=bpm_rec.report_num;
update xx_test_bpm_dynamic set value=l_actual_value where process_id=bpm_rec.process_id and sequence=Bpm_rec.sequence and report_num= bpm_rec.report_num;
end loop;
Commit;
END;