Results 1 to 10 of 10
  1. #1
    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

  2. #2
    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 ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,649
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  4. #4
    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);

  5. #5
    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

  6. #6
    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!!!

  7. #7
    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.

  8. #8
    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)%'
    Last edited by The_Duck; 03-01-05 at 15:24.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    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)

  10. #10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •