Oracle – How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider

oraclessis

When trying to enter a SQL query with parameters using the Oracle OLE DB provider I get the following error:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
ADDITIONAL INFORMATION:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)

I have tried following the suggestion here but don't quite understand what is required:Parameterized queries against Oracle

Any ideas?

Best Solution

To expand on the link given in the question:

Create a package variable
Double click on the package variable name. (This allows you to access the properties of the variable)
Set the property 'EvaluateAsExpression' to true
Enter the query in the expression builder.
Set the OLE DB source query to SQL Command from Variable

The expression builder can dynamically create expressions using variable to create 'parametised queries'.
So the following 'normal' query:

select * from book where book.BOOK_ID = ?

Can be written in the expression builder as:

"select * from book where book.BOOK_ID = " + @[User::BookID]

You can then do null handling and data conversion using the expression builder.

Related Solutions

Sql – How to limit the number of rows returned by an Oracle query after ordering

You can use a subquery for this like

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Have also a look at the topic On ROWNUM and limiting results at Oracle/AskTom for more information.

Update: To limit the result with both lower and upper bounds things get a bit more bloated with

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Copied from specified AskTom-article)

Update 2: Starting with Oracle 12c (12.1) there is a syntax available to limit rows or start at offsets.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

See this answer for more examples. Thanks to Krumia for the hint.

SSIS: Passing parameter in OLE DB Source Editor to a table-valued function

You can use the SQL Command from Variable, it is relatively straightforward. You could build the SQL statement to pass to the Source using a Script transform to edit the variable which holds the SQL. So for example you'd set up a variable called strSQL and make the value

select * from [dbo].[udf_test](?1, ?2)

And then in the script component, replace the ?1 and ?2 values with the parameters you want to pass using string manipulation methods.

It's a little more work in the package but works well in my experience.

Best Solution

Related Solutions

Sql – How to limit the number of rows returned by an Oracle query after ordering

SSIS: Passing parameter in OLE DB Source Editor to a table-valued function

Related Question