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.
4 comments:
do you know how, to date time parameter could be used in an oracle condition in one of this types of reports? because i tried to formatted as to_date but always raise me an error =(
How we work is we use TO_DATE(:Data_Param) and then we pass in text in Report Builder (i.e. 01-JAN-2012) and when you've deployed the report if you set the Parameter Type to "Date" then you get the picker and everything seems to work ok. Does that help?
We have also found that using TO_DATE(:Date_Parameter) works well.
When testing the dataset through report builder you must use the standard Oracle date format (usually DD-MON-YY or DD-MON-YYYY unless parameter NLS_DATE_FORMAT has been changed).
When entering the date when testing the report through report builder or using the report through reporting services or Sharepoint then you can use the date picker or just about any standard format and that seems to work.
How to use :parameter with SQL LIKE command?:
SELECT * FROM DUAL WHERE NAME LIKE '%text (:parameter) text%';
For example in Bash you have $variable and if you want to use inside text:
"Some_text${variable}some_text"
Post a Comment