Dynamic Query String

[gandolf989]

Try this.

Originally posted by JayM

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 LENGTH(IN_PROGRAM) > 0 THEN
        qry_str := qry_str || 
                   ' AND OT.ADMINISTRATION = ' || IN_PROGRAM;
    END IF;
    OPEN PR_CURSOR FOR qry_str;
END TEST;