Forum Stats

  • 3,781,362 Users
  • 2,254,505 Discussions
  • 7,879,657 Comments

Discussions

Using multiple values in a where clause, for values only known at runtime

470360
470360 Member Posts: 133
edited Jun 16, 2009 9:51AM in SQL & PL/SQL
Dear all

I am creating a PL/SQL program which returns multiple rows of data but only where it meets a set id values that a user has previously chosen. The id values are stored in an associative array and are chosen by a user in the preceding procedure at run time.

I know all the table and column names in advance. The only things I don't know are the exact number of ids selected from the id column and what their values will be. This will only be known at runtime. When the procedure is run by the user it prints multiple rows of data to a web browser.

I have been reading the following posting, which I understand to a large extent, 2452596 But I cannot seem to figure out how I would apply it to my work as I am dealing with multiple rows and a cursor.

The code as I have currently written it is wrong because I get an error not found message in my web browser. I think the var_user_chosen_map_list_ids in the for cursor loop could be the problem. I am using the variable_user_chosen_map_list_ids to store all the id values from my associatative array as a string. Which I modified from the code that vidyadhars posted in the other thread.

Should I be creating a OPEN FOR ref cursor and if so where would I put my associative array into it? At the moment I take the value, turning it into a string and IN part in the WHERE clause holds the string, allowing the WHERE clause to read all the values from it. I would expect the where clause to read everything in the string as 1 complete string of VARCHAR2 data but this would not be the case if this part of the code at least was correct. The code is as follows:
--Global variable section contains:
var_user_chosen_map_list_ids VARCHAR2(32767);
......
PROCEDURE PROCMAPSEARCH (par_user_chosen_map_list_ids PKG_ARR_MAPS.ARR_MAP_LIST)
IS
CURSOR cur_map_search (par_user_chosen_map_list_ids IN NUMBER)
IS
SELECT MI.map_date
       MT.map_title,
FROM map_info MI,
     map_title MT,
WHERE MI.map_title_id = MT.map_title_id
AND MI.map_publication_id IN 
 (var_user_chosen_map_list_ids);
var_map_list_to_compare VARCHAR2(32767) := '';
var_exe_imm_map VARCHAR2(32767);
BEGIN
FOR rec_user_chosen_map_list_ids IN 1 .. par_user_chosen_map_list_ids.count
LOOP
   var_user_chosen_map_list_ids := var_user_chosen_map_list_ids || 
   '''' || 
   par_user_chosen_map_list_ids(rec_user_chosen_map_list_ids) || 
   ''',' ;
END LOOP;
 var_user_chosen_map_list_ids := substr(var_user_chosen_map_list_ids,
                                        1, 
                                        length(var_user_chosen_map_list_ids)-1);
var_exe_imm_map := 'FOR rec_search_entered_details IN cur_map_search
LOOP
htp.print('Map date: ' || cur_map_search.map_date || ' Map title: ' || cur_map_search.map_title)
END LOOP;';
END PROCMAPSEARCH;
EXECUTE IMMEDIATE var_exe_imm_map;
I would be grateful of any comments or advice.

Kind regards

Tim
«1

