sql - Apex oracle process for a Interactive grid to save data in other table - Stack Overflow

admin管理员组

文章数量:1410682

Since I dont have a collection I modified my PL/SQL following the instructions in this page: /

 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

Since I dont have a collection I modified my PL/SQL following the instructions in this page: https://mikesmithers.wordpress/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

• sql
• plsql
• oracle-apex

Share Follow edited Mar 11 at 16:38 fesm asked Mar 6 at 21:04 fesmfesm 522 bronze badges 2

• Hello ! You might add how you created and populated the table – Eternal Dreamer Commented Mar 9 at 22:39
• Please provide enough code so others can better understand or reproduce the problem. – Eternal Dreamer Commented Mar 9 at 22:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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/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

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

So the changes needed are:

• In the source sql of the IG, include an outer join to RMA_USER_EVALUATIONS to ensure that the values for "ANSWER" and "COMMENTS" are displayed if they exist (see the note about the foreign key below)

• In the "RISKSAVE - Save Interactive Grid Data" process, change the logic check if the row in RMA_USER_EVALUATIONS exists and insert if it doesn't or update if it does not. You only should look at APEX$ROW_STATUS equals "U" as explained above. If someone sets "Response" to NULL then you could delete the row.

• Change the IG attributes to only allow Edit - a user cannot add a question I assume.

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

• RMA_USER_EVALUATIONS has a column "Question" that contains the actual question text. It would make more sense to have a foreign key to the RMA_QUESTIONS_TYPE table instead. That would make it it a lot simpler to determine that the question has already been answered or not.
• RMA_USER_EVALUATIONS does not have a primary key column - that is a good practice to follow

本文标签： sqlApex oracle process for a Interactive grid to save data in other tableStack Overflow