Solved

Oracle Reference Cursors using Enterprise Library

Posted on 2008-06-23
6
1,657 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
  • 2
6 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
Comment Utility

What is the COMPLETE error message?
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
this is solved.
0
 

Accepted Solution

by:
mathieu_cupryk earned 0 total points
Comment Utility
the cursor must be the first parameter.

by DAAB.

this is the solution why it crashes.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now