We have a table in Oracle described as the following:
SQL> desc cubetest
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COLB NUMBER
COLC VARCHAR2(3)
We populate it with some data and try to execute a select statement.
The following select statement executes fine under sqlplus, but when
we try to run it using the .NET OracleClient we get this:
Encountered an exception while preparing the SQL statement.
System.Data.OracleClient.OracleException: ORA-03001: unimplemented
feature
at System.Data.OracleClient.OracleConnection.CheckError(OciHandle
errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Prepare()
at Script.Main(String[] args)
SQL:
select sum(col1) "COL1", count(distinct Colb),'abc',colc "C" from
cubetest where col1 =1 group by cube(COL1,'abc',colc)
If we change count(distinct Colb) to count(Colb) or we change the
group by expression to group by COL1,'abc',colc then this query works.
Has anyone encountered a problem executing select statements with this
combination of expressions?
Below is the C# code used to execute test this:
using System;
using System.Data;
using System.Data.OracleClient;
using System.Windows.Forms;
using System.IO;
class Script
{
public static void Main(string[] args)
{
if(args.Length < 2){
Console.WriteLine("usage:
sqlrunner <connect string> <sql statement> [[<param name> <param
value>] ...]");
return;
}
string connectString = args[0];
string sql = args[1];
if(args[1][0] == '@'){
if(args[1].Length == 1){
Console.WriteLine("No
file specified.");
return;
}
string filename =
args[1].Substring(1);
StreamReader reader =
File.OpenText(filename);
try{
sql =
reader.ReadToEnd();
}finally{
reader.Close();
}
}
OracleConnection dbConn = new
OracleConnection(connectString);
OracleCommand cmd = new
OracleCommand(sql,dbConn);
try{
try{
dbConn.Open();
}catch{
Console.WriteLine("Failed to open
connection");
return;
}
bool parseOk = true;
bool executeOk = true;
try {
cmd.Prepare();
} catch (Exception ex){
Console.WriteLine("Encountered an
exception while preparing the SQL statement.\n"+ex+"\n"+"STACK
TRACE-----------------------\n"+ex.StackTrace);
parseOk = false;
}
OracleDataAdapter da = new
OracleDataAdapter(cmd);
DataSet ds = new DataSet("Results");
if(parseOk){
try{
da.Fill(ds);
}catch(Exception ex){
Console.WriteLine("Encountered an exception while executing the SQL
statement.\n"+ex.Message+"\n"+"STACK
TRACE-----------------------\n"+ex.StackTrace);
executeOk = false;
}finally{
dbConn.Close();
}
if(!executeOk) return;
try{
ds.WriteXml("results.xml",XmlWriteMode.IgnoreSchema);
Console.WriteLine(ds.GetXml());
Console.WriteLine("________________________________________________");
Console.WriteLine("Results have been written to results.xml");
}catch(Exception ex){
Console.WriteLine("Encountered an exception while retrieving
data.\n"+ex.Message+"\n"+"STACK
TRACE-----------------------\n"+ex.StackTrace);
}
}
}finally{
dbConn.Close();
}
}
}
The command line used would look something like this:
sqlrunner "User Id=test;Password=pss;Data Source=dev9" @test2.sql
where test2.sql contains the select statement you want to execute.
We are using the Microsoft oracle provider.
Thanks
Bill Zack
Similar Threads
