Results 1 to 6 of 6

Thread: dynamic sql

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: dynamic sql

    Hello Gurus

    My SQL statements is formed from the dynamic table which has fields for for , where and order by clause. I am using same bind variables twice in single select statement . It is giving me error , All varuable not bound. Any solution for this > My table is like this. This is bit urgent

    SQL> select * from tms_statement_definition where format_cd='AA' and
    section_typ_cd='DT2';

    PRG_NM FORMAT_CD SEC
    ---------------------------------------- ---------- ---
    FROM_CLAUSE
    ------------------------------------------------------------------------------------------------------------------------
    WHERE_CLAUSE
    ------------------------------------------------------------------------------------------------------------------------
    ORDER_BY
    ------------------------------------------------------------------------------------------------------------------------
    USING_CLAUSE
    ----------------------------------------------------------------------
    uoq1101 AA DT2
    FROM tms_conversion_data tcd, wrk_tracking_on_hold_non_ba wtohnb,
    wrk_tracking_output_control wtoc , (select
    to_number(min(reference_cd))-1 ref from
    wrk_tracking_output_control wtoc2 WHERE wtoc2.bch_no = :v_Batch_No AND wtoc2.agy_cd = :v_Agy_Cd AND wtoc2.file_typ_cd = :v_File_Typ AND wtoc2.db_output_cd = 'N' AND wtoc2.output_reasn_cd = 'T'
    AND wtoc2.sent_ind IS NULL) wtoc_min
    WHERE wtoc.bch_no = :v_Batch_No AND wtoc.agy_cd = :v_Agy_Cd AND
    wtoc.file_typ_cd = :v_File_Typ AND wtoc.db_output_cd = 'N' AND
    wtoc.output_reasn_cd = 'T'
    AND wtoc.sent_ind IS NULL AND wtohnb.on_hold_rec_no = wtoc.rec_no
    AND wtohnb.card_count_no = wtohnb.schem_card_count_no AND
    wtohnb.on_hold_reasn_cd = 'A'
    AND tcd.input_cd(+) = wtohnb.schem_cd AND tcd.conv_typ_nm(+) =
    'PARTNER CARD LENGTH'
    AND wtohnb.flt_dt BETWEEN tcd.start_dt(+) AND tcd.end_dt(+) AND
    wtohnb.aln_cx_cd = 'BA' AND to_number(wtoc.reference_cd) > wtoc_min.ref
    ORDER BY wtoc.reference_cd
    USING v_Batch_No, v_Agy_Cd, v_File_Typ

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    From what I can tell about your unformatted and incomplete code I am not sure about your error either. I might be your opening sub-select needs its own using clause but that is just a guess.

    In the future use the VB codes to make your code readable and give us everything with the select not just a portion.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to add a value in the USING clause for each usage of the variables, and yours are used twice. i.e it should be:

    USING v_Batch_No, v_Agy_Cd, v_File_Typ, v_Batch_No, v_Agy_Cd, v_File_Typ

    For example:

    Code:
    SQL> begin
      2    execute immediate 'update emp set sal=sal where ename=:p1 and ename=:p1'
      3    using 'KING';
      4  end;
      5  /
    begin
    *
    ERROR at line 1:
    ORA-01008: not all variables bound
    ORA-06512: at line 2
    
    
    SQL>  begin
      2     execute immediate 'update emp set sal=sal where ename=:p1 and ename=:p1'
      3     using 'KING', 'KING';
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    It may seem dumb, but the USING clause works on variable position rather than name!

  4. #4
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    same error

    I tried to use 6 six variables in where clause as there are 6 bind variables. Still it is giving the error "Bound variables not found " . I did it exactly you suggested still it is not working . Any ideas or suggestions . Its urgent,.


    Sorry . The error is different. It says bind variables does not exist. The error no is 1006.
    Last edited by varun_751980; 02-22-05 at 02:45.

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Urgent

    Hello

    Plz have a look at this . This is very very urgent.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Please post your full*, exact code and the exact error message. At the moment we don't have much to go on.

    * Preferably a cut-down, short, simplified verison that still shows the problem.

Posting Permissions

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