You ought to be trying to use bind variables unless you have a good reason why not. A simple approach here might be...
Code:PROCEDURE test ( in_program IN VARCHAR2, pr_cursor OUT package_reports_cursor) AS qry_str VARCHAR2 (2000); BEGIN qry_str := 'SELECT ot.sales_order_num, st.tracking_num, ss.shipment_status_detail, ' || ' st.exception_code, st.reference_num, st.expected_ship_date, ' || ' st.actual_ship_date, st.expected_deliv_date, st.req_deliv_date, ' || ' st.actual_deliv_date ' || 'FROM shipment_tracking st, order_table ot, shipment_status_lookup ss ' || 'WHERE ot.sales_order_num = st.sales_order_num ' || 'AND ss.shipment_status_code = st.ship_type '; IF in_program IS NOT NULL THEN qry_str := qry_str || 'AND ot.administration = :1'; OPEN pr_cursor FOR qry_str USING in_program; ELSE OPEN pr_cursor FOR qry_str; END IF; END test;




Reply With Quote