Solved

Oracle Reference Cursors using Enterprise Library

Posted on 2008-06-23
6
1,708 Views
Last Modified: 2008-07-02
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
Comment
Question by:mathieu_cupryk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21858927

What is the COMPLETE error message?
0
 

Author Comment

by:mathieu_cupryk
ID: 21860676
this is solved.
0
 

Accepted Solution

by:
mathieu_cupryk earned 0 total points
ID: 21891277
the cursor must be the first parameter.

by DAAB.

this is the solution why it crashes.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question