Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Urgent help needed

    Hello

    In my dynamic sql can the using clause be a part of sql statement. This is my small script. In this script using clause is also fetched from the table and sql statement is executed. But it gives error. Is this possible ?

    If i remove the using clause and open the cursor explicitly giving USING clause it works ?

    Please help. This is very very urgent.
    set serveroutput on

    declare

    TYPE r_File_Rec_Val IS REF CURSOR;

    c_Read_Data r_File_Rec_Val;

    v_Batch_No VARCHAR2(20) := 1045;
    v_File_Typ VARCHAR2(20) := 'AUBAPT';
    v_Agy_Cd VARCHAR2(20) := 'AA';
    var VARCHAR2(20);
    v_From_Clause varchar2(32000);
    v_Where_Clause varchar2(32000);
    v_Order_By varchar2(32000);
    v_Using_Clause varchar2(32000);
    v_Using1 varchar2(32000);
    v_Sql_Stmnt VARCHAR2(32000);

    begin

    SELECT tsd.from_clause,
    tsd.where_clause,
    tsd.order_by,
    tsd.using_clause
    INTO v_From_Clause,
    v_Where_Clause,
    v_Order_By,
    v_Using_Clause
    FROM tms_statement_definition tsd
    WHERE tsd.prg_nm = 'uoq1101'
    AND tsd.format_cd = 'AA'
    AND tsd.section_typ_cd = 'DT2';

    v_Sql_Stmnt := 'SELECT lpad(to_number(wtoc.reference_cd) -
    wtoc_min.ref , 5 ,0)';
    v_Sql_Stmnt := v_Sql_Stmnt ||' '||
    v_From_Clause ||' '||
    v_Where_Clause ||' '||
    v_Order_By ||' '||
    v_Using_Clause;



    OPEN c_Read_Data FOR v_Sql_Stmnt;


    dbms_output.put_line(nvl(var,'hello varun'));

    exception

    when others
    then
    dbms_output.put_line('some error');
    dbms_output.put_line(substr(SQLERRM,1,255));
    end;

    /

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

    This is really urgent

    Hello Gurus

    This was suggested during my code reviews. I dont know if this is possible ? Plz help

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The using clause is not put into the string which holds your sql and is part of the open statement.

    i.e.

    OPEN c_Read_Data FOR v_Sql_Stmnt USING v_var1;

    Now for your generic code youll either have to have the same bind variables everytime or dont bother using the USING clause and just put the variables in as literals. Of course if you are going to execute this dynamic sql a lot then it is going to bve very inefficient, if its just occasionally the overhead of hard parsing isnt going to be that great.

    Alan

Posting Permissions

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