Oracle Reference Cursors using Enterprise Library

[Guest]

I'm trying to use the Enterprise Library to return result sets from Oracle
using a package which returns a reference cursor. While this works outside
the Library using the OracleClient namespace objects directly, I can't get it
to work inside the library. Package access doesn't seem to work at all.
Direct SQL access works great. Has anyone tried to return result sets using a
reference cursor to with the Library? Code snippets are below...

The Package ...
----------------------
CREATE OR REPLACE PACKAGE employee_main
AS
TYPE ref_cursor_type IS REF CURSOR;
PROCEDURE get_employees (emp_cursor OUT ref_cursor_type);
END employee_main;
/

CREATE OR REPLACE PACKAGE BODY employee_main
AS
PROCEDURE get_employees (emp_cursor OUT ref_cursor_type)
IS
BEGIN
OPEN emp_cursor FOR
SELECT *
FROM employees;
END get_employees;
END employee_main;
/

The C# snippet which accesses the package...
---------------------------------
public DataSet GetEmployeeData() {

string pkgName = "employee_main.get_employees";

Database db=DatabaseFactory.CreateDatabase();
DBCommandWrapper cmdWrapper = db.GetStoredProcCommandWrapper(pkgName);
cmdWrapper.AddOutParameter("emp_cursor", DbType.Object, 2000);

return db.ExecuteDataSet(cmdWrapper);
}

and finally, the error I'm getting...
-------------------------------
{"ORA-06550: line 1, column 14:\nPLS-00103: Encountered the symbol
\"PACKAGE\" when expecting one of the following:\n\n := . ( @ % ;\nThe
symbol \":=\" was substituted for \"PACKAGE\" to continue.\n" }


Any help will be greatly appreciated. Thanks!

 

[Guest]

With OracleClient, you have to create an output parameter for every
REF_CURSOR you are outputting. WIth OleDb, you do not. Have to tried with a
package, but the above rules work well with sprocs.

I prefer ODP.NET (http://otn.oracle.com) to the OracleClient namespace.
NOTE: If you move to 10g, you will have to go to ODP.NET, as the MS libraries
have not been changed to reflect changes in the Oracle database.
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

I thought I was creating an output parameter (see C# snippet below). But I'm
having the same problem with every stored procedure or package call using the
MS libraries, even the ones without parameters, and I get the very same error
message shown below. In fact I've called non-existent stored procedures and
get that error message. It all works properly when I don't use the library
and write my own database access code.

We've looked at earlier versions of ODP.NET and found they were too
sensitive to minor version changes and tended to break when patches were
applied to the database. Has that changed in the recent versions?

 

[Guest]

Downloaded ODP.NET and tried that. It works GREAT! Thanks!

 

[Rob]

I'm trying to use the Enterprise Library to return result sets from Oracle
using a package which returns a reference cursor. While this works outside
the Library using the OracleClient namespace objects directly, I can't get it
to work inside the library. Package access doesn't seem to work at all.
Direct SQL access works great. Has anyone tried to return result sets using a
reference cursor to with the Library? Code snippets are below...

The Package ...
----------------------
CREATE OR REPLACE PACKAGE employee_main
AS
TYPE ref_cursor_type IS REF CURSOR;
PROCEDURE get_employees (emp_cursor OUT ref_cursor_type);
END employee_main;
/

CREATE OR REPLACE PACKAGE BODY employee_main
AS
PROCEDURE get_employees (emp_cursor OUT ref_cursor_type)
IS
BEGIN
OPEN emp_cursor FOR
SELECT *
FROM employees;
END get_employees;
END employee_main;
/

The C# snippet which accesses the package...
---------------------------------
public DataSet GetEmployeeData() {

string pkgName = "employee_main.get_employees";

Database db=DatabaseFactory.CreateDatabase();
DBCommandWrapper cmdWrapper = db.GetStoredProcCommandWrapper(pkgName);
cmdWrapper.AddOutParameter("emp_cursor", DbType.Object, 2000);

return db.ExecuteDataSet(cmdWrapper);
}

and finally, the error I'm getting...
-------------------------------
{"ORA-06550: line 1, column 14:\nPLS-00103: Encountered the symbol
\"PACKAGE\" when expecting one of the following:\n\n := . ( @ % ;\nThe
symbol \":=\" was substituted for \"PACKAGE\" to continue.\n" }


Any help will be greatly appreciated. Thanks!

I got this same error until i stepped through the code. There seems to
be some weird bug where the Name part of the NVP from the Oracle
Package part of the config file is used instead of the Value part. If
you left things as default you have a NVP of Oracle
Package/PACKAGE_NAME What's actually happening is that the code is
putting ORACLE PACKAGE.MY_STORED_PROC as the execution. The space
throws it off and you get the error you listed. I am still having
trouble with a SP that returns a REF CURSOR, but that is a different
issue.