Query data in INSTANCEPROMOTEDPROPERTIES

[yuanming]

When using dotConnect for Oracle, I noticed that promoted properties are stored as T_VARCHAR_ARRAY. For example, here is the SQL statement to insert a record into the InstancePromtedProperties table:
Insert into InstancePromtedProperties (SIMPLEVALUES) values (T_VARCHAR_ARRAY('Hello world!'));

When I try to query the value in T_VARCHAR_ARRAY, I used the following SQL statement:

SELECT SIMPLEVALUES IN InstancePromtedProperties

But the return is T_VARCHAR_ARRAY('Hello world!'). I am not familiar with T_VARCHAR_ARRAY. What should be the SQL query to return the text 'Hello world' instead? Thanks!

[Pinturiccio]

The T_VARCHAR_ARRAY type is declared as the VARRAY type:

Code: Select all

CREATE TYPE SCOTT.T_VARCHAR_ARRAY AS
VARYING ARRAY (32) OF VARCHAR2(4000);

The OracleArray class is used in dotConnect for Oracle to work with the VARRAY data type. For more information, please refer to http://www.devart.com/dotconnect/oracle ... Array.html

Here is an example that can be used for reading data from the SIMPLEVALUES column.

Code: Select all

OracleConnection conn = new OracleConnection("your conection string");
conn.Open();
OracleCommand comm = new OracleCommand("SELECT SIMPLEVALUES from INSTANCEPROMOTEDPROPERTIES", conn);
OracleDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
    OracleArray arr = reader.GetOracleArray(0);
    for (int i = 0; i < arr.Count; i++)
        Console.Write(arr[i] + "\t");
    Console.WriteLine();
}