SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot Christiano's Avatar
    Join Date
    Apr 2005
    Location
    Belfast, North of Ireland
    Posts
    153
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Building dynamic queries in PL / SQL against array values?

    Hi All,

    I'm doing some testing in PL / SQL and was wondering if any of you had experience building dynamic queries in PL/SQL?

    The query I am trying to achieve is this:
    Code:
          CURSOR the_cursor IS 
          SELECT column1_value FROM table1_tab t1 CROSS JOIN table2_tab t2 
          WHERE  t1.value_id = t2.value_id
          AND t2.status = 'Cancelled' OR t2.status = 'Sold'
          AND t1.column1_value LIKE '1%' OR t1.column1_value LIKE '2%'
          OR t1.column1_value LIKE '3%' OR t1.column1_value LIKE '4%'
          OR t1.column1_value LIKE '5%';
    The values ('1%', '2%', '3%', '4%' etc) will be held within an array.

    The problem I am having is trying to build a string that can be used in the

    EXECUTE IMMEDIATELY function but I am unsure as to how to approach such an implementation. Bare in mind the array containing '1%' like values can be dynamic (hence the need for a changing sql statement. I'n Java this can be done (by building the string) but I keep running into errors with the following code:

    Code:
    dynamic_query := 'CURSOR the_cursor IS 
                             SELECT column1_value FROM table1_tab t1 CROSS JOIN table2_tab t2 
                             WHERE  t1.value_id = t2.value_id
          AND t2.status = 'Cancelled' OR t2.status = 'Sold'
          AND t1.column1_value LIKE '1%';'
    With throws the following error:
    Error(25,22): PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "=" to continue.

    Any guidance is much appreciated.

    Regards,
    MC
    Innovative Design and Development Services
    Design: XHTML - CSS - Photoshop CS - Flash 7 - Illustrator
    Development: Java - JavaScript - PHP - MySQL - .NET - Struts 2

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there are two problems

    the first has to do with the way you are quoting the entire string...
    Code:
    dynamic_query := 'CURSOR the_cursor IS 
    SELECT column1_value FROM table1_tab t1 CROSS JOIN table2_tab t2 
    WHERE  t1.value_id = t2.value_id
    AND t2.status = '
    do you see where it's cut off there? the first single quote which you want to use for the string 'Cancelled' is actually terminating the string you're defining for dynamic_query

    the second problem is that you are mixing your ANDs and ORs without parentheses, and once you get the query to run, you will get some very incorrect results

    what you want is this --
    Code:
    SELECT column1_value 
      FROM table1_tab t1 
    CROSS 
      JOIN table2_tab t2 
     WHERE t1.value_id = t2.value_id
       AND (
           t2.status = 'Cancelled' 
        OR t2.status = 'Sold'
           )
       AND (
           t1.column1_value LIKE '1&#37;'
        OR t1.column1_value LIKE '2%'
        OR t1.column1_value LIKE '3%'
        OR t1.column1_value LIKE '4%'
        OR t1.column1_value LIKE '5%'
           )
    finally, the expression is "bear in mind" (i.e. remember), not "bare in mind" (mentally take off your clothes)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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