Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Location
    US
    Posts
    29

    Unanswered: parameterised query??

    hi there

    I am doing a quick report in Microsoft Reporting Services with an ORACLE 10g database as the datasource...
    The problem i'm having is with the two date parameterised lines in the FROM clause. The query works fine if i substitute hardcoded date in, but as soon as i try and enter a date param as follows
    where dt1 >= :dateparam1
    and dt2 <= :dateparam2

    then i get the following error:
    ora-01008 not all variables bound

    I dont understand what's wrong with my SQL.
    If i connect to a SQL Server datasource then the way of using parameters is
    where dt1 >= @dateparam1
    and dt2 <= @dateparam2
    and then Reporting Services picks this up as params that the user must enter and allows one to enter date values for param1 and param2.
    Yet with ORACLE, even although everywhere i look it says to indicate ORACLE params as :dateparam, this still does not seem to work!!


    The SQL looks is as follows:

    select
    revr_name as "person", revr_type as "queue", last_upd as "date", action, count(*)
    from dedb.th_manu_work
    where action = 'SKIP'
    and last_upd >= :frm_dt
    and last_upd <= :to_dt
    group by
    revr_name, revr_type, last_upd, action
    order by
    revr_name, revr_type, last_upd, action

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I admit I can not help you directly, but have an question & suggestion.

    Is the parameter being passed to Oracle really a DATE datatype or a string that can be interpreted as a DATE?

    My suggestion is to simply test your ability to pass in a parameter to Oracle.
    The easiest data type to pass to Oracle would be a NUMBER; since it does not require quotes or other conversion.

    SELECT NAME FROM TEST_TBL WHERE ID = :my_id;

    Can you obtain desired/expected results from Oracle from a query similar to line above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Location
    US
    Posts
    29
    OK, I now created a simple table in ORACLE DB as follows:

    create tmp_table1 (
    id integer,
    descr varchar(100)
    )

    and then populated table as follows:
    insert into tmp_table1 values (1, 'value1');
    insert into tmp_table1 values (2, 'value2');
    .
    .
    .
    insert into tmp_table1 values (10, 'value10');

    I then created a report having the following SQL:
    select id, descr from tmp_table1
    where id = :id

    Again i got the error "ORA-01008 : not all variables bound"

    So there is something fundamentally wrong with the way i'm showing the param in the SQL query.

    Any ideas will be welcome. i will try and ask at a MS Reporting Services forum too....

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    To have the user enter you would do it like this

    Code:
    SELECT NAME FROM TEST_TBL WHERE ID = &my_id;
    to use the colon you have to bind the variable first by using the VARIABLE command.

    Code:
    VARIABLE my_id NUMBER
    
    BEGIN
    :id := 23;
    SELECT NAME FROM TEST_TBL WHERE ID = :my_id;
    end;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2009
    Location
    US
    Posts
    29
    Quote Originally Posted by beilstwh View Post
    To have the user enter you would do it like this

    Code:
    SELECT NAME FROM TEST_TBL WHERE ID = &my_id;
    to use the colon you have to bind the variable first by using the VARIABLE command.

    Code:
    VARIABLE my_id NUMBER
    
    BEGIN
    :id := 23;
    SELECT NAME FROM TEST_TBL WHERE ID = :my_id;
    end;
    /
    Thank you for your help.
    I tried it out and had the following SQL text in Reporting Services:

    select id, descr
    from tmp_table1
    where id = &my_id

    Unfortunately it still gives me the error msg: ORA-01008: not all variables bound


    The funny thing though (and i know this doesnt help me but) is when i enter the above SQL into a SQL client tool like PL/SQL Developer, it understands that &my_id is meant to be in input value, and so it actually prompts for the value. So i'm still baffled by WHY SQL Reporting Services cannot figure this out!??

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Sandra82 View Post
    So i'm still baffled by WHY SQL Reporting Services cannot figure this out!??
    I'm sure SSRS is as baffled as you as to why you insist on supplying it with something it does not understand. &variable is a feature of SQL*Plus, SSRS does not know what it means. :variable is an indicator for Oracle to wait for value binding, but again, does not mean anything to SSRS, so the variables are not bound.

    Try using @variables instead - I'm sure SSRS will understand that and substitute values as you expect it to.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •