Oracle Store Procs with Resultset

Posted on 2004-04-21
Medium Priority
Last Modified: 2013-12-26


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.


Question by:sumitad
  • 2

Accepted Solution

mit_ashu earned 320 total points
ID: 10926071

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
ls_cmnt_cde    varchar2(50);

      OPEN p_rec_comment_cursor FOR
      SELECT     cmnt.cmnt_id,                                                                                                                          
        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;



Expert Comment

ID: 11149920

did you this resolved your issue ???

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deleti…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand column That will then direct you to their download page. From that p…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

621 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