Date/Time parameter for Oracle view

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • I found this solution online:

    [/url]

    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