March 14, 2011 at 7:44 am
I'm trying to create a report to pull Oracle's alert logs by reading from a view based on a fixed table. I added two parameters for :StartDate and :EndDate. If these parameters are strings, the report works as designed. If I change these parameters to date/time, the report fails with ORA-01858: a non-numeric character was found where a numeric character was expected. I believe the error has to do with how the date/time parameters are passed in the SQL statement to Oracle. Is there a way in Reporting Services to see the SQL statement that is passed to Oracle and the values of the parameters? So far, the closest I get is the SQL statement with the variables but not the actual values of the variables. Also, I prefer to use the date/time parameter and not the string data type.
March 14, 2011 at 8:35 am
if you use parameters instead of constructing the strings, that issue would go away, but you can doe the fix with a little bit of REPLACE magic, i think.
you need to use the TO_DATE function on your SQL statement to format the parameters;
something like this:
dim sql = "SELECT ISNULL(YourColumn,GETDATE()) FROM SOMETABLE WHERE THEDATE > @MINDATE)"
If myDA.IsDBORA Then
sql = Replace(sql, "ISNULL", "NVL")
sql = Replace(sql, "@MINDATE", "TO_DATE('1900-01-01','yyyy-mm-dd')")
sql = Replace(sql, " + ", " || ")
sql = Replace(sql, "GETDATE()", "SYSDATE")
Else
sql = Replace(sql, "@MINDATE", "'1900-01-01'")
End If
substitute teh 1900-01-01 witht eh date from your parameters.
Lowell
March 14, 2011 at 8:59 am
Thanks for your reply but I don't think that's the problem.
My query runs fine in Reporting Services' Query Designer. Here's the statement:
select distinct
originating_timestamp,
message_text
from
sys.v_x$dbgalertext
where
originating_timestamp between
to_date(:StartDate,'MM/DD/YYYY hh24:mi:ss') and
to_date(:EndDate,'MM/DD/YYYY hh24:mi:ss')
order by
originating_timestamp asc
This works fine but when prompted for the parameter values, I think it's getting passed as a string instead of date/time in Query Designer. When I try to preview the report, that's when the report fails because I'm truly passing a date/time at that time. Is there any way to see the SQL after I pick the parameter values?
March 14, 2011 at 9:07 am
yeah, i think that's the issue; the value passed, when it comes in as a string, is not guaranteed to be in the format you are expecting:
to_date(:StartDate,'MM/DD/YYYY hh24:mi:ss')
if StartDate comes in as '2001-01-10', a truncated date/string from the application, it would fail, right? or if it's not a string in the explicit format you were expecting.
so you need to use a format function to get the data in the expected string :
How would the application be able to get your Parameter into the right format? can you do something like Dim @StartParam string = Format(Date.Now, "yyyy-MM-dd_hh-mm-ss-tt") (change the format string to whatever you see fit)
and then replace the value in your statement with teh string for StartDate?
Lowell
March 14, 2011 at 11:17 am
I think you're getting closer to my problem. Oracle doesn't like the format (or data type) of the parameter when passed to the SQL statement. I'm not writing an application but simply creating a report in Reporting Services. I've tried to manipulate the parameter by using various functions in the where clause of the SQL Statement. Unfortunately, all attempts failed when the parameter is set as a date/time data type in Reporting Services. I've also tried to manipulate the parameters within DataSet Properties by converting them to a string, truncating the time portion, etc. All have failed. I feel like Reporting Services is passing the date/time value as AM/PM no matter what I do to format it differently, and I can't get Oracle to accept the AM/PM portion of the date/time no matter what I do on that side either. Is there any way to capture what Reporting Services is sending over for parameter values.
I want to be able to see something similar to below instead of :StartDate and :EndDate.
select distinct
originating_timestamp,
message_text
from
sys.v_x$dbgalertext
where
originating_timestamp between
to_date('3/13/2011 7:00','MM/DD/YYYY') and
to_date('3/14/2011 7:00','MM/DD/YYYY')
order by
originating_timestamp asc
Help! Has anyone used a date/time parameter in RS against Oracle 11g and got it to work?
March 15, 2011 at 10:41 am
I found this solution online:
Basically, I needed to use the Oracle Provider for OLE DB instead of the Oracle Provider.
My query fails in Query Designer with "not all variables bound." However, I can preview and run my report without errors or warnings while using date/time data types for my parameters.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy