ADO.NET / Oracle problem

  • Thread starter Thread starter Bill Zack
  • Start date Start date
B

Bill Zack

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
 
Hi Bill,

I've seen similar weirdness with Oracle.
Which oracle client are you using?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Bill Zack said:
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
Click to expand...
 
We have tried it with 8.1.7 and 9.2 with the same results.
Bill Zack


Miha Markic said:
Hi Bill,

I've seen similar weirdness with Oracle.
Which oracle client are you using?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Bill Zack said:
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
Click to expand...
Click to expand...
 
We have tried it with 8.1.7 and 9.2 with the same results.

Thanks
Bill Zack


Miha Markic said:
Hi Bill,

I've seen similar weirdness with Oracle.
Which oracle client are you using?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Bill Zack said:
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
Click to expand...
Click to expand...
 
Hi Bill,

I have tried the following code you have provided on Oracle's sqlplus.

select sum(col1) "COL1", count(distinct Colb),'abc',colc "C" from
cubetest where col1 =1 group by cube(COL1,'abc',colc)

However, an error ORA-30480: DISTINCT option not allowed with GROUP BY CUBE
or ROLLUP was thrown. It seem that the Oracle server dosen't allow DISTINCT
and GROUP BY CUBE in the same statement.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for the response Kevin.

Which Oracle client did you use? We had the problem with 8.1.7 and 9.2.

We sent the sample program to Developer Support and they were able to
re-create the problem. They said that they are working with Oracle to
resolve it now.

Bill Zack
 
Hi Bill,

I have tried it on Oracle client 8.1.7. It gives the error which says
ORA-30480: DISTINCT option not allowed with GROUP BY CUBE or ROLLUP. Have
you created a case with Microsoft PSS? Please feel free to let me know, if
I can provide help.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Yes we opened a case. They were able to re-create it and they are
working with Oracle to resolve it. I will post the solution here when
they come up with one.

Thanks
Bill
 
Hi Bill,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top