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.
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.
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. :)
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;
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);