*** 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.
What do i do ???
nested table or separate that data into a child table and create a primary key - foreign key relationship between the original table and the new child table.
I am getting a String (char*) object and inside the string are the multiple values concatenated with a backslash
I don't know which data i will receive and i check the data by going over the string and extracting the concatenated values.
How can i save them in the database table ???
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.
1. parse string, putting each string segment into a variable
2. insert/update child and parent rows with appropriate data
create table parent_table(id number(10) primary key);
create table child_table(id number(10) references parent_table(id), stringdata varchar2(50));
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