Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: Urgent query - How to pass an array of strings to, and return a table set from a proc

    Hi,

    I have an urgent requirement and I am stuck at a place. Any advice will be helpful.

    I have a procedure, which is supposed to take an array of strings as input.
    Now I have a query in the procedure, which will return a row, for each of the array elements.
    For example:
    SELECT
    su.EMPLOYEE_ID,su.FIRST_NAME,
    su.LAST_NAME
    FROM
    USERS su,
    USER_E Sue,
    ROLE r,
    DIM_STAFF dos
    WHERE
    su.EMPLOYEE_ID=dos.LINE_EMPLOYEE_ID
    and su.EMPLOYEE_ID=ue.EMPLOYEE_ID
    and ue.ROLE_ID=r.ROLE_ID
    and dos.EMPLOYEE_ID=? ;

    In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.

    Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method(as you suggest) too.

    Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.

    Can any one tell me how to do all this together? This is the main question!

    Any other easier methods you know, can also be helpful.

    Suggestions are also requested about what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set.
    Like we can use arrays, table type data,ref cursors, etc.


    Thanks in advance
    Neel

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    I am not sure about which "procedure" are you talking about, as in PL/SQL, INTO clause is required. It is also not clear, which is the type of these "array elements". Anyway, if they have SQL collection object type (defined with CREATE TYPE statement), they may be treated as table inside the IN subquery, as shown in this code:
    Code:
    create type t_array as table of varchar2(30);
    /
    
    declare
      l_array t_array := t_array( 'SSS', 'X' );
      l_cnt integer;
    begin
      select count(*) into l_cnt
      from dual
      where dummy in ( select column_value from table( l_array ) );
      dbms_output.put_line( l_cnt );
    end;
    /

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    Let me just clarify the doubts:

    I have to write a PL/SQL procedure which needs to implement this requirement.

    The array which will be passed to the proc is an array of simple strings like '000877S', '0000897S'.
    My purpose is to select required fields from the above query where EMPLOYEE_ID='0008976S', and so on. So it is similar to writing the query as below:

    SELECT
    su.EMPLOYEE_ID,su.FIRST_NAME,
    su.LAST_NAME
    FROM
    USERS su,
    USER_E Sue,
    ROLE r,
    DIM_STAFF dos
    WHERE
    su.EMPLOYEE_ID=dos.LINE_EMPLOYEE_ID
    and su.EMPLOYEE_ID=ue.EMPLOYEE_ID
    and ue.ROLE_ID=r.ROLE_ID
    and dos.EMPLOYEE_ID IN ('0008976S', '00067554S','0006754S');

    But obviously these strings will be passed to the proc with the array of strings. So I have to return the result set of the query for each element of the array and collect it in a table type data and make the procedure return it to the calling environment.

    I hope this is clearer.

    Thanks
    Neel

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The array which will be passed to the proc is an array of simple strings like '000877S', '0000897S'.
    If those values were records in a table, the solution would be trivial.
    You now pay the price for not Normalizing data
    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.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2010
    Posts
    6
    @anacedent: ok supposing those strings are records in a table. can you give the code solution for that? that may also help me n I may change the design.

    btw, this array will be sent to the proc from a JAva program n the proc has to return the table to this java program.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >and dos.EMPLOYEE_ID IN ('0008976S', '00067554S','0006754S');

    and dos.EMPLOYEE_ID IN (select item_id from item_table)
    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.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1

Posting Permissions

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