Dynamic Query String
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Dynamic Query String

  1. #1
    Join Date
    Jan 2005
    Posts
    8

    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
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,971

    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 11:26 AM.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2005
    Posts
    8
    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?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,971
    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.
    this space intentionally left blank

  5. #5
    Join Date
    Jan 2005
    Posts
    8
    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;

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,971
    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.
    this space intentionally left blank

  7. #7
    Join Date
    Jan 2005
    Posts
    8
    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;

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,971
    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;
    this space intentionally left blank

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    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
  •  



Click Here to Expand Forum to Full Width