Dynamic Query String

[JayM]

hello..

i've written the following test procedure:

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;

i'm getting the following error though when i run this:

2005-03-01 10:37:06,922 [2148] ERROR SeNTReporter.SeNTReport - Failed to run query: TEST: ORA-00904: "IN_PROGRAM": invalid identifier
ORA-06512: at "SENTPROTO.PACKAGE_REPORTS_PKG", line 19
ORA-06512: at line 1

[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;

[JayM]

gandolf..

i tried that and got:

2005-03-01 12:18:28,447 [2148] ERROR SeNTReporter.SeNTReport - Failed to run query: TEST: ORA-00904: "ASDF": invalid identifier
ORA-06512: at "SENTPROTO.PACKAGE_REPORTS_PKG", line 23
ORA-06512: at line 1

which means, now it's failing on the string i'm actually entering on the gui. gettin closer =D

anything else you suggest?

[gandolf989]

Originally posted by JayM
gandolf..

i tried that and got:

2005-03-01 12:18:28,447 [2148] ERROR SeNTReporter.SeNTReport - Failed to run query: TEST: ORA-00904: "ASDF": invalid identifier
ORA-06512: at "SENTPROTO.PACKAGE_REPORTS_PKG", line 23
ORA-06512: at line 1

which means, now it's failing on the string i'm actually entering on the gui. gettin closer =D

anything else you suggest?

Yes, somewhere there in an invalid reference to "ASDF" in your code.

[JayM]

Originally posted by gandolf989
Yes, somewhere there in an invalid reference to "ASDF" in your code.

"ASDF" is what i input on the gui. this column is defined as a varchar2, so i don't see why it's having problems doing the following comparison:

AND OT.ADMINISTRATION = ' || IN_PROGRAM;

[gandolf989]

Originally posted by JayM
"ASDF" is what i input on the gui. this column is defined as a varchar2, so i don't see why it's having problems doing the following comparison:

AND OT.ADMINISTRATION = ' || IN_PROGRAM;

IT seems like the problem is not in test but somewhere else. The problem could be in the gui. If this is a part of a package that has an initialization section that references ASDF, that could also be the problem.

[JayM]

i found it..

here's what i changed the code to..

IF LENGTH(IN_PROGRAM) > 0 THEN
	 	dbms_output.PUT_LINE(IN_PROGRAM);
 	    qry_str := qry_str || ' AND OT.ADMINISTRATION = ' || ''''||UPPER(IN_PROGRAM)||'''';
	 END IF;

[gandolf989]

So ASDF is a value not a column name.
You can also simplify it slightly.

IF LENGTH(IN_PROGRAM) > 0 THEN
   dbms_output.PUT_LINE(IN_PROGRAM);
   qry_str := qry_str || ' AND OT.ADMINISTRATION = ''' ||
   UPPER(IN_PROGRAM)||'''';
END IF;

[padders]

You ought to be trying to use bind variables unless you have a good reason why not. A simple approach here might be...

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;