C# .net Oracle Regular Expressions

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mike Stevens16y ago
I am building an application where i will be creating and altering oracle tablespaces. When retreiving existing tablespaces, i query the oracle data dictionary views. The view with the tablespaces includes a column called FILE_NAME which includes the entire path. I just want to show the file name and extention and not the entire path.

I could probably do a Regex on the resultant set but i figure it would be easier for oracle to do this and would require no additional code.

Thanks
Mike
Bryian Tan16y ago
hello,

What the purpose of your regular expression? Do you want to replace the file extension with NULL or empty string?