Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 791
  • Last Modified:

Oracle Store Procs with Resultset


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
sumitad
Asked:
sumitad
  • 2
1 Solution
 
mit_ashuCommented:
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
 
mit_ashuCommented:
Hi

did you this resolved your issue ???
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now