Mike Stevens

Mike Stevens

  • NA
  • 3
  • 9k

C# .net Oracle Regular Expressions

Oct 12 2010 11:51 AM
Hello, i am writing a program that is using the Oracle .net connector. I am passing a query through the OracleDataAdapter that contains an Oracle regular expression in querying the data dictionary to return rows. Below is the code.

oraadap = new OracleDataAdapter("select (Chr(39) || regexp_replace(file_name, '^.+\\', NULL) || Chr(39) as NAME, (bytes/1048576) as BYTES, 'M' as SizeUnit, (case when autoextensible = 'YES' then 'ON' else 'OFF' end) as AUTOEXTEND, (case when autoextensible = 'YES' then increment_by/128 else null end) as INCREMENT_BY, (case when autoextensible = 'YES' then 'M' else null end) as Next_Unit, (case when autoextensible = 'YES' then cast(maxbytes/1048576 as int) else null end) as MAXBYTES, (case when autoextensible = 'YES' then 'M' else null end) as MaxSize_Unit from sys.dba_data_files where tablespace_name = '" + seltabspace + "')", oraconn);

oraadap.Fill(dt);


The result i get in the NAME field is two single quotes. The file_name field is blank. If i take out the regular expression it will return the entire path which is not what i am wanting. There is something not quite right with library either not passing the regular expression OR not returning properly.

If i take out the single quotes Chr(39) and just leave in the regexp_replace then VS.net throws an exception "Operation did not succeed because the program cannot commit or quit a cell value change."  I am using this information to fill a datagridview and have mapped it as such.


dgvORATabSpace.Columns[0].DataPropertyName = dt.Columns[0].ToString();
dgvORATabSpace.Columns[1].DataPropertyName = dt.Columns[1].ToString();
dgvORATabSpace.Columns[2].DataPropertyName = dt.Columns[2].ToString();
dgvORATabSpace.Columns[3].DataPropertyName = dt.Columns[3].ToString();
dgvORATabSpace.Columns[4].DataPropertyName = dt.Columns[4].ToString();
dgvORATabSpace.Columns[5].DataPropertyName = dt.Columns[5].ToString();
dgvORATabSpace.Columns[6].DataPropertyName = dt.Columns[6].ToString();
dgvORATabSpace.Columns[7].DataPropertyName = dt.Columns[7].ToString();
dgvORATabSpace.DataSource = dt;


I am confused, any information on this error or if anyone has passed a regular expression to oracle in a query please let me know. It works properly if i do not use the regular expression in the query.
 
Thanks
Mike

Answers (2)