OracleCommand does not always respect SchemaOnly

Question

For SqlClient, I am able to specify CommandBehavior.SchemaOnly for a lot of queries to get the query result schema. However, when use the same command behavior for Oracle, OracleClient seems to execute the query instead of obtaining the schema only. In the following example, I can see the row was indeed inserted into my database while I was just expecting to run a schema only query:

OracleCommand command = connection.CreateCommand();
command.CommandText = "insert into Test (ID, Name) values (1, 'test')";
var reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
var dataTable = reader.GetSchemaTable();

I cannot find much info on OracleClient doc about CommandBehavior. Does OracleClient implement this option? Or should I use a different way to get the schema without running the query?

score 0 · Answer 1 · edited Feb 11 '21 at 21:10

The issue here is SchemaOnly calls SET FMTONLY ON which is an SQL Server TSQL command.

The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.

Using this CommandBehavior just won't work on Oracle, you will need to find another way.

Maybe this previously asked question can help:

SET FMTONLY ON in Oracle queries

OracleCommand does not always respect SchemaOnly

1 Answers1