Oracle Apex 12c：创建关联其他表结果的LOV及症状匹配疾病LOV方法咨询

Alright, let's break down your two Oracle Apex 12c problems step by step. I’ve built dozens of these LOVs over the years, so here’s a straightforward, actionable guide for each scenario:

Let’s assume your main table is something like patient_visits (where you’ll use the LOV), and you want to pull data from a separate table like disease_reference (the source of your LOV options). Here’s how to set this up, with two flexible options:

Option 1: Reusable Shared LOV (best for app-wide use) #

• Navigate to Shared Components > Lists of Values > Create
• Pick From Scratch, give it a clear name (e.g., LOV_Disease_Reference)
• Under Query Source, select SQL Query and input a query that defines what users see and what gets stored. For example:

  SELECT disease_name AS display_value, disease_id AS return_value
  FROM disease_reference
  ORDER BY disease_name
  

  Pro tip: Always explicitly name display_value and return_value—this avoids Apex guessing and causing unexpected behavior
• Save the LOV. Now, whenever you add an item (like a select list) to a form/report linked to your main table, just set its List of Values property to this shared LOV. Done.

Option 2: Page-Level LOV (for one-off use) #

If you don’t need to reuse this LOV elsewhere, skip the shared components and do this directly on your page:

• Edit the item (e.g., a select list) on your page that’s tied to the main table
• Go to the List of Values section
• Set Type to SQL Query, then paste the same query from above. Make sure the return_value matches the column in your main table that will store the selected value.

First, let’s confirm the data model I’m assuming here (adjust if your schema differs):

• symptoms: Stores symptom details (symptom_id PK, symptom_name)
• diseases: Stores disease details (disease_id PK, disease_name)
• symptom_disease_mapping: Links symptoms to their associated diseases (symptom_id FK, disease_id FK)

Each independent LOV will let users select a symptom, then display the corresponding suspected disease(s). Here’s how to build this:

Step 1: Create the symptom selection LOVs #

For each of the three independent symptom pickers:

• On your target page, add a Select List item (name them something like P1_SYMPTOM_1, P1_SYMPTOM_2, P1_SYMPTOM_3)
• In the List of Values section:
  • Set Type to SQL Query
  • Use this query to populate symptom options:

    SELECT symptom_name AS display_value, symptom_id AS return_value
    FROM symptoms
    ORDER BY symptom_name
    

• Save the item.

Step 2: Add display items for suspected diseases #

For each symptom select list, add a Display Only or Text Field item to show the linked disease(s) (e.g., P1_SUSPECTED_DISEASE_1). Set these to read-only if you don’t want users editing the disease name.

Step 3: Add dynamic actions to populate diseases automatically #

This is the magic part—when a user selects a symptom, the corresponding disease will populate instantly:

• Go to Dynamic Actions > Create
• Name it clearly (e.g., Populate Disease for Symptom 1)
• Event: Choose Change (triggers when the select list value updates)
• Selection Type: Item(s)
• Item(s): Pick your symptom item (P1_SYMPTOM_1)
• Under True Action:
  • Action: Set Value
  • Set Type: SQL Query
  • Input this query to pull the linked disease(s):

    -- For single disease per symptom
    SELECT d.disease_name
    FROM diseases d
    JOIN symptom_disease_mapping sdm ON d.disease_id = sdm.disease_id
    WHERE sdm.symptom_id = :P1_SYMPTOM_1
    
    -- For multiple diseases per symptom (use LISTAGG to combine)
    SELECT LISTAGG(d.disease_name, ', ') WITHIN GROUP (ORDER BY d.disease_name)
    FROM diseases d
    JOIN symptom_disease_mapping sdm ON d.disease_id = sdm.disease_id
    WHERE sdm.symptom_id = :P1_SYMPTOM_1
    

  • Affected Elements: Select the corresponding disease display item (P1_SUSPECTED_DISEASE_1)
• Save the dynamic action. Repeat this exact process for the other two symptom-disease pairs.

Alternative: Directly return the disease from the LOV #

If you don’t need to store the symptom ID (only the disease name), you can simplify the LOV query to skip the dynamic action entirely:

SELECT s.symptom_name AS display_value, d.disease_name AS return_value
FROM symptoms s
JOIN symptom_disease_mapping sdm ON s.symptom_id = sdm.symptom_id
JOIN diseases d ON sdm.disease_id = d.disease_id
ORDER BY s.symptom_name

Now, when a user selects a symptom, the item’s value is the disease name—no extra dynamic actions needed.

内容的提问来源于stack exchange，提问作者A. Wood