如何基于NVL条件批量校验ap_supplier_sites_all表的org_id参数

Got it, let's solve this. Instead of manually looping through each org_id from ap_supplier_sites_all, you can directly reference the table in your query to pass every unique org_id to your function in one go.

Basic Query to Get Validation Results for All org_ids #

This query will return each unique org_id along with its corresponding validation result ('Y' or 'N'):

SELECT DISTINCT
       assa.org_id,
       NVL(GECM_ICP_PKG.GECM_GET_PARAMETER_VALUE_FNC('GECM_SCX_VALIDATION', 'REN_SCX_VALIDATION', assa.org_id, NULL, NULL), 'N') AS validation_status
FROM ap_supplier_sites_all assa;

Key Details: #

• DISTINCT ensures you only get one result per org_id (since a single org might have multiple supplier site entries in the table)
• We directly substitute assa.org_id for the v_org_name parameter in the function call—this lets the query iterate over all unique org IDs automatically, no loops needed
• The validation_status column clearly shows whether each org has the validation enabled or not

Filter for Only Validated org_ids (Optional) #

If you only need to see org_ids where the validation result is 'Y', add a WHERE clause:

SELECT DISTINCT
       assa.org_id
FROM ap_supplier_sites_all assa
WHERE NVL(GECM_ICP_PKG.GECM_GET_PARAMETER_VALUE_FNC('GECM_SCX_VALIDATION', 'REN_SCX_VALIDATION', assa.org_id, NULL, NULL), 'N') = 'Y';

This approach is efficient and avoids the overhead of writing a loop (like a PL/SQL block) to process each org ID individually.

内容的提问来源于stack exchange，提问作者Pasha Md