Answers

  • SeánMacGC
    SeánMacGC Member Posts: 2,914 Gold Trophy
    Hello, I think a REF CURSOR would be the way to go here, have a look at this link for a comprehensive rundown of the possibilities:

    3405497
    SeánMacGC
  • SanjayRs
    SanjayRs Member Posts: 3,024
    This may be helpful

    http://db-oracl.blogspot.com/2009/01/dynamic-in-list-from-string.html

    SS
    SanjayRs
  • 470360
    470360 Member Posts: 133
    edited Jun 15, 2009 9:11AM
    Thank you so much SeánMacGC and Sänjay for your replies and the links. They were really helpful. I wasn't aware of sys.dbms_debug_vc2coll and although I've read up on ref cursors, I've never used them.

    I have a couple of related questions. In order to pass through a string, such as '330001','330005','330010', into sys.dbms_debug_vc2coll, do I need to use dynamic_sql and/or create my cursor as a ref cursor? Or can I just use a explicit cursor?

    I ask because since my last message I have now placed my explicit cursor in a separate function and got it all to work if I put actual numbers into sys.dbms_debug_vc2coll but not if I supply that with a string, as in sys.dbms_debug_vc2coll(var_user_chosen_map_list_ids). Of course the string is VARCHAR2. If I add TO_NUMBER as follows:
    WHERE MI.map_publication_id = TO_NUMBER (var_user_chosen_map_list_ids);
    It doesn't work. I think it is due to an invalid number error. However if I do nothing as in leave the string as '330001','330005',;330010', I also get an invalid number error message. If I remove ',' part from the variable I get one longer number which of course is not in the database so no match is found.

    Kind regards

    Tim

    Edited by: user467357 on Jun 15, 2009 2:05 PM
    Amended the message to include more detail on the error.
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    It'll be helpful if you post you db version: Other possibilities come up if you are e.g. already on 11g.
    MichaelS
  • 470360
    470360 Member Posts: 133
    Thank you for reminding me michaels. I forgot to do that. It is 10.2.0.2.

    Regards

    Tim
  • Christian Balz
    Christian Balz Member Posts: 462 Silver Badge
    edited Jun 15, 2009 9:32AM
    Hi Tim,

    maybe this helps,
    SQL> Declare
      2     v_String Varchar2(4000);
      3     v_Empno  dbms_utility.uncl_array;
      4     v_Nro    Number;
      5     v_cur    Sys_Refcursor;
      6     v_name   Varchar2(30);
      7  Begin
      8     SELECT Empno Bulk Collect
      9       INTO v_Empno
     10       FROM Emp
     11      WHERE deptno IN (20, 30);
     12  
     13     dbms_utility.table_to_comma( v_Empno
     14                                , v_Nro
     15                                , v_String );
     16  
     17     dbms_output.put_line(v_String);
     18  
     19     Open v_cur For
     20        SELECT ename
     21          FROM EMP
     22         WHERE instr(',' || v_String || ',' , ',' || empno || ',') > 0;
     23     Loop
     24        Fetch v_cur
     25           INTO v_name;
     26        Exit When v_cur%Notfound;
     27        dbms_output.put_line(v_name);
     28     End Loop;
     29  End;
     30  /
    
    7369,7499,7521,7566,7654,7698,7788,7844,7876,7900,7902
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    SCOTT
    TURNER
    ADAMS
    JAMES
    FORD
    
    PL/SQL procedure successfully completed
    Regards,
    Christian Balz

    Edited by: Christian Balz on 15/06/2009 06:31 - Add example
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Or
    SQL>  declare
      str varchar2(100) := '100,110,120,130';
      o  sys.odcinumberlist;
    begin
      execute immediate 'begin :o := sys.odcinumberlist(' || str || '); end;' using out o;
      
      for i in 1 .. o.count loop
        dbms_output.put_line(o(i));
      end loop;
    end;
    /
    100
    110
    120
    130
  • 470360
    470360 Member Posts: 133
    Thank you Christian and Michael for your replies.

    My procedure and function, which is part of a larger programme, is as below. With this values as VARCHAR2 strings, such as '330001','330005','330007', are being submitted. These of course change according to the maps selected by users. I read through your replies and wondered, are you saying I need to use a ref cursor and/or dynamic_sql. Is that where I am going wrong? I had been thinking that by using (sys.dbms_debug_vc2coll(par_user_chosen_map_list_ids)) I would not require dynamic_sql and/or a ref cursor. If that's not the case then I will try rework by code so that it uses a ref cursor and/or dynamic_sql.
    --Global variable section contains:
    var_user_chosen_map_list_ids VARCHAR2(32767);
    var_details VARCHAR(32767);
    ......
    PROCEDURE PROCMAPSEARCH (par_user_chosen_map_list_ids PKG_ARR_MAPS.ARR_MAP_LIST)
    IS
    BEGIN
    FOR rec_user_chosen_map_list_ids IN 1 .. par_user_chosen_map_list_ids.count
    LOOP
       var_user_chosen_map_list_ids := var_user_chosen_map_list_ids || 
       '''' || 
       par_user_chosen_map_list_ids(rec_user_chosen_map_list_ids) || 
       ''',' ;
    END LOOP;
     var_user_chosen_map_list_ids := substr(var_user_chosen_map_list_ids,
                                            1, 
                                            length(var_user_chosen_map_list_ids)-1);
    var_details := FUNCMAPDATAFIND (var_user_chosen_map_list_ids);
    htp.print(var_details);
    END PROCMAPSEARCH;
    FUNCTION FUNCMAPDETAILS (par_user_chosen_map_list_ids IN VARCHAR2(32767)
    RETURN VARCHAR2
    AS
    CURSOR cur_map_search (par_user_chosen_map_list_ids IN VARCHAR2(32767))
    IS
    SELECT MI.map_date,
           MT.map_title
    FROM map_info MI,
         map_title MT,
         TABLE (sys.dbms_debug_vc2coll(par_user_chosen_map_list_ids))
    WHERE MI.map_title_id = MT.map_title_id
    AND MI.map_publication_id = column_value 
    BEGIN
    FOR rec_map_search IN cur_map_search (par_user_chosen_map_list_ids)
    LOOP
    var_details := var_details || 'Feature name: '||
                        rec_map_search.compare_name_of_feature ||
                        'Map date: '||
                        rec_map_search.map_date ||
                        'Map title: ' ||
                        rec_map_search.map_title;
    END LOOP;
    RETURN var_details; 
    END FUNCMAPDETAILS;
    Kind regards

    Tim
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    I had been thinking that by using (sys.dbms_debug_vc2coll(par_user_chosen_map_list_ids)) I would not require dynamic_sql and/or a ref cursor
    unfortunately - as I tried to show you in my last post - you need some dynamic sql to fill the dbms_debug_vc2coll array properly. Not that of an obstacle I'd say though ;)
  • 470360
    470360 Member Posts: 133
    Thank you Michael, I will do just that.

    Am I right in thinking that dynamic SQL allows strings to be converted to number, where as static does not.

    What I partly got stuck on when I first looked into dynamic sql was that none of my tables or columns were unknown and I was coming across examples related to creating programmes where tables and columns were passed through at run time.

    I will read through the various books I have, as well as your examples and reformat my code accordingly.

    When I create something which works, I will post the code back here, so that others in my position have another example to look in the future.

    Kind regards

    Tim
This discussion has been closed.