[SOLVED] ORA-01422 error thrown when executing a stored procedure from SSIS script task, however it executes successfully when run from SQL Developer

ORA-01422 error thrown when executing a stored procedure from SSIS script task, however it executes successfully when run from SQL Developer

I am executing an Oracle stored procedure from SSIS script task and the package used to execute successfully without any issues. However, it started to fail with an ORA-01422 error suddenly.

There were no package changes, stored procedure changes or config changes from where the connection is fetched.

Also, the SELECT INTO that I have at Oracle side in the stored procedure is just doing a SELECT COUNT(1) INTO - so I am not really sure how it can fetch more than 1 row, which is what the error suggests as far as I understand.

Also, the stored procedure runs successfully when executed manually from Oracle SQL Developer, which has me baffled.

I work with mainly T-SQL and SSIS, so not sure if I am missing something while trying to read the PL/SQL error.

EDIT:

This is my script task code:

OracleConnection OracleConn = new OracleConnection(Dts.Variables["OracleConnectionString"].Value.ToString());

OracleCommand OracleCmd = new OracleCommand("TEST.TEST_DATA", OracleConn);
OracleCmd.CommandType = CommandType.StoredProcedure;

OracleParameter ins_count = new OracleParameter("ins_count", OracleDbType.Int32);
ins_count.Direction = ParameterDirection.Output;

OracleCmd.Parameters.Add(ins_count);

OracleParameter upd_count = new OracleParameter("upd_count", OracleDbType.Int32);
upd_count.Direction = ParameterDirection.Output;

OracleCmd.Parameters.Add(upd_count);

OracleParameter rev_count = new OracleParameter("rev_count", OracleDbType.Int32);
rev_count.Direction = ParameterDirection.Output;

OracleCmd.Parameters.Add(rev_count);

OracleConn.Open();
OracleCmd.ExecuteNonQuery();

string returnValue = ins_count.Value.ToString();
Dts.Variables["DestInsCount"].Value = Convert.ToInt64(returnValue);

returnValue = upd_count.Value.ToString();
Dts.Variables["DestUpdCount"].Value = Convert.ToInt64(returnValue);

returnValue = rev_count.Value.ToString();
Dts.Variables["DestRevCount"].Value = Convert.ToInt64(returnValue);

I get the error on the ExecuteNonQuery. ORA-01422: exact fetch returns more than requested number of rows\nORA-06512: at "TEST.TEST_DATA"

Here's the Oracle stored proc code:

CREATE PROCEDURE TEST_DATA
    (
      ins_count OUT number,
      upd_count OUT number,
      rev_count OUT number
    )
    AS
    log_id_v number;
    update_cnt number;
    insert_cnt number;
    reverse_cnt number;
    stg_cnt number;
    BEGIN
    
    SELECT COUNT(1) INTO stg_cnt
    FROM TEST.STAGE_DATA;
    IF (stg_cnt > 0) THEN
        --Records exist in the stage table
          
      INSERT INTO TEST.DATA 
        (
          DATE,
          CLIENT_ID,
          PROGRAM_ID,
          STATUS,
          LOG_ID
        )
      SELECT
          TD.DATE,
          TD.CLIENT_ID,
          TD.PROGRAM_ID,
          TD.STATUS,
          STD.LOG_ID
      FROM TEST.DATA TD
      INNER JOIN TEST.STAGE_DATA STD
        ON TD.DATE = STD.DATE
          AND TD.CLIENT_ID = STD.CLIENT_ID
          AND TD.PROGRAM_ID = STD.PROGRAM_ID
          AND TD.STATUS <> 'Reverse'
      WHERE TD.MODIFIED_DTM = (
          SELECT MAX(TD.MODIFIED_DTM)
          FROM TEST.DATA TD
          WHERE TD.CLIENT_ID = STD.CLIENT_ID 
          AND TD.PROGRAM_ID = STD.PROGRAM_ID
          AND TD.DATE = STD.DATE
          AND TD.STATUS <> 'Reverse'
          );
          
        --Get current LOG_ID - stage table should have only one
        SELECT DISTINCT(LOG_ID) INTO log_id_v
        FROM TEST.STAGE_DATA;
    
      
        --Get count of records inserted with reverse status in main table
        SELECT COUNT(1) INTO reverse_cnt
        FROM TEST.DATA
        WHERE STATUS = 'Reverse'
        AND LOG_ID = log_id_v;
      
      INSERT INTO TEST.DATA 
        (
          DATE,
          CLIENT_ID,
          PROGRAM_ID,
          STATUS,
          LOG_ID
        )
     SELECT
          SELECT
          STD.DATE,
          STD.CLIENT_ID,
          STD.PROGRAM_ID,
          'Update',
          STD.LOG_ID
      FROM TEST.STAGE_DATA STD
      WHERE EXISTS
          (
            SELECT 1 
            FROM TEST.DATA TD 
            WHERE TD.CLIENT_ID = STD.CLIENT_ID 
            AND TD.PROGRAM_ID = STD.PROGRAM_ID
            AND TD.DATE = STD.DATE
            AND TD.STATUS <> 'Reverse'
            );
            
      --Get count of records inserted with update status in main table
      SELECT COUNT(1) INTO update_cnt
      FROM TEST.DATA
      WHERE STATUS = 'Update'
      AND LOG_ID = log_id_v;  
      
      --Insert new records  
        MERGE INTO TEST.DATA TD
        USING TEST.STAGE_DATA STD
        ON (
            TD.CLIENT_ID = STD.CLIENT_ID 
            AND TD.PROGRAM_ID = STD.PROGRAM_ID
            AND TD.DATE = STD.DATE
          )
        WHEN NOT MATCHED THEN INSERT
        (
          DATE,
          CLIENT_ID,
          PROGRAM_ID,
          STATUS,
          LOG_ID
        )
        VALUES
        (
          STD.DATE,
          STD.CLIENT_ID,
          STD.PROGRAM_ID,
          'New',
          STD.LOG_ID
        );
        
        --Get count of records inserted with new status in main table
        SELECT COUNT(1) INTO insert_cnt
        FROM TEST.DATA
        WHERE STATUS = 'New'
        AND LOG_ID = log_id_v;
     
      --Set output variables
        rev_count := reverse_cnt;  
        ins_count := insert_cnt;
        upd_count := update_cnt;
        
    ELSE
        --when no records in stage table
        
        --Set output variables to 0
        ins_count := 0;
        upd_count := 0;
        rev_count := 0;
        
    END IF;
    END;

Please note that my stage table is empty so the code goes to the ELSE block straight and returns 0 for all the output parameters when run manually from Oracle SQL Developer. I was expecting the same output from my script task but it throws the ORA error mentioned above.

Solution

I was able to resolve this with the help of DBA. Seems like the tnsnames.ora file had an update and the host was updated to point to some other server. However, the service name and the credentials was same as before and that's why my SSIS config worked as expected.

Essentially I was connecting to a different server with the same exact structure. And in this server, the TEST.STAGE_DATA table was already populated with some data that had duplicates. So, the 01422 error was actually thrown by the merge statment because it found more than 1 record for the merge condition.

Although I have worked with SSIS to some extent, this is my first time connecting to an Oracle database hence the oversight. Thank you all for your time and inputs on this, really appreciate it.