Solved

Oracle Store Procs with Resultset

Posted on 2004-04-21
4
783 Views
Last Modified: 2013-12-26

Hello,

I am getting some difficulty in Calling Oracle Stored Procedure thru PB.

I have create one store proc in Oracle that returns multiple records based on the condition given.
Ex: my SQL select statement in the store proc is:
SELECT empno,ename,job,sal FROM emp WHERE deptno = deptid.
where deptid is a IN type of argument. I am associating this query with a cursor. The procedure is returning the records using a cursor(OUT) variable.
 
Now i want to return the resultset associated with the cursor to PB object.
I want to displayed these records in the Datawindow control in my application. I will pass the parameter from the application to the store proc and it should display records dynamically according to the parameter given.

Please tell me how i can achieve this? What r the stepts for this? pls give us complete example of this.

Also i have created one datawindow runtime using the Create method. but by default it is the taking the layout of the datawindow as FreeForm and i want to change it to Grid or Tabular style. Please tell how i can do this runtime i.e. while creating the datawindow object.

Thanks



0
Comment
Question by:sumitad
[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
4 Comments
 

Accepted Solution

by:
mit_ashu earned 80 total points
ID: 10926071
Hi

I have used following sp in Oracle and created a Tabular Type of datawindow , I think it is similar to your requirement , don't get bothered about rest of the SP , what I have done it simply declared a cursor which will give me multiple records , it put the cursor in OUT Parameter. The SP have one IN Parameter

On PB side :
1. Select New Datawindow
2.Select Datastore as Stored Procedure
3. It will show you list of SPs , select the SP from list and click Next
(This might take a while if the SP is in Package and Package is large.)
4. Specify other Design Parameters and click Finish
4. Power Builder will generate the required Datawindow with Out Parametres of cursor as Result set columns and SP's IN Parameter as Retrieval Argument.
5. Though you have Open the cursor , do not worry about closing it , Powerbuilder will handle it internally.





SP Code :

PROCEDURE sp_retrieve_cmnt                                          
    (  num_in IN ip_cmnt.num%TYPE,
       p_rec_comment_cursor OUT rec_comment_cursor
     )
IS
ls_cmnt_cde    varchar2(50);

BEGIN
      OPEN p_rec_comment_cursor FOR
      SELECT     cmnt.cmnt_id,                                                                                                                          
         cmnt.cmnt_cde,                            
         cmnt.cmnt_text,                                        
         cmnt.creat_dt_tm,                                                                                    
         cmnt.log_creat_id,
         ip_cmnt._num
        FROM cmnt, ip, ip_cmnt --, emply
        WHERE cmnt.cmnt_id = ip_cmnt.cmnt_id
         AND ip_cmnt._num = num_in
   AND Instr( ls_cmnt_cde,('*'||trim(cmnt.cmnt_cde)||'*')) > 0;
END sp_retrieve_cmnt;



Thanks

Ashu
0
 

Expert Comment

by:mit_ashu
ID: 11149920
Hi

did you this resolved your issue ???
0

Featured Post

Technology Partners: 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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

726 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