ORA-01427错误排查：存储过程中单行子查询返回多行问题解决

Got it, let's break down why you're hitting the ORA-01427 error when running this query inside the stored procedure, even though it works standalone. The core issue is that one of your inline subqueries is returning multiple rows for some records in the dataset processed by the stored procedure—something that didn't happen when you ran the query manually. Let's fix this step by step.

1. Fix the Critical Table Alias Conflict #

First, there's a major alias collision in your cursor query that's almost certainly causing unexpected results:

-- Original problematic subquery (alias A clashes with outer table a)
(SELECT EXTRACT_ACCT(HOST_ACCT_INFO, 'SUFFIX') FROM LEA_AGREEMENT_GROUPGL_MAP A, FA_ACCTCATG_M B WHERE ... and AGREEMENTID = a.caseid)

The inner LEA_AGREEMENT_GROUPGL_MAP uses alias A, which conflicts with the outer table alias a from nbfc_txn_advice_dtl. This makes the database resolve a.caseid incorrectly, potentially pulling multiple rows. Fix the alias to avoid confusion:

(SELECT EXTRACT_ACCT(LAGM.HOST_ACCT_INFO, 'SUFFIX') 
 FROM LEA_AGREEMENT_GROUPGL_MAP LAGM, FA_ACCTCATG_M B 
 WHERE EXTRACT_ACCT(LAGM.HOST_ACCT_INFO, 'ACCTCATG') = B.ACCTCATG 
 AND B.GROUPID = 'FA' 
 AND LAGM.ACTIVE_FLAG = 'Y' 
 AND LAGM.AGREEMENTID = a.caseid) "Account No"

2. Ensure All Subqueries Return Exactly One Row #

Every inline SELECT ... FROM ... WHERE subquery must return only one row. If your business logic allows multiple matches, use an aggregate function like MAX() or MIN() to get a single value, or adjust the WHERE clause to enforce uniqueness.

Examples of fixes:

• For the customer CIF number (if multiple cif_no exist for a customerid):

  (SELECT MAX(cif_no) FROM nbfc_customer_m WHERE customerid = a.bpid)
  

• For the agreement number:

  (SELECT MAX(AGREEMENTNO) FROM lea_agreement_dtl WHERE AGREEMENTID = a.caseid)
  

