Oracle Store Procs with Resultset


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.


Who is Participating?
mit_ashuConnect With a Mentor Commented:

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;



did you this resolved your issue ???
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.

All Courses

From novice to tech pro — start learning today.