Unexpected ORA-01422: exact fetch returns more than requested number of rows

From: <john.mcafee_at_newscale.com>
Date: 26 Sep 2006 21:38:59 -0700
Message-ID: <1159331939.338608.283460@e3g2000cwe.googlegroups.com>

I'm stumped by this one.

The code below (followed by many similar fragments for different tables and columns) has been working to upgrade the schema of our database at various customer sites. For one customer, it worked fine in a first round of testing, but then when the schema was copied again from production for another round of testing, we began to get ORA-01422: exact fetch returns more than requested number of rows.

Any ideas why this might be happening?

Short of re-writing to use a cursor, any suggestions on how avoid the condition?

Thanks in advance -

###

DECLARE
  rowCount int;
  dataPrecision number;
  dataScale number;
  isNullable varchar(2);
BEGIN
  SELECT COUNT(*) INTO rowCount FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('BVDefAgreement') AND COLUMN_NAME = UPPER('TotalAgreementBudget');
  IF (rowCount > 0) THEN
    SELECT DATA_PRECISION INTO dataPrecision FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('BVDefAgreement') AND COLUMN_NAME = UPPER('TotalAgreementBudget');

    SELECT DATA_SCALE INTO dataScale FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('BVDefAgreement') AND COLUMN_NAME = UPPER('TotalAgreementBudget');

    SELECT NULLABLE INTO isNullable FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = UPPER('BVDefAgreement') AND COLUMN_NAME = UPPER('TotalAgreementBudget');

    IF ((dataPrecision IS NULL) OR (dataScale IS NULL) OR
(dataPrecision <> 38) OR (dataScale <> 2)) THEN

      IF (isNullable <> 'Y') THEN
        EXECUTE IMMEDIATE 'ALTER TABLE BVDefAgreement MODIFY


(TotalAgreementBudget NULL)';

      END IF;
      EXECUTE IMMEDIATE 'ALTER TABLE BVDefAgreement ADD xTempCol number
NULL';
      EXECUTE IMMEDIATE 'UPDATE BVDefAgreement SET xTempCol =
TotalAgreementBudget';
      EXECUTE IMMEDIATE 'UPDATE BVDefAgreement SET TotalAgreementBudget
= NULL';
      EXECUTE IMMEDIATE 'ALTER TABLE BVDefAgreement MODIFY


(TotalAgreementBudget number(38,2))';

      EXECUTE IMMEDIATE 'UPDATE BVDefAgreement SET TotalAgreementBudget
= xTempCol';
      EXECUTE IMMEDIATE 'ALTER TABLE BVDefAgreement DROP (xTempCol)';

    END IF;
  END IF;
END;
/

### Received on Tue Sep 26 2006 - 23:38:59 CDT