Anonymous Block in OleDbCommand - DataAdapter.Fill() hangs

  • Thread starter Thread starter Ross
  • Start date Start date
R

Ross

Hello,

I would like to execute an Oracle anonymous block using the following
code. The code works just fine with a regular Insert/Update/Select
commands, but with even a very simple anonymous block the
DataAdapter.Fill() method will hang. No error is returned.

StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("DECLARE myID NUMBER; BEGIN INSERT INTO
doc_pkg(doc_pkg_id) VALUES(doc_pkg_id_seq.nextval) RETURNING
doc_pkg_id INTO myID; END; ");
OleDbConnection dbcConn = new
OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=mydb;User
Id=user;Password=psw");
OleDbCommand myCommand = new OleDbCommand(sbQuery.ToString(),
dbcConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myCommand);
dbcConn.Open();
myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet,"Results");
dbcConn.Close();

I have also tried Provider=MSDAORA in teh connection string, but the
results were no different.

Any ideas would be appreciated.
 
Ross said:
Hello,

I would like to execute an Oracle anonymous block using the following
code. The code works just fine with a regular Insert/Update/Select
commands, but with even a very simple anonymous block the
DataAdapter.Fill() method will hang. No error is returned.
Click to expand...

For DataAdapter.Fill you must return records, or at least bind a ref cursor
parameter.

This program:

DECLARE
myID NUMBER;
BEGIN
INSERT INTO doc_pkg(doc_pkg_id)
VALUES(doc_pkg_id_seq.nextval)
RETURNING doc_pkg_id INTO myID;
END;

Doesn't output anyting. Try something like

DECLARE
myID NUMBER;
BEGIN
INSERT INTO doc_pkg(doc_pkg_id)
VALUES(doc_pkg_id_seq.nextval)
RETURNING doc_pkg_id INTO myID;

open :rc for
select * from doc_pkg
where doc_pkg_id = myId;
END;


Then bind an output ref-cursor parameter.
Oh, wait, you are using OleDb.NET. You don't have ref cursor parameters.

You see, the only way to fill a datatable is with a cursor.
The only 2 ways to get a cursor are:

1 - run an SQL query.
2 - bind a ref cursor parameter.


And OleDB.NET can only use 1. I would recommend thay you go download
ODP.NET from otn.oracle.com (or at least use the framework managed provider
for Oracle).

And get rid of OLEDB.NET.

David
 
David - Thanks! I changed from OleDb to the MS provider for Oracle
but will move to the Oracle ODP.NET as you suggest. Using the Ref
Cursor and setting up the output bind variable worked. Below is the
resulting sample code for future reference.

Thanks again for your help.

Ross

StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("DECLARE myID NUMBER; BEGIN INSERT INTO
doc_pkg(doc_pkg_id) VALUES(doc_pkg_id_seq.nextval) RETURNING
doc_pkg_id INTO myID; OPEN :rc FOR SELECT * FROM doc_pkg WHERE
doc_pkg_id = myID; END; ");
OracleConnection dbcConn = DADBUtils.GetDbConnection(dbName, userName,
password);
OracleCommand myCommand = new OracleCommand(sbQuery.ToString(),
dbcConn);
OracleDataAdapter myDataAdapter = new OracleDataAdapter(myCommand);
dbcConn.Open();
myDataSet = new DataSet();
OracleParameter myParameter = new
OracleParameter("rc",OracleType.Cursor);
myParameter.Direction = ParameterDirection.Output;
myDataAdapter.SelectCommand.Parameters.Add(myParameter);
myDataAdapter.Fill(myDataSet,"Results");
dbcConn.Close();
 
Back
Top