Solved

Oracle Store Procs with Resultset

Posted on 2004-04-21
4
780 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
  • 2
4 Comments
 

Accepted Solution

by:
mit_ashu earned 80 total points
Comment Utility
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
Comment Utility
Hi

did you this resolved your issue ???
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

744 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

17 Experts available now in Live!

Get 1:1 Help Now