*** I have posted the same message in Oracle Development forum.
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.
whatever you use (nested tables or varchar22 or long), consider the following portion of relational theory:
Table column should have atomic values. Classic example of deciding on it is date datatype value: to store it as 3 columns (year, month, day) or as one value? In general, storing as 3 column value is more flexible for quering.
However in your case one may consider using column1..colunmN, but such approach leads to really ugly denormalization (1'st normal form not satisfied).
So it is strongly advised to have a separate table and to store every VALUEi into separate record.
suppose you have data to insert about one specific object, which includes a number to uniquely identify the object, and multiple string values as some data about the object.
1. read the object id and the entire concatenated string
2. parse string into individual variables
3. check parent_table to avoid attempting to insert duplicate row for this object
4. insert one parent_table record, if necessary
5. insert one row in child table for each of those parsed string values, using the ID from the parent table as the ID in the child table