This content has been marked as final. Show 8 replies
not using forms data block wizardWhat is it with some programmers and ref cursors? There is hardly ever a need for ref cursors. In your case you just create a Forms block based on table EMP.
PL/SQL 101 : Understanding Ref Cursors
3. What is the point of ref cursors?
Can anyone give me an example of a scenario where we need to create a form manually based on a database stored procedures.Typically, you would use a procedure based block when you have a collection of data from multiple tables presented in a Form and your user needs to be able to update the information displayed.
From your code example, it looks like you are using Oracle Support document "Basing a Block on a Stored Procedure - Sample Code [ID 66887.1]". If this is the case, keep following the document - it walks you through all of the steps. There is no need to Manually configure things that the Data Block Wizard will perform for you!
i want to populate the data in forms manually not using forms data block wizard and programmatically.Why? Let the Data Block Wizard take care of configuring your block based on a procedure for you. There is no need to manually loop through the data! I've actually done what you are attempting and it was more work than was needed. Let Forms do the work for you. :)
If you absolutely must do things manually, I recommend you use the PROCEDURE bonus_query(bonus_data IN OUT bontab) instead of the bonus_refcur(bonus_data IN OUT b_cursor) . Then, in your code create a variable of type BONTAB and then call the bonus_query procedure. Then it is a simple case of looping through the table of records returned by the bonus_query procedure. For example:
This code sample demonstrates the basics, but as it is sample code - you will have to adapt it to your situation.
DECLARE t_bonus bonus_pkb.bontab; BEGIN bonus_pkg.bonus_query(t_bonus); FOR i in 1 .. t_bonus.count LOOP :YOUR_BLOCK.EMPLOYEE_NUMBER := t_bonus(i).empno; :YOUR_BLOCK.EMPLOYEE_NAME := t_bonus(i).ename; :YOUR_BLOCK.EMPLOYEE_JOB := t_bonus(i).job; :YOUR_BLOCK.EMPLOYEE_SALARY := t_bonus(i).sal; :YOUR_BLOCK.EMPLOYEE_COMMISSION := t_bonus(i).comm; END LOOP; END;
Also, I strongly recommend you look at the article InoL listed. This is a very comprehensive discussion on REF CURSORs. If you are set on using a procedure based data source - it is more efficient to pass the table of records back to your form than it is to pass a ref cursor. Using a ref cursor, you might as well just using a standard named cursor and loop through your named cursor. The effect is the same (one row returned at a time creating lots of network traffic). Using the table of records is more efficient because the entire data set is returned so network traffic is reduced.
Hope this helps,
If someone's response is helpful or correct, please mark it accordingly.
Thanks for he valuable post .
I'm facing the below problem though i did th same code.
create package pkgNow again i called package as below
type t_rec is recod (id number,id_name varchar2(10));
type t_data is table of t_rec index by binary_integer;
procedure return_data is (o_rec out t_data,dept number,region number) ;
create package body pkg
procedure return_data is (o_rec out t_data,dept number,region number);
l_text varchar2(1000):=null; -- i have built adynamic where clause which has a complex logic as per requirement in this variable
t_data:=&t_data -- i passed 10
dept :&dept -- ipassd 10
open p_cur for 'select id,id_name from tab1'||l_text';
fetch p_cur into o_rec(indx).id,o_rec(indx).id_name;
exit when p_cur%notfound;
dbms_output.put_line('id is '||o_rec(indx).id); ---- 4
DECLAREhere its not giving data though when it's giving data in -----4
PKG.RETURN_DATA(bk_data, 10, 11);
dbms_output.put_line('id is '||bk_data(1).id);
Can you please help me in this?