9 Replies Latest reply: Mar 1, 2012 2:24 PM by 912146 RSS

    Query Loop or more efficient way?

    912146
      Hi Oracle Experts! I have a question that has stumped me for a few hours. Figured I'd toss this one out to see if anyone has a suggestion.


      Say I have the following two VARCHAR2 array's as input:

      keys: ['author', 'title', 'author', 'title', 'pub_year']
      values: ['Dr. Seuss', 'The Cat In the Hat', 'Shel Silverstein', 'Where the Sidewalk Ends', '1988']

      You can visually think of these as entries in a largely populated table (1M rows) as:

      id | key | value
      -------|------------|-------------------------
      1024 | author | Dr. Seuss
      1025 | title | The Cat in the Hat
      9933 | author | Shel Silverstein
      9934 | title | Where the Sidewalk Ends
      9935 | pub_year | 1988


      Now, lets say I want to retrieve the id's for these entries out of that table - call it Book_Props.

      I was trying to construct a reusable PL/SQL procedure such as the following:

      -- key array
      CREATE OR REPLACE TYPE K_ARR AS VARRAY(100) OF VARCHAR2(255);
      /
      -- value array
      CREATE OR REPLACE TYPE V_ARR AS VARRAY(100) OF VARCHAR2(3000);
      /

      CREATE OR REPLACE PROCEDURE propsearch(key_arr IN K_ARR, val_arr IN V_ARR, table_name IN VARCHAR2) IS
      BEGIN
           FOR i IN 1 .. key_arr.count LOOP
      SELECT table."id" FROM table WHERE (table."key"='key_arr(i)' AND table."value" ILIKE '%val_arr(i)%') as id;
                DBMS_OUTPUT.PUT_LINE( id );
           END LOOP;
      END;
      /

      Questions:
      1. Is this a sound approach or is there something more efficient?

      2. I am by no means a PL/SQL expert and know that the above syntax is wrong. I am getting the following errors:

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      4/9 PL/SQL: SQL Statement ignored
      4/71 PL/SQL: ORA-00920: invalid relational operator

      How would I go about an advanced query like this?

      Thanks for all of your help!

      Nick
        • 1. Re: Query Loop or more efficient way?
          JustinCave
          1) This is generally a very poor way to model data in a relational database. You would really want to have a BOOK table with separate columns for AUTHOR, TITLE, PUBLICATION_DATE, etc.

          2) Creating tables with case-sensitive column names like "id" and "key" is also going to create headaches down the line. Future developers will be much happier with you if you use case-insensitive identifiers.

          3) Something like
          CREATE OR REPLACE PROCEDURE propsearch(key_arr IN K_ARR, val_arr IN V_ARR, table_name IN VARCHAR2) 
          IS
            l_id INTEGER;
          BEGIN
            FOR i IN 1 .. key_arr.count LOOP
              SELECT table."id" 
                INTO l_id
                FROM table t 
               WHERE t."key"= key_arr(i) 
                 AND t."value" LIKE '%' || val_arr(i) || '%';
              DBMS_OUTPUT.PUT_LINE( l_id );
            END LOOP;
          END;
          should by syntactically valid (though it will throw a NO_DATA_FOUND exception if any key and value pair fails to return exactly 1 row from the table). Since, in reality, you'd never write a procedure that simply writes data to DBMS_OUTPUT, however, it's hard to say how reasonable this approach would be (assuming that the broken data model and the problematic column names cannot be changed).

          Justin
          • 2. Re: Query Loop or more efficient way?
            SamFisher
            Declare a variable of id datatype.
             
            var table.id%type;     -- Variable declaration. 
            SELECT table.id INTO var FROM table WHERE key = key_arr(i).key AND value = key_arr(i).value; 
             
            • 3. Re: Query Loop or more efficient way?
              rp0428
              Here is an AskTom reply that shows a simple function for turning a CSV list into a pl/SQl table. You could just call it twice, once for each list and then use the result pl/sql tables to query your main table

              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:139812348065
              ops$tkyte@8i> create or replace type myTableType 
                            as table of number;
                2  /
              Type created.
              
              ops$tkyte@8i> create or replace function in_list
                                   ( p_string in varchar2 ) return myTableType
                2  as
                3      l_data             myTableType := myTableType();
                4      l_string           long default p_string || ',';
                5      l_n                number;
                6  begin
                7  
                8    loop
                9      exit when l_string is null;
               10      l_data.extend;
               11      l_n := instr( l_string, ',' );
               12      l_data( l_data.count ) := substr( l_string, 1, l_n-1 );
               13      l_string := substr( l_string, l_n+1 );
               14    end loop;
               15    return l_data;
               16  end;
               17  /
              
              Function created.
              
              
              So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function returns a 
              SQL Table type as follows:
              
              ops$tkyte@8i> 
              ops$tkyte@8i> select *
                2    from THE ( select cast( in_list('1,2,3,5,12') 
                                      as mytableType ) from dual ) a
                3  /
              
              COLUMN_VALUE
              ------------
                         1
                         2
                         3
                         5
                        12
              • 4. Re: Query Loop or more efficient way?
                912146
                Thanks for all of the help guys! I agree that the data model is FUBAR, but changing it is a daunting task that is left for future work.

                I really appreciate all of the feedback but still seem to be having difficulties. Apparently passing table names dynamically does not work as I had expected. I was getting the following PL/SQL error:


                LINE/COL ERROR
                -------- -----------------------------------------------------------------
                5/5 PL/SQL: SQL Statement ignored
                7/10 PL/SQL: ORA-00942: table or view does not exist


                After further research it looks like Dynamic SQL is what is needed? However, I must not be understanding Dynamic SQL well enough to craft a correct statement.

                The following procedure created without errors:
                CREATE OR REPLACE PROCEDURE prop_search(key_arr IN K_ARR, val_arr IN V_ARR, tbl_name IN USER_TABLES.table_name%type)
                IS
                  l_id NUMBER;
                BEGIN
                     FOR i in 1 .. key_arr.count LOOP
                          EXECUTE IMMEDIATE 'SELECT LOWER(' || tbl_name || ')_id '
                          || 'FROM ' || tbl_name || '_PROPERTY '
                          || 'WHERE( "key" = :keyarr AND "value" LIKE %:valarr%)' INTO l_id using key_arr(i), val_arr(i);
                          DBMS_OUTPUT.PUT_LINE(l_id);
                     END LOOP;
                END;
                /
                The idea here is that for the table named BOOK there is a corresponding BOOK_PROPERTY table that looks like my example above - it stores key value pairs describing a book. The column book_id is a foreign key to the "id" column in the table BOOK. I want a procedure that finds all of the book_id values in the BOOK_PROPERTY table that matches the provided key/value arrays. The key/value variables are arrays that will be passed in from OJDBC and php OCI8 clients.

                The data model is horrendous because it was designed around a Hibernate concept. Every object has a table, and properties are stored in separate tables. This is why I need to pass the table name in as a variable. And I can't go changing the model right now.. that will have to be done later.

                Here is the PHP code I am executing: again it needs some work.
                $db = $this->getInstance();
                
                // create and populate the key and value collection
                $key_collection = oci_new_collection($db->getConnection(),"K_ARR");
                $val_collection = oci_new_collection($db->getConnection(),"V_ARR");
                             
                for ($i=0;$i<count($keys);$i++) {
                        $key_collection->append($keys[$i]);
                        $val_collection->append($values[$i]);
                }
                             
                // create the statement and bind the variables to PHP
                $stmt = oci_parse($db->getConnection(),"BEGIN prop_search(:key_arr,:val_arr,:tbl_name); END;");
                oci_bind_by_name($stmt,':tbl_name',$tableName);
                oci_bind_by_name($stmt,':key_arr',$key_collection,-1, OCI_B_NTY);
                oci_bind_by_name($stmt,':val_arr',$val_collection,-1, OCI_B_NTY);
                
                // execute the statement
                oci_execute($stmt, OCI_DEFAULT);
                And here is the error I am getting from OCI8
                PHP Warning:  oci_execute() [<a href='function.oci-execute'>function.oci-execute</a>]: ORA-00911: invalid character\nORA-06512: at &quot;ISPATIAL.PROP_SEARCH&quot;, line 6 
                Oh and it might help to know that I am using Oracle 10g R2.

                Been working this now for almost 2 days, and I'm starting to go cross eyed. Looking for a little bump from the Oracle experts! You guys have been fantastic.

                Thanks a lot!
                • 5. Re: Query Loop or more efficient way?
                  JustinCave
                  1) Based on the error, are you sure the table name being passed in is valid?
                  2) When you're building dynamic SQL, it's always a good idea to build it in a local variable and log it before trying to execute it. That makes it immensely easier to see what went wrong
                  CREATE OR REPLACE PROCEDURE prop_search(key_arr IN K_ARR, val_arr IN V_ARR, tbl_name IN USER_TABLES.table_name%type)
                  IS
                    l_id NUMBER;
                    l_sql_stmt VARCHAR2(4000);
                  BEGIN
                       FOR i in 1 .. key_arr.count LOOP
                            l_sql_stmt := 'SELECT LOWER(' || tbl_name || ')_id '
                            || 'FROM ' || tbl_name || '_PROPERTY '
                            || 'WHERE( "key" = :keyarr AND "value" LIKE %:valarr%)' ;
                                  <<write l_sql_statement to a log table, print it out using dbms_output, etc.>>
                                  EXECUTE IMMEDIATE l_sql_stmt INTO l_id USING key_arr(i), val_arr(i);
                            DBMS_OUTPUT.PUT_LINE(l_id);
                       END LOOP;
                  END;
                  3) If you do that, one thing that jumps out at me is that your LIKE clause needs to be restructured. You probably want something like this where you're concatenating the %'s to the bind variable value.
                  CREATE OR REPLACE PROCEDURE prop_search(key_arr IN K_ARR, val_arr IN V_ARR, tbl_name IN USER_TABLES.table_name%type)
                  IS
                    l_id NUMBER;
                    l_sql_stmt VARCHAR2(4000);
                  BEGIN
                       FOR i in 1 .. key_arr.count LOOP
                            l_sql_stmt := 'SELECT LOWER(' || tbl_name || ')_id '
                            || 'FROM ' || tbl_name || '_PROPERTY '
                            || 'WHERE( "key" = :keyarr AND "value" LIKE ''%'' || :valarr || ''%'' )' ;
                                  <<write l_sql_statement to a log table, print it out using dbms_output, etc.>>
                                  EXECUTE IMMEDIATE l_sql_stmt INTO l_id USING key_arr(i), val_arr(i);
                            DBMS_OUTPUT.PUT_LINE(l_id);
                       END LOOP;
                  END;
                  Justin
                  • 6. Re: Query Loop or more efficient way?
                    912146
                    Thanks Justin! I tried your suggestion but have the same error, this time its not on the SQL its on the EXECUTE IMMEDIATE LINE.
                    ORA-00911: invalid character
                    ORA-06512: at &quot;ISPATIAL.PROP_SEARCH&quot;, line 11
                    Here is the EXECUTE IMMEDIATE STATEMENT:
                    EXECUTE IMMEDIATE l_sql_stmt INTO l_id USING key_arr(i), val_arr(i);
                    And here is the debug output of the l_sql_stmt variable:
                    SELECT LOWER(OBJECT)_id FROM OBJECT_PROPERTY WHERE( "key" = :keyarr AND "value" LIKE '%' || :valarr || '%' )
                    Any thoughts?
                    • 7. Re: Query Loop or more efficient way?
                      JustinCave
                      user10248405 wrote:
                      SELECT LOWER(OBJECT)_id FROM OBJECT_PROPERTY WHERE( "key" = :keyarr AND "value" LIKE '%' || :valarr || '%' )
                      I assume that you wanted the statement to be
                      SELECT LOWER(OBJECT_ID) 
                      not
                      SELECT LOWER(OBJECT)_ID
                      So, presumably, you want to concatenate the '_ID' before the ')'

                      Justin
                      • 8. Re: Query Loop or more efficient way?
                        912146
                        Yeah, I saw that shortly after I posted... durrr.. :) Sorry about that.

                        I corrected it and get the following error...
                        ORA-01403: no data found\nORA-06512: at &quot;ISPATIAL.PROP_SEARCH&quot;, line 11
                        I assume that's not actually an error rather the SQL returned no rows?

                        If my assumption is correct then I can consider this matter closed/answered and am EXTREMELY grateful for your assistance!!!!!
                        • 9. Re: Query Loop or more efficient way?
                          JustinCave
                          user10248405 wrote:
                          ORA-01403: no data found\nORA-06512: at "ISPATIAL.PROP_SEARCH", line 11
                          I assume that's not actually an error rather the SQL returned no rows?
                          It indicates that the SQL statement returned no rows. It is also an error.

                          A SELECT ... INTO statement must return exactly one row or it is an error. You'll get either a NO_DATA_FOUND or a TOO_MANY_ROWS exception if 0 or multiple rows are returned. If you don't want to consider those errors, you can catch and handle them in an exception handler, i.e.
                          <<other code>>
                          LOOP
                            l_sql_stmt := ...
                            <<other code>>
                            BEGIN
                              EXECUTE IMMEDIATE l_sql_stmt ...
                            EXCEPTION
                              WHEN no_data_found 
                              THEN
                                <<log something>>
                              WHEN too_many_rows
                              THEN
                                <<log something else>>
                            END;
                            <<more code>>
                          END LOOP;
                          Justin