?
Solved

Multiple Ref cursor as Out Parameter

Posted on 2008-06-17
14
Medium Priority
?
1,789 Views
Last Modified: 2013-12-07
Hi experts,
i've a question about using reference cursors into delphi.
i've a pl/sql procedure which has 2 out parameters which are reference cursor.
1) how do i access all the data. I want to print this data in quick report.
2) what if a cursor returns no value.

thanks
0
Comment
Question by:Asmitagoyal
[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
  • 5
  • 3
  • 3
  • +1
14 Comments
 

Author Comment

by:Asmitagoyal
ID: 21801578
Dear all
suggestions required very fast. It is very urgent
thanks for all your help
0
 
LVL 14

Accepted Solution

by:
SteveBay earned 1000 total points
ID: 21801883
Let me suggest that you use as QuickAbstractRep and QRStringsBand in the report.
Here are some very general instructions. The code I include is meant as a rough example. I did not test it.

- Add form to to your applicaiton.
- Add QuickAbstractRep to the form.
- Place QRStringsBand on the QuickAbstractRep.
- Place a QRExpr on the QRStringsBand and set it's Expression to the QRSTRINGSBAND1 variable.  This will print the QRStringsBand1.Item.
-Place one or more QRLabels on the QRStringsBand.

Define an object into which you can place the values returned by your query,
type TMyObject = class(TObject)
  public
     value1 : String;
     value2 : String;
     value3 : String;
    {etc...}
end;

Then you can traverse your Cursor and load the values into the Items property of the QRStringsBand.

procedure TForm1.PrepareReport;
var MyObject : TMyObject;
begin
     While not Query1.eof do
          begin
          MyObject := TMyObject.Create;
        // if there is no value you can assign an empty string here
          MyObject.value1 := Query1.Parameters[0].Value;
          MyObject.value2 := Query1.Parameters[1].Value;
          MyObject.value3 := Query1.Parameters[2].Value;
        {etc...}
          // Assign the object as well as a string to the Items List.
          QRStringsBand1.Items.AddObject(MyObject.value1, MyObject)
          Query1.Next;
          end;
end;

In the BeforePrint Event of the QuickAbstractRep you can reference the values in MyObject

procedure TForm1.QRStringsBand1BeforePrint(Sender: TQRCustomBand;  var PrintBand: Boolean);
var MyObject : TMyObject;
begin
     //Get a refference to MyObject;
     MyObject := TMyObject(TQRStringsBand(Sender).Items.Objects[TQRStringsBand(Sender).Index]);
     // first Item is printed by QRExpr1
     QRLabel1.Caption := MyObject.value2;
     QRLabel2.Caption := MyObject.value3;
     {etc...}
end;

Make sure to free the Objects aftrer printing them.

procedure TForm1.QuickAbstractRep1AfterPrint(Sender: TObject);
begin
     TMyObject(TQRStringsBand(Sender).Items.Objects[TQRStringsBand(Sender).Index]).Free;
end;

I hope this helpful.
   
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 21802789
intersting problem

there is a sample for one ref-cursor

http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_20842305.html

SteveBay: guess you are off topic ;-)
Asmitagoyal: let me give a try

meikl ;-)
0
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!

 

Author Comment

by:Asmitagoyal
ID: 21810028
hi
i've worked with one ref cursor as out parameter but do not know how to work with multiple ref cursor as out parameter.
i basically want to display the recordsets returned by ref cursors into quick report.
In quick reports work with one dataset is alright but how to proceed with multiple datasets?????
0
 
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 1000 total points
ID: 21810512
Ado has multiple datasets, haven't tried this yet.
But maybe this is the direction your looking for

As I remember there was a proc in the TAdoStoredProc or TAdoQuery
TAdoStoredProc.NextDataset

0
 

Author Comment

by:Asmitagoyal
ID: 21810686
But then how will i be using that in the quick reports
0
 
LVL 14

Expert Comment

by:SteveBay
ID: 21811800
Off topic or not, the method I described - using QuickAbstractRep and BeforePrint event is a way to handle multiple datasets.
0
 

Author Comment

by:Asmitagoyal
ID: 21812026
Hi
where can i find 'QuickAbstractRep '. I've just installed quickrep component and in that i can't find QuickAbstractRep
thanks
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21812181
What exactly is it that you want to print on the report ?
maybe there is other easier way ?
0
 
LVL 14

Expert Comment

by:SteveBay
ID: 21812519
I use QuickReports Pro. It's posible that it's only available in that version
0
 

Author Comment

by:Asmitagoyal
ID: 21818912
I want to print data on report. and this data is coming from multiple queries. So i thought of writing a stored procedure which returns ref cursors as OUT parameters.
I've got an alternative solution to it by using multiple datasets (tquery component) on the form. I wanted that if only a single procedure could suffice my problem in which i could rite all my queries and then return the datasets.
If anyone finds a solution to this then kindly let me know.

Thanks
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 21863257
Nive idea, but i think you will end up with what you have.

If you look at the total result, it will be the same anyway.
X queries give X datasets

later if you get it to work ...
1 procedure gives X datasets

by the way, it will be simpler to find 1 error in the X queries than in the 1 procedure
only one of the X queries will give an error ...
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!

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

719 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