?
Solved

Crystal Report Stored Procedure , C# , Ref Cursors

Posted on 2010-01-12
3
Medium Priority
?
1,834 Views
Last Modified: 2013-12-19
Hi ,

I am facing a unique problem.

I have a stored procedure which contains a ref cursor for an global temp table  :-

create Global Temporary table gt
(
a number,
b number
)

CREATE OR REPLACE PACKAGE pkg_gt
as type ref_gt is ref cursor
return gt%ROWTYPE;
end pkg_gt;
/

CREATE OR REPLACE procedure
proc_gt
(
tmp_cursor In OUT pkg_gt.ref_gt ,
va number default null , vb numberdefault null
)
as
begin
Open tmp_cursor for
select
nvl(va,1) a, nvl(va,1)  b from dual
;
end proc_gt;
/

grant execute on proc_gt to public

Now I am creatin a crystal report in VS 2008 and tried to see the preview , I am getting "Failed to open row set"

Why I am amazed is because the same procedure with only one in parameter "va" and I create a crystal report that works and i can see the preview ... I am not sure whts going wrong ... Even i wrote all code also and tried to call the report in asp page thats also not working

I am attaching the code here

protected void Page_Load(object sender, EventArgs e)
    {
        ReportDocument customCrystalReport = new ReportDocument();
        customCrystalReport.Load(Server.MapPath("crsuccess.rpt"));


        String paramName;
        CrystalDecisions.Shared.ParameterValues pList = new ParameterValues();
        CrystalDecisions.Shared.ParameterDiscreteValue pV = new ParameterDiscreteValue();

        customCrystalReport.SetDatabaseLogon("scott", "password");

        //pass parameters here
        for (int i = 0; i < customCrystalReport.DataDefinition.ParameterFields.Count; i++)
        {
            paramName = customCrystalReport.DataDefinition.ParameterFields[i].Name;
            //paramValue = Request.QueryString.Get(paramName);
            pV.Value = 1;
            pList.Add(pV);
            customCrystalReport.DataDefinition.ParameterFields[paramName].ApplyCurrentValues(pList);
        }
       
        foreach (CrystalDecisions.CrystalReports.Engine.Table repTable in customCrystalReport.Database.Tables)
        {
            CrystalDecisions.Shared.TableLogOnInfo repLogOn;
            repLogOn = repTable.LogOnInfo;

            repLogOn.ConnectionInfo.UserID = "scott";
            repLogOn.ConnectionInfo.Password = "password";
            repLogOn.ConnectionInfo.ServerName = "qbank_dev";

            repTable.ApplyLogOnInfo(repLogOn);
        }
 

        if (customCrystalReport.Subreports.Count > 0)
        {
            List<string> subReports = new List<string>();
            for (int i = 0; i < customCrystalReport.Subreports.Count; i++)
            {
                subReports.Add(customCrystalReport.Subreports[i].Name);
            }

            for (int i = 0; i < subReports.Count; i++)
            {
                CrystalDecisions.CrystalReports.Engine.ReportDocument reportSubReport = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
                reportSubReport = customCrystalReport.OpenSubreport(subReports[i]);

                foreach (CrystalDecisions.CrystalReports.Engine.Table repTable in reportSubReport.Database.Tables)
                {
                    CrystalDecisions.Shared.TableLogOnInfo repLogOn;
                    repLogOn = repTable.LogOnInfo;

                    repLogOn.ConnectionInfo.UserID = "scott";
                    repLogOn.ConnectionInfo.Password = "password";
                    repLogOn.ConnectionInfo.ServerName = "qbank_dev";

                    repTable.ApplyLogOnInfo(repLogOn);
                }
            }
        }
        CrystalReportViewer1.ReportSource = customCrystalReport;
        CrystalReportViewer1.DataBind();
        //code for Parameter in procedure p_SUBJECTID ends
       
    }


Please help stuck since last two days in this same issue.
Regards
Pragya
0
Comment
Question by:pragyat9
[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
3 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 26294272
0
 

Author Comment

by:pragyat9
ID: 26294376
Hi mlmcc,

thanks for the reply but i got the solution by changing the signature of my stored procedure as following


  // I only re-ordered the parameters and made the ref cursor as out parameter ... it worked like a magic ... still not convinced why the previous one didn't worked

CREATE OR REPLACE procedure
proc_gt
(
va number default null , vb numberdefault null,
tmp_cursor OUT pkg_gt.ref_gt
)
as
begin
Open tmp_cursor for
select
nvl(va,1) a, nvl(va,1)  b from dual
;
end proc_gt;
/


thanks a lot for the post !!!
0
 

Author Closing Comment

by:pragyat9
ID: 31675996
Thanks for your help !!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

752 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