1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by neel134, Jan 24, 2011.

  1. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    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. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Urgent query - How to pass an array of strings to, and return a table set from a

    You need to pass "an array of strings" but you don't say what these strings are; are they database rows, are they simple text strings? Such information is crucial to making any sort of suggestions so please provide a better description of the problem.
     
  3. neel134

    neel134 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Re: Urgent query - How to pass an array of strings to, and return a table set from a

    The strings are simple strings like '0008976S', '00067554S'. Texts only. Because my purpose is to select required fields from the above query where dos.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 info would be helpful.

    Thanks
    Neel
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India