CODEWITHSUNDEEP
oracle-databasereporting-services
thotwielder
thotwielder

Reputation: 1707

Ssrs on Oracle date parameter

I am using SSRS 2005 to run against a Oracle data source. I used this simple query

    SELECT order_number FROM apps.oe_order_headers_all  
    WHERE ordered_date >= :start
    AND ordered_date  < :end + 1
    and rownum = 1

Firstly I got:

ORA-01745: invalid host/bind variable name

I ignore the error and click refresh (to let SSRS determine the columns returned and parameters used)

SSRS prompts me for the values of the parameters, I tried 01/01/2014, 01-JAN-2014, 01-01-2014 for both parameters but all got this error:

Cannot bind type System.String as Blob. (System.Data.OracleClient)

The order_number is NUNBER and ordered_date is DATE in oracle.

Not sure it's a pl sql thing or SSRS thing?

Upvotes: 1

Views: 1162

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

Not sure if the second error is just a follow-up to the initial one, but the first error is because start is a reserved word in Oracle; you'll get an ORA-01745 from this in SQL*Plus or SQL Developer too:

SQL> var start varchar2(10);
SQL> select :start from dual;
select :start from dual
        *
ERROR at line 1:
ORA-01745: invalid host/bind variable name

end is allowed though. If you change the bind variable name to, say, :start_date - and add the explicit to_date(:start_date, 'YYYY-MM-DD') and to_date(:end_date, 'YYYY-MM-DD') that OracleUser suggested so you know what format to enter the variables in - that will go away.

I can only imagine that will resolve the second error too, which I guess is coming from the parameter being in an odd state at that point.

Upvotes: 4

Related Questions