• For charge descriptions (if chargeid isn't a primary key in nbfc_charges_m):

  (SELECT MAX(rpad(chargedesc,35,' ')) FROM nbfc_charges_m WHERE chargeid = a.chargeid)
  

3. Refactor Subqueries to JOINs (Better Performance & Reliability) #

Inline subqueries are prone to this error and can be slow. Rewriting your query with JOINs makes it easier to control row counts and improves performance:

CURSOR VAT1 IS 
SELECT 
    NVL(ncm.cif_no, '') || NVL(EXTRACT_ACCT(LAGM.HOST_ACCT_INFO, 'SUFFIX'), '') "Account No",
    LPAD(a.caseid,6,0) Loan_No,
    NVL(lad.AGREEMENTNO, '') AGREEMENTNO,
    LPAD(a.productid,3,0) Scheme_ID,
    NVL(RPAD(lsm.schemedesc,35,' '), '') SchemeDesc,
    TO_CHAR(a.advicedate,'ddmmyyyy') advicedate,
    IT_CONV(a.adviceamt) adviceamt,
    RPAD(a.chargeid,6,' ') chargeid,
    NVL(RPAD(ncm_charges.chargedesc,35,' '), '') "Charge Description",
    IT_CONV(a.chargeamt) chargeamt,
    CASE WHEN COUNT(y.txnadviceid) > 0 THEN 'Y' ELSE 'N' END Paid,
    LPAD(b.chargeid,6,0) "VAT ChargeID",
    NVL(RPAD(ncm_vat_charges.chargedesc,35,' '), '') "VAT Charge Description",
    IT_CONV(b.chargeamt) "VAT Amount"
FROM 
    (SELECT * FROM nbfc_txn_advice_dtl WHERE status = 'A' AND tax_applicable = 'Y' AND ptxnadviceid IS NULL) a
JOIN 
    (SELECT * FROM nbfc_txn_advice_dtl WHERE status = 'A' AND ptxnadviceid IS NOT NULL) b 
    ON a.txnadviceid = b.ptxnadviceid
LEFT JOIN 
    nbfc_customer_m ncm ON ncm.customerid = a.bpid
LEFT JOIN 
    lea_agreement_dtl lad ON lad.AGREEMENTID = a.caseid
LEFT JOIN 
    lea_scheme_m lsm ON lsm.schemeid = a.productid
LEFT JOIN 
    nbfc_charges_m ncm_charges ON ncm_charges.chargeid = a.chargeid
LEFT JOIN 
    nbfc_charges_m ncm_vat_charges ON ncm_vat_charges.chargeid = b.chargeid
LEFT JOIN 
    LEA_AGREEMENT_GROUPGL_MAP LAGM 
    JOIN FA_ACCTCATG_M B ON EXTRACT_ACCT(LAGM.HOST_ACCT_INFO, 'ACCTCATG') = B.ACCTCATG 
        AND B.GROUPID = 'FA' 
        AND LAGM.ACTIVE_FLAG = 'Y'
    ON LAGM.AGREEMENTID = a.caseid
LEFT JOIN 
    nbfc_pmnt_dtl y ON a.txnadviceid = y.txnadviceid 
        AND a.status = 'A' 
        AND y.status IS NULL 
        AND a.tax_applicable = 'Y' 
        AND a.ptxnadviceid IS NULL
GROUP BY 
    ncm.cif_no, EXTRACT_ACCT(LAGM.HOST_ACCT_INFO, 'SUFFIX'), a.caseid, lad.AGREEMENTNO, 
    a.productid, lsm.schemedesc, a.advicedate, a.adviceamt, a.chargeid, ncm_charges.chargedesc, 
    a.chargeamt, b.chargeid, ncm_vat_charges.chargedesc, b.chargeamt;

The GROUP BY ensures each main record returns only one row, even if there are multiple matches in joined tables.

4. Add Error Handling to Debug #

If you still hit errors, add exception handling inside your loop to pinpoint exactly which record is causing the issue:

BEGIN
    fHandle := UTL_FILE.FOPEN('UAEDB', 'VAT', 'W');
    FOR I IN VAT1 LOOP
        BEGIN
            v_str:= NULL;
            v_str:= I."Account No" || I.Loan_No || I.AGREEMENTNO || I.Scheme_ID || I.SchemeDesc || 
                    I.advicedate || I.adviceamt || I.chargeid || I."Charge Description" || 
                    I.chargeamt || I.Paid || I."VAT ChargeID" || I."VAT Charge Description" || I."VAT Amount";
            UTL_FILE.PUTF(fHandle, v_str);
            UTL_FILE.PUTF(fHandle, '\n');
        EXCEPTION
            WHEN OTHERS THEN
                UTL_FILE.PUTF(fHandle, 'ERROR processing txnadviceid ' || a.txnadviceid || ': ' || SQLERRM || '\n');
                CONTINUE; -- Skip bad record and keep processing
        END;
    END LOOP;
    UTL_FILE.FCLOSE(fHandle);
EXCEPTION
    WHEN UTL_FILE.INVALID_PATH OR UTL_FILE.INVALID_MODE THEN
        DBMS_OUTPUT.PUT_LINE('File error: Invalid path or mode');
        IF UTL_FILE.IS_OPEN(fHandle) THEN
            UTL_FILE.FCLOSE(fHandle);
        END IF;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        IF UTL_FILE.IS_OPEN(fHandle) THEN
            UTL_FILE.FCLOSE(fHandle);
        END IF;
        RAISE;
END ;

These changes should resolve the ORA-01427 error and make your stored procedure more robust.

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