SSRS: Working With Oracle Database Parameters In Report Builder 3

This Knol gives a simple example which shows some of the tricks/ ways of working you might like to use then writing SSRS reports against a back-end Oracle system.


The first thing to do is to open Report Builder, create a new dataset, connect to any Oracle database (the SQL we'll be using will run against any system), and then paste in the SQL;

SELECT
  TRUNC(SYSDATE) as TODAYS_DATE,
  :Where_Test as TEST_PARAMETER
FROM DUAL
WHERE 'TEST' = :Where_Test

As you can see this is pretty simple; it is just returning today's date and the parameter that's being used but only if the user passes in 'TEST'.

Give this a run (pass in TEST) and you'll see something like;

Now if you change the parameter you're passing in to 1234 and re-run;

The error is the standard "ORA-01722: invalid number" error and is due to Oracle trying to convert 'TEST' (in the SQL) to a number which of course fails.

You might assume that setting your "Data Type" to Text for the parameter would work;

You'd be wrong. It makes absolutely no difference.

The only way to resolve this problem is to update the original SQL to;

SELECT
  TRUNC(SYSDATE) as TODAYS_DATE,
  :Where_Test as TEST_PARAMETER
FROM DUAL
WHERE 'TEST' = TO_CHAR(:Where_Test)

By adding the TO_CHAR you are converting whatever value is passed into automatically to a character. Running this SQL (with 1234 as the parameter) will give you;

There is no error (there is no result because '1234' is not 'TEST').

You get exactly the same problems with dates.