-
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
-
Re: Dynamic Query String
Try this.
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;
Last edited by gandolf989; 03-01-2005 at 12:26 PM.
-
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?
-
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.
-
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;
-
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.
-
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;
-
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;
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|