This discussion is archived
8 Replies Latest reply: Jun 1, 2012 5:23 AM by 577162 RSS

using plsql table and ref cursor in oracle forms 10g

extreme Newbie
Currently Being Moderated
Hi all,
Can anyone give me an example of a scenario where we need to create a form manually based on a database stored procedures.
And in that procedure i have created a pl/sql table and a ref cursor in data base level.

CREATE OR REPLACE PACKAGE SCOTT.BONUS_PKG IS TYPE bonus_rec
IS RECORD(
empno     bonus_EMP.empno%TYPE,
ename     bonus_EMP.ename%TYPE,
job     bonus_EMP.job%TYPE,
sal     bonus_EMP.sal%TYPE,
comm     bonus_EMP.comm%TYPE);

TYPE b_cursor IS REF CURSOR RETURN bonus_rec;
TYPE bontab IS TABLE OF bonus_rec INDEX BY BINARY_INTEGER;

PROCEDURE bonus_refcur(bonus_data IN OUT b_cursor);
PROCEDURE bonus_query(bonus_data IN OUT bontab);
END bonus_pkg;


CREATE OR REPLACE PACKAGE BODY SCOTT.BONUS_PKG IS
PROCEDURE bonus_query(bonus_data IN OUT bontab) IS
ii NUMBER;
CURSOR bonselect IS
SELECT empno, ename, job, sal, comm FROM bonus_EMP ORDER BY empno;
BEGIN
OPEN bonselect;
ii := 1;
LOOP
FETCH bonselect INTO
bonus_data( ii ).empno,
bonus_data( ii ).ename,
bonus_data( ii ).job,
bonus_data( ii ).sal,
bonus_data( ii ).comm;
EXIT WHEN bonselect%NOTFOUND;
ii := ii + 1;
END LOOP;
END bonus_query;

PROCEDURE bonus_refcur(bonus_data IN OUT b_cursor) IS
BEGIN
OPEN bonus_data FOR SELECT empno, ename, job, sal, comm FROM bonus_EMP ORDER BY empno;
END bonus_refcur;

END bonus_pkg;

i want to populate the data in forms manually not using forms data block wizard and programmatically.

please reply...
  • 2. Re: using plsql table and ref cursor in oracle forms 10g
    DanielB Pro
    Currently Being Moderated
    or create a public variable of ref cursor and programming the open as you do, and a procedure fetch and read and check each row
  • 3. Re: using plsql table and ref cursor in oracle forms 10g
    extreme Newbie
    Currently Being Moderated
    hi DanielB,
    i have written those packages in database level
    i want that what can i write in forms 10g so that i can populate data in blocks
    using those packages.


    please reply....
  • 4. Re: using plsql table and ref cursor in oracle forms 10g
    DanielB Pro
    Currently Being Moderated
    use the open procedure
    go_block('yourblock');
    fetch_from_cursor;
    while found of cursor loop
    populate_record;
    next_record;
    fetch_from_cursor;
    end loop;
    close cursor;
    first_record;
  • 5. Re: using plsql table and ref cursor in oracle forms 10g
    InoL Guru
    Currently Being Moderated
    not using forms data block wizard
    What 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.

    Read this:
    PL/SQL 101 : Understanding Ref Cursors
    especially:
    3. What is the point of ref cursors?
  • 6. Re: using plsql table and ref cursor in oracle forms 10g
    DanielB Pro
    Currently Being Moderated
    i am prefer use block based table, or if need block based procedure

    but a lot of people use the block as control block and populate and populate and use update's insert's
    not using forms as forms

    but if they ask how use ref cursor's, how use cursor's i answer.
  • 7. Re: using plsql table and ref cursor in oracle forms 10g
    CraigB Guru
    Currently Being Moderated
    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:
    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;
    This code sample demonstrates the basics, but as it is sample code - you will have to adapt it to your situation.

    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,
    Craig B-)

    If someone's response is helpful or correct, please mark it accordingly.
  • 8. Re: using plsql table and ref cursor in oracle forms 10g
    577162 Newbie
    Currently Being Moderated
    Hi,

    Thanks for he valuable post .

    I'm facing the below problem though i did th same code.
    create package pkg

    type t_rec is recod (id number,id_name varchar2(10));

    type t_data is table of t_rec index by binary_integer;
    act_data t_data;

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

    p_cur sys_refcursor;
    l_text varchar2(100);
    indx number:=0;
    l_text varchar2(1000):=null; -- i have built adynamic where clause which has a complex logic as per requirement in this variable
    begin

    t_data:=&t_data -- i passed 10
    dept :&dept -- ipassd 10

    open p_cur for 'select id,id_name from tab1'||l_text';
    loop
    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
    indx:=indx+1;
    end loop;
    end return_data;


    ...
    exception ....
    end pkg;
    Now again i called package as below

    DECLARE
    bk_data PKG.T_DATA;
    BEGIN
    PKG.RETURN_DATA(bk_data, 10, 11);
    dbms_output.put_line('id is '||bk_data(1).id);
    end;
    here its not giving data though when it's giving data in -----4
    Can you please help me in this?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points