Query data in INSTANCEPROMOTEDPROPERTIES

Query data in INSTANCEPROMOTEDPROPERTIES

Postby yuanming » Tue 01 Jul 2014 18:48

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!
yuanming
 
Posts: 1
Joined: Tue 01 Jul 2014 17:55

Re: Query data in INSTANCEPROMOTEDPROPERTIES

Postby Pinturiccio » Thu 03 Jul 2014 14:34

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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleArray.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();
}
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle