Dynamic Query String

Printable View

03-01-2005, 12:15 PM
JayM

Dynamic Query String

hello..

i've written the following test procedure:

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 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
03-01-2005, 12:22 PM
gandolf989

Re: Dynamic Query String

Try this.

Quote:

Originally posted by JayM

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 LENGTH(IN_PROGRAM) > 0 THEN qry_str := qry_str || ' AND OT.ADMINISTRATION = ' || IN_PROGRAM; END IF; OPEN PR_CURSOR FOR qry_str; END TEST;
03-01-2005, 01:24 PM
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?
03-01-2005, 01:51 PM
gandolf989

Quote:

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.
03-01-2005, 02:10 PM
JayM

Quote:

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:

Code:

AND OT.ADMINISTRATION = ' || IN_PROGRAM;
03-01-2005, 02:19 PM
gandolf989

Quote:

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:

Code:

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.
03-01-2005, 02:22 PM
JayM

i found it..

here's what i changed the code to..

Code:

IF LENGTH(IN_PROGRAM) > 0 THEN dbms_output.PUT_LINE(IN_PROGRAM); qry_str := qry_str || ' AND OT.ADMINISTRATION = ' || ''''||UPPER(IN_PROGRAM)||''''; END IF;
03-01-2005, 04:11 PM
gandolf989

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

Code:

IF LENGTH(IN_PROGRAM) > 0 THEN dbms_output.PUT_LINE(IN_PROGRAM); qry_str := qry_str || ' AND OT.ADMINISTRATION = ''' || UPPER(IN_PROGRAM)||''''; END IF;
03-02-2005, 06:22 AM
padders

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;