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.
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.