Return Oracle LONG value from SSIS Execute SQL Task

Question

I have a procedure in Oracle that takes a varchar2 as input and returns a long.

I want to run that procedure from SSIS, and then use that value in subsequent SSIS steps.

I have a sequence container, so once I have received that result, I can use it. But I am failing at calling the procedure. I try various things, and always get an error.

Here is what I have:

In Oracle, my procedure (and it's not mine, so I can't change it) is defined as

P_CREATE_BATCH (V_NAME IN VARCHAR2, V_BATCH OUT LONG)

In SSIS, I have a Variable called v_batch which is Int32 and has a value of 0. I also have an Execute SQL Task. It has an OLE connection to the Oracle database. The SourceType is Direct input, the ResultSet is Single row, and my SQL Query is

BEGIN
  P_CREATE_BATCH ('FACT_METER',?);
END;

I have Parameter Mapping from user::v_batch, direction Output, Data Type LONG, Parameter 0. I have a Result Name called MyBatch, with a Variable Name of User::v_batch.

And when I run it, I get the error:

[Execute SQL Task] Error: Executing the query "BEGIN P_CREATE_BATCH ('FACT_METER',?); END;" failed with the following error: "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'P_CREATE_BATCH' ORA-06550: line 1, column 7: PL/SQL: Statement ignored". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have tried it with my variable having at data type of Int64. I have tried with Result Set of none. I have forgotten how many different permutations I've tried. Sometimes I get a different error, but it's because I'm trying something that is obviously wrong.

How do I successfully call an Oracle procedure that returns a LONG, and get that value in SSIS?

Update, based on comment: The underlying table that is written to (in Oracle) has a data type of number(18). I have made a version of the Oracle procedure that returns NUMBER, instead of LONG.

When I call it, it still gives me an error, and I think it's because I need a different type for my SSIS variable. The procedure does run, and then returns the error

[Execute SQL Task] Error: Executing the query "BEGIN P_CREATE_BATCHK ('FACT_METER',?); END;" failed with the following error: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have my parameter as Data Type NUMERIC and my variable as Int32, and then Int64, then Double, then UInt64, then Decimal. There aren't a lot of options left.

Update If I have my SSIS variable as Double, and my parameter data type as Double, with no result set, the task runs. I don't know yet if I can access the return value, but there isn't an error, so it's progress.

Are you aware that in Oracle a LONG is not a numeric type, but rather an obsolete type of LOB? — Bob Jarvis - Reinstate Monica
@BobJarvis I was not. And that might be a vital clue to help us resolve this. — thursdaysgeek
@BobJarvis - even though that's a short note, we did a lot of searching, so I think the question could still be valid. If you were to convert you comment to an answer, and suggest using NUMBER on the Oracle side, DOUBLE on the SSIS side, I would accept it. — thursdaysgeek

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2018-08-06T21:46:47

LONG is not a numeric type in Oracle - it's a deprecated variety of LOB. In Oracle you should use the type NUMBER to store numeric values. NUMBER is the basic data type used to store a base-10 floating point value with 38 digits of precision, and thus does not suffer from the problem of having values (such as 0.1) which cannot be represented in e.g. IEEE-754 binary floating point values. On the SSIS side this would correspond to, variously, DOUBLE, INT, DECIMAL, etc - i.e. all the numeric types.

Return Oracle LONG value from SSIS Execute SQL Task

1 Answers