ORA-01008, you drove me crazy!

If you're like me and haved used SQL Server Reporting Services (SSRS), writing reports hooked to a SQL Server back end for most of your SSRS life, a little Oracle can be fun and a tad frustrating because of the niggly differences that can make one's head hurt.

Recently, I created my first parameterized report with an Oracle datasource. It's just a bit different that it tripped me up when I tried using parameters. The problem seems to be more an SSRS GUI bug more than anything. If you're like me and like to dive in before investigating if there are subtle differences when shifting to a different back end for a datasource, this could happen to you. It turns out that depending on the provider you use to access a datasource, Oracle in this case, the way the provider interprets parameters in a SQL query may be different. In T-SQL, the parameter is preceded by the "@", e.g., @accountID. And would look like this,

SELECT accountName FROM Account WHERE accountID = @accountID

With an Oracle datasource, using Microsoft OLE DB as the provider, the parameter is represented by a positional parameter, not a named parameter like in T-SQL. This is all well and good. But if, again, you're like me, and started using the first example you googled, you could end up doing it this way,

SELECT accountName FROM Account WHERE accountID = :accountID

with a named parameter preceded by the a colon (":") .

Now there's nothing wrong with that syntax, SSRS takes it and works perfectly fine when you are in Data tab and click "!" to execute it. It dutifully prompts for the value of :accountID, like so.

Figure 1 - Query Parameters prompt

Here's the twist, that query got executed when the Generic Query Designer toggle was on. Like below.

Figure 2 - Generic Query Designer

But try the same thing with the toggle off, i.e., coding the SQL statement by hand. Still in the same tab except with the Generic Query Designer toggle off. And you get this!

Figure 3 - ORA-01008 Not all variables are bound (Microsoft OLE DB Provider for Oracle)

The answer lies in the way the OLE DB provider for Oracle recognizes its parameters. It wants its parameters represented with a placeholder (positional parameter), specifically a question mark ("?"). E.g.,

SELECT accountName FROM Account WHERE accountID = ?

It then automatically creates the parameter of the report for you using a naming convention of Parameter1, Parameter2 and so on. Parameters are then bound in the order they appear in the query. Its just like any SSRS report parameter, except it created it with a naming convention of its choosing.

Figure 4 - Auto Generated Parameter, Parameter1

Such a weird little thing that led me to more googling, and head scratching. SSRS does it diffently with the Generic Query Designer toggled off. Obviously it took into consideration the provider of the datasource, OLE DB in this case, and syntax checked the SQL Query. Why it does it differently when the Generic Query Designer is toggled on, who knows. Maybe my Dachshund has the answer... time now to bond with him.

Oh, some useful reading from MSDN on OLE DB and positional parameters -http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.aspx.