?
Solved

Crystal Report Stored Procedure , C# , Ref Cursors

Posted on 2010-01-12
3
Medium Priority
?
1,897 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
  • 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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

569 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