Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    2

    Unanswered: PLSQL: Cursor and PLSQL tables

    Hi!

    I have the following situation:

    In a config table we have a string containing different codes, seperated by a '#': eg: '19#123#48'

    I have to get this string of codes, 'tokenizes' them and then I want to lookup the description for every single code in a different table:

    <abstract>
    SELECT codes FROM code_table INTO s_codes;

    -- tokenize the s_codes into an array or a plsql table v_codes

    SELECT description FROM code_table INTO s_des WHERE code IN v_codes
    </abstract>

    How can I do that? Obviously it is not possible to use the PLSQL table in the 'IN' clause of the second statement. Or is there even a more appropriate way, eg. to with two cursors (well, the second statement will anyway be a cursor)?

    Do you have any idea? (I hope you all understand what I want to do...)

    Thanks and
    Best regards

    Gino

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: PLSQL: Cursor and PLSQL tables

    Originally posted by gino
    Hi!

    I have the following situation:

    In a config table we have a string containing different codes, seperated by a '#': eg: '19#123#48'

    I have to get this string of codes, 'tokenizes' them and then I want to lookup the description for every single code in a different table:

    <abstract>
    SELECT codes FROM code_table INTO s_codes;

    -- tokenize the s_codes into an array or a plsql table v_codes

    SELECT description FROM code_table INTO s_des WHERE code IN v_codes
    </abstract>

    How can I do that? Obviously it is not possible to use the PLSQL table in the 'IN' clause of the second statement. Or is there even a more appropriate way, eg. to with two cursors (well, the second statement will anyway be a cursor)?

    Do you have any idea? (I hope you all understand what I want to do...)

    Thanks and
    Best regards

    Gino
    Before getting into the complexities (perfectly doable) of tokenising s_codes and joining to a PL/SQL table, you might want to consider doing just this:

    SELECT description FROM code_table INTO s_des WHERE INSTR( '#'||s_codes||'#', '#'||code||'#' ) > 0;

    The downside of this approach is that it will not use an index, so if the table is large this may not perform so well.

  3. #3
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    2
    Thanks a lot...I thought it must be easier...exactly what I wanted!

    Thanks
    Gino

Posting Permissions

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