have a cursor that looks like this



CURSOR resavations(i_sol_id IN NUMBER, i_warehouses_id IN NUMBER, i_dnl_ids IN NUMBER_ARRAY) IS

SELECT sold.SALES_ORDER_LINES_ID,

wat.warehouse_assortment_trans_id,

sold.style_details_id,

wwt.actual_quantity AS quantity,

dnl.dispatch_note_lines_id,

dnld.dispatch_note_line_details_id,

wwt.warehouses_id

FROM warehouse_assortment_trans wat,

warehouse_trans wt,

warehouse_trans_types wtt,

warehouse_warehouse_trans wwt,

sales_order_line_details sold,

logistics.dispatch_note_lines dnl,

logistics.dispatch_note_line_details dnld

WHERE wat.sales_order_line_details_id = sold.sales_order_line_details_id

AND wat.warehouse_trans_id = wt.warehouse_trans_id

AND wt.warehouse_trans_id = wwt.warehouse_trans_id

AND wt.warehouse_trans_types_id = wtt.warehouse_trans_types_id

AND wtt.transaction_type_name = 'Ready for Shipment'

AND sold.sales_order_line_details_id = dnld.sales_order_line_details_id

AND dnld.dispatch_note_lines_id = dnl.dispatch_note_lines_id

AND wwt.actual_quantity != 0

AND wwt.warehouses_id = i_warehouses_id

AND wat.sales_order_lines_id = i_sol_id

AND dnl.dispatch_note_lines_id IN (SELECT * FROM TABLE ( cast ( i_dnl_ids as NUMBER_ARRAY) ));



Where NUMBER_ARRAY is defined like the following

CREATE OR REPLACE

TYPE NUMBER_ARRAY AS TABLE OF NUMBER;



After I have looked at my pl/sql function I have found out that (SELECT * FROM TABLE ( cast ( i_dnl_ids as NUMBER_ARRAY) )) is what seems to be taking the time.

Because I if removed this and only put in one value like (i_dnl_ids IN NUMBER) instead of (i_dnl_ids IN NUMBER_ARRAY) it seems to be running better.

But this will of course not work cause of multiple values that I have to pass to the cursor.



Now my question is

Is there a better way to do this?

Or is this the only way?

Sonu