i have a process on my page which inserting some tables. one table may have more than one row. say
id1, id2, attr1, desc
1 1001, 1, abc
2, 1001, 4, xyz
3, 1001, 5, hhh
so on. id2 is a fk key in this table. for same id2 it may have more than one row.
i have process and following is part of it but getting error that is PLS-00224: object 'P30_QUES' must be of type function or array to be used this way .
select count(*) into vv_count from table1; -- it may have 7 or 8 questions
FOR i IN 1 .. v_count LOOP
select code into v_code from table1 where code = :p30_ques(i);
if :p30_ques(i) is not null then
INSERT INTO table2 (id1, id2, attr1, desc)
values (null,fk_value, v_code,nvl(:p30_ques(i),null));
i appriciate your help!
i have almost solved the issue myself but still having an issue.
i have created cursor and collection type then usiing for loop and all working ok but i am not getting session state as i am doiing as follows.
v_desc := APEX_UTIL.GET_SESSION_STATE(':p30_ques(i)'); -- p.s :p30_ques(i) has both char and numeric value. say. :p30_ques1 (y/n), :p30_ques2 ('AA' or ZZ')
:p30_ques3 ('ahhaa'), :p30_ques4('asdasja')
in my table all first 3 cols are pouplated but last col (desc) not.
any reason. please help?
Actually I was looking current session item value in my process for data insert.
i have been used as v_desc := v('p30_ques'||rec(i).my_code); and its working ok.
i was doing wrongly as v_desc := v(':p30_ques.rec(i).my_code');