If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Dynamic Query String

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2005
Posts: 11
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Posts: 2,296
you need to post the cursor and package you are calling that gets the error.

line #19: "SENTPROTO.PACKAGE_REPORTS_PKG", line 19

and also that cursor you pass the string to
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,571
Here is a free clue when dealing with dynamic SQL.
While debugging always print out the whole statement before executing.
If/when it generates an error, cut/paste into SQL*Plus & see what it says.
Is "IN_PROGRAM" a string literal or what?
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2005
Posts: 11
duck.. the sql works when i comment out the entire check/appending for IN_PROGRAM, so i know the cursor is ok.


anacedent.. where will the print out be shown if i did
Code:
dbms_output.PUT_LINE(qry_str);
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2003
Location: Greenville, SC (USA)
Posts: 1,155
If IN_PROGRAM is a variable being passed in then:

qry_str := qry_str || ' AND OT.ADMINISTRATION = IN_PROGRAM';
change to

qry_str := qry_str || ' AND OT.ADMINISTRATION = '||''''||IN_PROGRAM||'''';

HTH
Gregg
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2005
Posts: 11
Quote:
Originally Posted by gbrabham
If IN_PROGRAM is a variable being passed in then:

qry_str := qry_str || ' AND OT.ADMINISTRATION = IN_PROGRAM';
change to

qry_str := qry_str || ' AND OT.ADMINISTRATION = '||''''||IN_PROGRAM||'''';

HTH
Gregg
Thanks for the help!!!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2005
Posts: 11
Quote:
Originally Posted by gbrabham
If IN_PROGRAM is a variable being passed in then:

qry_str := qry_str || ' AND OT.ADMINISTRATION = IN_PROGRAM';
change to

qry_str := qry_str || ' AND OT.ADMINISTRATION = '||''''||IN_PROGRAM||'''';

HTH
Gregg
1 more..

Code:
 ELSIF LENGTH(IN_ORD_PREFIX) > 0 THEN
	 	qry_str := qry_str || ' AND OT.SALES_ORDER_NUM LIKE ' || ''''||UPPER(IN_ORD_PREFIX)%||'''';
when compiling i get the error: PLS-00103: Encountered the symbol "|" when expecting one of the following:

( type <an identifier> <a double-quoted delimited-identifier>
The symbol "type" was substituted for "|" to continue.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Posts: 2,296
i am confused, but that is nothing unusual.
i think you want something more like this:
PHP Code:
qry_str := qry_str || ' AND OT.SALES_ORDER_NUM LIKE ''UPPER(IN_ORD_PREFIX)%'''
example when selecting from dual:
PHP Code:
SQL select ' AND OT.SALES_ORDER_NUM LIKE ''UPPER(IN_ORD_PREFIX)%'';'  text from dual
  2  
/

TEXT
-----------------------------------------------------
 AND 
OT.SALES_ORDER_NUM LIKE 'UPPER(IN_ORD_PREFIX)%'
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...

Last edited by The_Duck; 03-01-05 at 14:24.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Feb 2005
Posts: 11
one last question for the day..

Code:
PROCEDURE PR_PKG_SUMMARY (IN_PROGRAM IN VARCHAR2, IN_ADMIN_MONTH IN VARCHAR2,
  							IN_ASD_START_DATE IN DATE, IN_ASD_END_DATE IN DATE,
							IN_EDD_START_DATE IN DATE, IN_EDD_END_DATE IN DATE,
							IN_ORD_PREFIX IN VARCHAR2, IN_REFERENCE IN VARCHAR2,
							IN_ROUTE IN VARCHAR2, IN_RESOLVED 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 = ' || ''''||UPPER(IN_PROGRAM)||'''';

	 ELSIF LENGTH(IN_ADMIN_MONTH) > 0 THEN
		qry_str := qry_str || ' AND TO_CHAR(OT.ADMINISTRATION_DATE,''MM'') = ' || ''''||UPPER(IN_ADMIN_MONTH)||'''';

	 ELSIF LENGTH(IN_ASD_START_DATE) > 0 THEN
	    qry_str := qry_str || ' AND ST.ACTUAL_SHIP_DATE >= ' || ''''||IN_ASD_START_DATE||'''';

	 ELSIF LENGTH(IN_ASD_END_DATE) > 0 THEN
	 	qry_str := qry_str || ' AND ST.ACTUAL_SHIP_DATE <= ' || ''''||IN_ASD_END_DATE||'''';

	 ELSIF LENGTH(IN_EDD_START_DATE) > 0 THEN
		qry_str := qry_str || ' AND ST.EXPECTED_DELIV_DATE >= ' || ''''||IN_EDD_START_DATE||'''';

	 ELSIF LENGTH(IN_EDD_END_DATE) > 0 THEN
 	    qry_str := qry_str || ' AND ST.EXPECTED_DELIV_DATE <= ' || ''''||IN_EDD_START_DATE||'''';

	 ELSIF LENGTH(IN_ORD_PREFIX) > 0 THEN
	 	qry_str := qry_str || ' AND OT.SALES_ORDER_NUM LIKE ''UPPER(IN_ORD_PREFIX)%''';

	 ELSIF LENGTH(IN_ROUTE) > 0 THEN
	 	qry_str := qry_str || ' AND ST.ROUTE_TYPE = ' || ''''||UPPER(IN_ROUTE)||'''';
	 END IF;

     OPEN PR_CURSOR FOR qry_str;
  END PR_PKG_SUMMARY;
i'm getting this error:

fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleCommand.ExecuteRead er()
at SeNTReporter.SeNTReport.Rpt_Pkg_Summary(OracleConn ection dbConnection)
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Mar 2002
Location: Reading, UK
Posts: 1,133
If you cant get access to your dbms_output then write the query_str to a table. Do a commit after the insert and then you can actually try executing your query in sqlplus and see what the error is.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On