sqlplsqloracle-apex

Apex oracle process for a Interactive grid to save data in other table

Since I dont have a collection I modified my PL/SQL following the instructions in this page: https://mikesmithers.wordpress.com/2019/07/23/customizing-dml-in-an-apex-interactive-grid/

 DECLARE
    l_id_risk NUMBER := :P12_ID_RISK;
    l_subtype VARCHAR2(100) := :P12_SUBTYPE;
    l_evaluator VARCHAR2(100) := :P12_EVALUATOR;
    l_supplier VARCHAR2(100) := :P12_SUPPLIER;
    l_row_exists NUMBER;
BEGIN
    -- Check if the row exists in RMA_USER_EVALUATIONS
    SELECT COUNT(*)
    INTO l_row_exists
    FROM RMA_USER_EVALUATIONS
    WHERE ID_RISK = l_id_risk
        AND SUBTYPE = l_subtype
        AND QUESTION = :Question
        AND EVALUATOR = l_evaluator
        AND SUPPLIER = l_supplier;

    IF l_row_exists > 0 THEN
        -- Update the existing row
        IF :Response IS NULL THEN
            -- Delete the row if "Response" is set to NULL
            DELETE FROM RMA_USER_EVALUATIONS
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :Question
                AND EVALUATOR = l_evaluator
                AND SUPPLIER = l_supplier;
        ELSE
            UPDATE RMA_USER_EVALUATIONS
            SET ANSWER = :Response,
                COMMENTS = :Comments
            WHERE ID_RISK = l_id_risk
                AND SUBTYPE = l_subtype
                AND QUESTION = :Question
                AND EVALUATOR = l_evaluator
                AND SUPPLIER = l_supplier;
        END IF;
    ELSE
        -- Insert a new row
        INSERT INTO RMA_USER_EVALUATIONS (ID_RISK, SUBTYPE, QUESTION, ANSWER, COMMENTS, EVALUATOR, SUPPLIER)
        VALUES (l_id_risk, l_subtype, :Question, :Response, :Comments, l_evaluator, l_supplier);
    END IF;
END;

It should save the data from my front end:

Question    Response    Comments
How sustainable are the supplier's sourcing and supply chain practices? Yes Test1
How sustainable are the supplier's sourcing and supply chain practices?2    No  Test2
How sustainable are the supplier's sourcing and supply chain practices?3    Yes Test3
How sustainable are the supplier's sourcing and supply chain practices?5    No  Test4
How sustainable are the supplier's sourcing and supply chain practices?4    Yes Test5

In the table above is where the must enter an answer with yes/no static selection, and if necessary enter a comment, this table is from a table called RMA_QUESTIONS_TYPE, using this query:

SELECT ID,
    QUESTION,
    WEIGHT AS RESPONSE,
    COMMENTS
FROM RMA_QUESTIONS_TYPE
WHERE SUBTYPE = :P12_SUBTYPE

I have the attributes enabled for this interactive grid, so there is an automatically generated save button, I need that if the user modifies the above table and then clicks on that save the information is saved in this table: RMA_USER_EVALUATIONS which has these columns: ID_RIESGO, SUBTYPE, QUESTION, ANSWER, COMMENTS, EVALUATOR, PROVIDER ID. The ID_RISK_SUBTYPE should be filled with P12_SUBTYPE, the EVALUATOR column with P12_EVALUATOR,the ID_RISK column with P12_ID_RISK and the Question, answer and comments columns with the data the user fills in the app. But when I click on save it doesn't save anything, I have the attributes enabled in my interactive grid.

---UPDATE I have the SQL for the IG like this:

    SELECT q.ID,
       q.QUESTION,
       (SELECT ue.ANSWER
        FROM RMA_USER_EVALUATIONS ue
        WHERE ue.ID_RISK = :P12_ID_RISK
          AND ue.SUBTYPE = :P12_SUBTYPE
          AND ue.EVALUATOR = :P12_EVALUATOR
          AND ue.SUPPLIER = :P12_SUPPLIER
          AND ue.QUESTION = q.QUESTION) AS RESPONSE,
       (SELECT ue.COMMENTS
        FROM RMA_USER_EVALUATIONS ue
        WHERE ue.ID_RISK = :P12_ID_RISK
          AND ue.SUBTYPE = :P12_SUBTYPE
          AND ue.EVALUATOR = :P12_EVALUATOR
          AND ue.SUPPLIER = :P12_SUPPLIER
          AND ue.QUESTION = q.QUESTION) AS COMMENTS
FROM RMA_QUESTIONS_TYPE q
WHERE q.SUBTYPE = :P12_SUBTYPE
Solution

  • That code selects from APEX_COLLECTIONS. How are you populating the collection ? An interactive grid does not populate APEX_COLLECTIONS "automatically", so if that code is all there is it will never work.

    Here is an example of how to use a custom pl/sql process on an interactive grid: https://mikesmithers.wordpress.com/2019/07/23/customizing-dml-in-an-apex-interactive-grid/

    -- update --

    I had a look at your app. Thanks a lot for providing that, it is very helpful. If I understand correctly, then the user will always get a fixed number of questions in that interactive grid and put in an answer. If an answer has already been put in then he should get his previously entered answer. There are several points to take into account here. I copied the app to my own app to do some debugging. First step I took is to add code instrumentation. The original pl/sql process has DBMS_OUTPUT , I replaced those with APEX_DEBUG (docs )as good practice.

    The current interactive grid always generates a number of rows based on the following query:

    SELECT ID,
    QUESTION,
    WEIGHT AS RESPONSE,
    COMMENTS
FROM RMA_QUESTIONS_TYPE
WHERE SUBTYPE = :P5_SUBTYPE

    Now... if any of those rows are changed (eg a comment entered or a response entered), then that operation is a row update, NOT a row creation. That is because the row in the interactive grid already exists. Turn on debug, put in an answer in one or more rows and have a look at the debug generated by the statements that are moved out of the IF/THEN statement:

    BEGIN
    apex_debug.info(
        p_message => 'KL Debug Insertando nuevo registro ID_RISK: %0, SUBTYPE: %1, QUESTION: %2, RESPONSE: %3, COMMENTS: %4, EVALUATOR: %5, SUPPLIER: %6',
        p0 => l_id_risk,
        p1 => l_subtype,
        p2 => :Question,
        p3 => :Response,
        p4 => :Comments,
        p5 => l_evaluator,
        P6 => l_supplier);
    apex_debug.info(
        p_message => 'kl debug:  :APEX$ROW_STATUS: %0',
        p0        => :APEX$ROW_STATUS);    

    IF :APEX$ROW_STATUS = 'C' THEN

    enter image description here

    The debug shows that APEX$ROW_STATUS equals "U".

    So the changes needed are:

    There are a couple of design decisions you took that make development harder: