Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Unanswered: ORDER SIBLINGS BY not working properly with BULK COLLECT

    I have a dynamically build SQL statement which contains ORDER SIBLINGS BY and is fetched using BULK COLLECT. When I run a sample SQL stmt similar to wht is generated dynamically, I get the sort order correctly. But when it is run using BULK COLLECT the sort order is different.

    Any idea why and how to overcome this ??

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You wrote the SQL & can not understand what is wrong.
    We (TINW) have not seen the SQL so why do you expect us to be able to solve your mystery without any meaningful clues?
    My car is not working properly. Tell me how to make my car do what I expect.
    Last edited by anacedent; 02-20-07 at 17:10.
    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
    Jan 2006
    Posts
    6
    Dynamic SQL is this :

    sql_stmt_ := 'SELECT ms.component_part Part_No,
    :top_parent_value_ Top_Parent,
    ms.part_no Parent_Part,
    ipcp.configuration_id,
    LEVEL level_no,
    LPAD('' '',LEVEL+1, ''X'') Level_Graph
    FROM tab1 ipcp,
    tab2 ms
    WHERE mrp_order_code LIKE :mrp_order_code_
    AND planner_buyer LIKE :planner_buyer_
    AND part_status LIKE :part_status_
    AND (NVL(second_commodity ,''%'') LIKE :second_commodity_)
    AND (NVL(part_product_code ,''%'') LIKE :part_product_code_)
    AND (NVL(part_product_family ,''%'') LIKE :part_product_family_)
    AND project_id = :project_id_
    AND ipcp.contract = :contract_
    AND ms.contract = ipcp.contract
    AND ms.component_part = ipcp.part_no
    AND ms.eff_phase_in_date <= :BOM_date_
    AND NVL(ms.eff_phase_out_date, :BOM_date_) >= :BOM_date_
    START WITH ' || where_cond1_ || '
    CONNECT BY
    PRIOR ' || where_cond2_ || '
    AND PRIOR ms.contract = ms.contract
    ORDER SIBLINGS BY ms.component_part';
    OPEN PartCur
    FOR sql_stmt_
    USING top_parent_value_,
    mrp_order_code_,
    planner_buyer_,
    part_status_,
    second_commodity_,
    part_product_code_,
    part_product_family_,
    project_id_,
    contract_,
    BOM_date_,
    BOM_date_, BOM_date_,
    where_value1_;
    FETCH PartCur BULK COLLECT INTO part_tab_;
    CLOSE PartCur;

    =======================================
    Sample SQL i used was:
    SELECT ms.component_part Part_No,
    'A005' Top_Parent,
    ms.part_no Parent_Part,
    ipc.configuration_id,
    LEVEL level_no,
    LPAD(' ',LEVEL+1, 'X') Level_Graph
    FROM tab1 ip,
    tab2 ipp,
    tab3 ipc,
    tab4 ms
    WHERE ipp.contract = ip.contract
    AND ipp.part_no = ip.part_no
    AND ipc.contract = ip.contract
    AND ipc.part_no = ip.part_no
    AND ipp.mrp_order_code LIKE '%'
    AND ip.planner_buyer LIKE '%'
    AND ip.part_status LIKE '%'
    AND (NVL(ip.second_commodity ,'%') LIKE '%')
    AND (NVL(ip.part_product_code ,'%') LIKE '%')
    AND (NVL(ip.part_product_family ,'%') LIKE '%')
    AND ip.contract = '02'
    AND ms.contract = ip.contract
    AND ms.component_part = ip.part_no
    -- AND ms.eff_phase_in_date <= :BOM_date_
    -- AND NVL(ms.eff_phase_out_date, :BOM_date_) >= :BOM_date_
    START WITH ms.part_no = NVL('A005' , ms.part_no )
    CONNECT BY
    PRIOR ms.component_part = ms.part_no
    AND PRIOR ms.contract = ms.contract
    ORDER SIBLINGS BY ms.component_part

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What's your database version ?

    I have successfully execute the following snippet of code on 9iR2, 10gR1 and 10gR2 with the same results.

    Code:
    SQL>
    SQL> select lpad( '*', level, '*' ) || ename name
      2    from emp
      3  start with mgr is null
      4  connect by prior empno = mgr
      5  order siblings by ename asc
      6  /
    
    NAME
    --------------------------------------------------------------------------------
    *KING
    **BLAKE
    ***ALLEN
    ***JAMES
    ***MARTIN
    ***TURNER
    ***WARD
    **CLARK
    ***MILLER
    **JONES
    ***FORD
    ****SMITH
    ***SCOTT
    ****ADAMS
    
    14 rows selected.
    
    SQL>
    SQL> set serveroutput on
    SQL>
    SQL> declare
      2     l_stmt          varchar2(500);
      3     l_table         dbms_sql.varchar2_table;
      4     rfcur           sys_refcursor;
      5  begin
      6     l_stmt := 'select lpad( ''*'', level, ''*'' ) || ename name
      7                       from emp
      8                     start with mgr is null
      9                     connect by prior empno = mgr
     10                     order siblings by ename asc';
     11     open rfcur for l_stmt;
     12     fetch rfcur bulk collect into l_table;
     13     close rfcur;
     14     for i in 1..l_table.count
     15     loop
     16             dbms_output.put_line( l_table(i) );
     17     end loop;
     18  end;
     19  /
    *KING
    **BLAKE
    ***ALLEN
    ***JAMES
    ***MARTIN
    ***TURNER
    ***WARD
    **CLARK
    ***MILLER
    **JONES
    ***FORD
    ****SMITH
    ***SCOTT
    ****ADAMS
    
    PL/SQL procedure successfully completed.

  5. #5
    Join Date
    Jan 2006
    Posts
    6
    DB version is 10g release 10.2.0.1.0

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Can you actually execute this from SQL*Plus and cut and paste the results here ? How much data are we talking about here ?

  7. #7
    Join Date
    Jan 2006
    Posts
    6
    From sample SQL statement :


    1 A006 A005 A005 * 1 X
    2 A008 A005 A006 * 2 XX
    3 A003 A005 A008 * 3 XXX
    4 A006 A005 A005 * 1 X
    5 A008 A005 A006 * 2 XX
    6 A003 A005 A008 * 3 XXX
    7 A006 A005 A005 * 1 X
    8 A008 A005 A006 * 2 XX
    9 A003 A005 A008 * 3 XXX
    10 A007 A005 A005 * 1 X
    11 A007 A005 A005 * 1 X

    From actual method:
    columns 2, 3, 4 and 7 are given below .... this has less no of rows due to an additional filter used which is commented in the sample SQL. This list is taken from this LOOP :

    FOR i IN 1.. part_tab_.count LOOP
    IF (part_tab_.exists(i)) THEN
    dbms_output.put_line(to_char(i) || ' ' || part_tab_(i).part_no || ' ' || part_tab_(i).Parent_Part || ' > ' || part_tab_(i).Top_Parent || ' ' || part_tab_(i).Level_Graph);
    END IF;
    END LOOP;

    1 A008 A006 > A005 XX
    2 A003 A008 > A005 XXX
    3 A008 A006 > A005 XX
    4 A003 A008 > A005 XXX
    5 A006 A005 > A005 X
    6 A008 A006 > A005 XX
    7 A003 A008 > A005 XXX
    8 A007 A005 > A005 X

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It looks *right* (other than you probably don't need the exists there).

    One would need more info to get to this, I would file a tar if I were you though.

  9. #9
    Join Date
    Jan 2006
    Posts
    6
    U got any hint on where should i try to change and overcome this ???

  10. #10
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I would start by filling a tar with metalink.

    But.. why is that you're using bulk collect.. have you thought of any other alternatives ? perhaps you could do it in another way without using bulk collect at all..

Posting Permissions

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