Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1758
  • Last Modified:

Oracle Reference Cursors using Enterprise Library

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

I used the code provided by Horst.

I have the same problem HELP!!!
I keep coming up with the same problem and cannot figure it out, any help would be appreciated.
--------------------------------------------------------------------------------

[WebMethod(Description = "Compare PriceList")]

publicDataSet ComparePriceListDelete(string OldPriceList, string NewPriceList, refstring Status)

{

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



System.Data.DataSet DSNew = newDataSet();



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;
0
mathieu_cupryk
Asked:
mathieu_cupryk
  • 2
1 Solution
 
MikeOM_DBACommented:

What is the COMPLETE error message?
0
 
mathieu_cuprykAuthor Commented:
this is solved.
0
 
mathieu_cuprykAuthor Commented:
the cursor must be the first parameter.

by DAAB.

this is the solution why it crashes.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now