I have a interactive report which has some columns include APEX.ITEM . The data of APEX.ITEMs should store in a seperate table which this table is not the main table of my query . for example :
SELECT
A.id ID
,APEX_ITEM.SELECT_LIST_FROM_QUERY(P_IDX => 1,
P_VALUE => B.id,
P_QUERY => ' SELECT C.name,C.id from
C
where C.DETAIL_id='||B.id
) LOV_COLUMN
,APEX_ITEM.text(p_idx => 3 , P_VALUE => B.DESCRIPTION,p_maxlength =>150,p_attributes => 'style="width:350px";') DESCRIPTION
from A
left join B ON B.DETAIL_ID =A.DETAIL_ID
WHERE A.D = :P5_VALUE_ID
I want to store data of LOV_COLUMN and DESCRIPTION in B table .
for approaching this goal , first I wrote a function for generating JSON of IR for each row with filled APEX.ITEM before submit . then I wrote a merge for update and insert on this IR . here's my JSON :
function create_json()
{
var TBL =document.getElementsByClassName("classname")
var allDataArr=[];
$(TBL[1]).find('tr').each(function(index, element) {
var row_data={};
$(element).find('td').each(function(index2, element2) {
if(index2 == 0){
row_data.LOV_COLUMN=$(element2).find(":selected").val()
}
///and other columns...
});
allDataArr.push(row_data)
});
}
my merge :
merge into C
using (
SELECT ID,LOV_COLUMN,DESCRIPTION
FROM json_table
( :P5_GRID_JSON,'$[*]' ---this is a page item that contains the json of IR
columns(
ID NUMBER PATH '$.ID',
LOV_COLUMN NUMBER PATH '$.LOV_COLUMN',
DESCRIPTION VARCHAR2 PATH '$.DESCRIPTION'
)
)
) jsn
on (jsn.ID = C.DETAIL_A_ID )
WHEN MATCHED THEN
UPDATE SET
C.LOV_COLUMN = jsn.LOV_COLUMN
,C.DESCRIPTION =jsn.DESCRIPTION
WHEN NOT MATCHED then
INSERT (DETAIL_A_ID,LOV_COLUMN,DESCRIPTION)
VALUES (jsn.ID,jsn.LOV_COLUMN,jsn.DESCRIPTION) ;
for getting the JSON , I use index of td to get the selected value or input value . but there's something that can make problems for created JSON . if the user changes the order of column s, the indexes of IR's columns would change and that make the JSON corrupted. Is there any ways to create the JSON without having problems like this and use something like data-id in IG instead of column index in IR?
Also if it's seems complicated to figure out , I made an app named IR JSON on a workspace.
workspace name : ASKTOM_FRIENDLY_URL
user : [email protected]
password : 123@456_HELP
APEX_ITEM uses server side processing to handle the data that is submitted in dynamically created forms. This is documented in most of the subprogrames of this API. There should be no need to write your own code for processing the data.
Here is a solution using server side processing:
In the query, concatenate the APEX_ITEM.HIDDEN item to another column so the primary key of the rowset is included in the report.
SELECT qd.id
,apex_item.hidden(
p_idx => 1
,p_value => qd.m5questionnaire_id
)||
apex_item.select_list_from_query(
p_idx => 2
,p_query => 'select QUESTION_TEXT , ID from M5QUESTIONNAIRE_DETAILS where M5QUESTIONNAIRE_ID ='
|| :P10_M5QUESTIONNAIRE_ID
) m5questionnaire_details_id
,apex_item.text(
p_idx => 3
,p_value => a.answer
) descriptive_answer
FROM m5questionnaire_details qd
LEFT JOIN m5answersheet a ON a.m5questionnaire_details_id = qd.id
WHERE qd.m5questionnaire_id = :P10_M5QUESTIONNAIRE_ID
FETCH FIRST ROW ONLY
Then in the page process, process the posted answers using the APEX_APPLICATION.G_F0x arrays that are submitted. In this case, since you were asking for a merge, the rows are put in a collection that can then be joined in the query for the merge transaction.
DECLARE
l_collection_name VARCHAR2(100) := 'ANSWERS';
BEGIN
-- create collection
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
p_collection_name => l_collection_name);
-- some code instrumentation for easier debugging
apex_debug.info(
p_message => q'#kl debug index count : %0, %1, %2#',
p0 => APEX_APPLICATION.G_F01.COUNT,
p1 => APEX_APPLICATION.G_F02.COUNT,
p2 => APEX_APPLICATION.G_F03.COUNT);
-- loop over the index column
FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP
-- add elements to collection
APEX_COLLECTION.ADD_MEMBER (
p_collection_name => l_collection_name,
p_c001 => APEX_APPLICATION.G_F01(i),
p_c002 => APEX_APPLICATION.G_F02(i),
p_c003 => APEX_APPLICATION.G_F03(i)
);
END LOOP;
-- merge in to destination table
MERGE INTO m5answersheet a
USING (
SELECT c002 AS m5questionnaire_details_id
,c003 AS descriptive_answer
,n001
FROM apex_collections
WHERE collection_name = l_collection_name
) coll ON ( coll.m5questionnaire_details_id = a.m5questionnaire_details_id )
WHEN MATCHED THEN UPDATE
SET a.answer = coll.descriptive_answer
WHEN NOT MATCHED THEN
INSERT (
m5questionnaire_details_id
,answer )
VALUES
( coll.m5questionnaire_details_id
,coll.descriptive_answer );
END;