Oracle Reference Cursors using Enterprise Library

  • Thread starter Thread starter Galia via DotNetMonster.com
  • Start date Start date
G

Galia via DotNetMonster.com

I have the trouble with the stored procedure returning ref cursor.

I used the code provided by Horst.

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);
}

Another procedure that updates the record with only IN and OUT parameters
executes without problems. However as soon as I try to include parameter
cmdWrapper.AddOutParameter("emp_cursor", DbType.Object, 2000);
it gives me error on execution db.ExecuteDataSet(cmdWrapper):

"ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of
arguments in call to 'GET_VERSIONS'\nORA-06550: line 1, column 7:\nPL/SQL:
Statement ignored\n"

Help greatly appreciated.
 
I mean IN and OUT parameters of simple types that match the column types of
my table -- NOT of ref cursor type.
 
I have the same problem HELP!!!

[WebMethod(Description = "Compare PriceList")]

public DataSet ComparePriceListDelete(string OldPriceList, string NewPriceList, ref string Status)

{

string procedureName = "init_price.PRICE_LIST_REPORTING.price_list_deletions"; //schema.stored procedure



System.Data.DataSet DSNew = new DataSet();



Database db = DatabaseFactory.CreateDatabase("InitialPrices.Properties.Settings.ConnectionString");

DbCommand dbCommand = db.GetStoredProcCommand(procedureName);

db.AddInParameter(dbCommand, "p_old_price_list_id", DbType.String, OldPriceList);

db.AddInParameter(dbCommand, "p_new_price_list_id", DbType.String, NewPriceList);

db.AddOutParameter(dbCommand, "p_status", DbType.String, 255);

db.AddOutParameter(dbCommand, "p_delete_cursor", DbType.Object, 2000);

DSNew = db.ExecuteDataSet(dbCommand);

Status = dbCommand.Parameters[2].Value.ToString();

return (DSNew);



}


It returns invalid column 7.

here is my stored proc.

PROCEDURE price_list_deletions (cur_out IN OUT t_cursor,
p_old_price_list_id IN price_list_dtl.price_list_hdr_id%TYPE,
p_new_price_list_id IN price_list_dtl.price_list_hdr_id%TYPE,
p_status OUT NOCOPY varchar2)

IS
rec_count number := 0;
BEGIN
p_status := 'Success';
SELECT count(*) into rec_count
FROM (
SELECT grade_code_dtl_id
FROM price_list_dtl
WHERE price_list_hdr_id = p_old_price_list_id
MINUS
SELECT grade_code_dtl_id
FROM price_list_dtl
WHERE price_list_hdr_id = p_new_price_list_id);
If rec_count = 0 then
p_status := 'No Data';
OPEN cur_out
FOR
SELECT count(*)
FROM price_list_dtl
WHERE price_list_hdr_id = p_old_price_list_id
MINUS
SELECT count(*)
FROM price_list_dtl
WHERE price_list_hdr_id = p_new_price_list_id;
else
OPEN cur_out
FOR
SELECT grade_code_dtl_id
FROM price_list_dtl
WHERE price_list_hdr_id = p_old_price_list_id
MINUS
SELECT grade_code_dtl_id
FROM price_list_dtl
WHERE price_list_hdr_id = p_new_price_list_id;
end if;
EXCEPTION
WHEN others THEN
p_status := 'Failure: ' || SQLERRM;
END price_list_deletions;
 
Back
Top