DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Complex Problem

  1. #1
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    I am developing an application in pro*C and Oracle.
    I want to create a table that has a multi-valued column, meaning can hold more than one value.
    My problem is this:
    1) I thought about using a varchar2 type and contcatenating values through pro*C, but when i perform a select clause, i will have to use like %someting% and that will cause a full table scan.
    2) I thought about using object type, but i know oracle holds the object as lob type and i cant query the lob type for its contents.

    What do i do ???

    Thanx
    Rotem.

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    Is it not an option to use two columns, and concatenate the values where neccessary on output?

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    Angry No !

    It has to be in one column because the insert it dynamic, i dont know how many vlaues i will receive.
    Rotem.

  4. #4
    Join Date
    Feb 2001
    Posts
    123
    What about the option of a new table, with a reference to the original table. The new table could contain the ref. to the original table, an identifier for the field number within the input, and a single column for the value. You could then have one row in this new table for each input variable received by the application.

  5. #5
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    Help

    I am getting a String (char*) object and inside the string are the multiple values concatenated with a backslash
    i.e. VALUE1\VALUE2\VALUE3

    I don't know which data i will receive and i check the data by going over the string and extracting the concatenated values.

    I still need help with saving the data in database.

    Thanx for the effort,
    Rotem.

  6. #6
    Join Date
    Feb 2001
    Posts
    123
    Presumably, in your original table, there will be some sort of unique key to identify a row.

    Create a new table containing the following columns:
    keyval (points to the unique key in the original table.)
    variable_pos (gives the sequence number of the variable in the input string)
    value (the actual value of the input variable that you want to store).

    In the loop that you use to 'slice' the original input string into its individual values, you can have a counter that starts at 1, and increments for each value. This counter can then be used for the value that is inserted in the variable_pos field of the table. You now have all the values required for the new table, and can insert them using a regular insert statement.

    That way, when searching for specific keywords later, you are searching the entire value field, not using 'like'. You can also get the original variable list in order by linking the original table to the new table, and sorting by variable_pos.

    [Edited by Dave_A on 02-27-2001 at 10:52 AM]

Posting Permissions

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


Click Here to Expand Forum to Full Width