Re: Procedure based block in Forms 6.0

From: Anthony Hegarty <ahegarty_at_eircom.net>
Date: 2000/04/25
Message-ID: <0%mN4.7625$sB3.5570_at_news.indigo.ie>


Hello Mike

I think the main point of using the form this way is that you can build a plsql table from a number of different tables so instead of things like having code in your post query trigger to get lookup information all the work is done on the server. This is supposed to be quicker with less network traffic etc.

Below is the code that the block is based on.

PACKAGE emp_pkg AS
TYPE emprec IS RECORD(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
sal emp.sal%type,
deptno emp.deptno%type);

TYPE empnorec IS RECORD(
empno emp.empno%type);

TYPE empcur IS REF CURSOR RETURN emprec;

TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER; TYPE empnotab IS TABLE OF empnorec INDEX BY BINARY_INTEGER;

--PROCEDURE empquery_refcur(block_data IN OUT empcur, p_deptno IN NUMBER); PROCEDURE empquery(block_data IN OUT emptab, p_deptno IN NUMBER);

PROCEDURE empinsert(block_data IN emptab);
PROCEDURE empupdate(block_data IN emptab);
PROCEDURE empdelete(block_data IN empnotab);
PROCEDURE emplock(block_data IN empnotab); END; PACKAGE BODY emp_pkg AS
/*PROCEDURE empquery_refcur(block_data IN OUT empcur, p_deptno IN NUMBER) IS BEGIN
     OPEN block_data FOR
     SELECT empno, ename, job, sal, deptno
     FROM emp
     WHERE deptno = NVL(p_deptno, deptno)
     ORDER BY empno;

END;*/
---

PROCEDURE empquery(block_data IN OUT emptab, p_deptno IN NUMBER) IS
i NUMBER;
CURSOR empselect IS
     SELECT empno, ename, job, sal, deptno
     FROM   emp
     WHERE  deptno = NVL(p_deptno, deptno)
     ORDER  BY empno;

BEGIN
   insert into temp (test,deptno)
   values('t',p_deptno);

    OPEN empselect;
    i := 1;
    LOOP
       FETCH empselect INTO block_data(i).empno, block_data(i).ename,
       block_data(i).job, block_data(i).sal,block_data(i).deptno;
       EXIT WHEN empselect%notfound;
       i := i + 1;
    END LOOP;
END;
----------------------------------------------------------------------------
---

PROCEDURE empinsert(block_data IN emptab) IS
i NUMBER;
cnt NUMBER;
BEGIN
    cnt := block_data.count;
    FOR i IN 1..cnt LOOP
       INSERT INTO emp(empno, ename, job,sal,deptno)
       VALUES(block_data(i).empno, block_data(i).ename,
       block_data(i).job, block_data(i).sal,block_data(i).deptno);
    END LOOP;
END;
----------------------------------------------------------------------------
---

PROCEDURE empupdate(block_data IN emptab) IS
i NUMBER;
cnt NUMBER;
BEGIN
    cnt := block_data.count;
    FOR i IN 1..cnt LOOP
       UPDATE emp
       SET ename = block_data(i).ename, job = block_data(i).job,
       sal = block_data(i).sal, deptno = block_data(i).deptno
       WHERE empno = block_data(i).empno;
    END LOOP;
END;
----------------------------------------------------------------------------
---

PROCEDURE empdelete(block_data IN empnotab) IS
i NUMBER;
cnt NUMBER;
BEGIN
    cnt := block_data.count;
    FOR i IN 1..cnt LOOP
       DELETE FROM emp
        WHERE empno = block_data(i).empno;
    END LOOP;
END;
----------------------------------------------------------------------------
---

PROCEDURE emplock(block_data IN empnotab) IS
i NUMBER;
cnt NUMBER;
block_rec emprec;
BEGIN
    cnt := block_data.count;
    FOR i IN 1..cnt LOOP
       SELECT empno, ename, job,sal,deptno
       INTO block_rec
       FROM emp
       WHERE empno = block_data(i).empno
       FOR UPDATE OF ename NOWAIT;
    END LOOP;
END;
END;

Anthony

Mike Dwyer <dwyermj_at_co,larimer.co.us> wrote in message
news:gO3N4.5$Dg.907_at_wdc-read-01.qwest.net...

> Without seeing Oracle's code it is hard to answer "why?" But, it does seem
> unnecessary to BUILD a table for the block if the emp table already exists
> and can be limited with a where clause. I must not be understanding
> something.
>
> But the WHERE clause property becomes an actual WHERE clause in the SQL
> query sent to the database engine. The result set is limited at the
> server/database, not at the Form.
>
> "Anthony Hegarty" <ahegarty_at_eircom.net> wrote in message
> news:OZ_M4.7357$sB3.5287_at_news.indigo.ie...
> > Thanks for answering,
> >
> > That's one way of doing it, but why in the example do Oracle show that
you
> > can pass a parameter to the procedure when it does nothing? You can see
in
> > the query-procedure trigger created by the form that it does pass in a
> > parameter and I thought that this is based on the blocks
> > query-data-source-arguments in the blocks properties. What are these
for if
> > not to pass parameters to the procedure ? Any ideas?
> >
> > If I do it this way is the plsql block filled with all the details and
then
> > the form limits what is retrieved. If this is so, it won't be much good
to
> > me as the table I want to base this on will have millions of rows.
> >
> > Anthony.
> >
> > Anthony.
> > Mike Dwyer <dwyermj_at_co,larimer.co.us> wrote in message
> > news:BBYM4.72$Ad.17907_at_wdc-read-01.qwest.net...
> > > Use the WHERE Clause property on the block. You can hard-code it in
the
> > > property sheet or change it dynamically with SET_BLOCK_PROPERTY(...,
> > > DEFAULT_WHERE,...).
> > >
> > > "Anthony Hegarty" <ahegarty_at_eircom.net> wrote in message
> > > news:f2UM4.7286$sB3.5259_at_news.indigo.ie...
> > > > Hello
> > > >
> > > > I've been trying to use this function in a form and have used the
example
> > > > for the oracle metalink site. It is based on the emp table. What I
need to
> > > > know is how to limit the query of the the base table to certain
deptno's.
> > > > In the example it shows that the deptno is passed as a parameter to
the
> > > > procedure that builds the plsql table on which the block is based,
but it
> > > > doesn't seem to do anything.
> > > >
> > > > I would be grateful for any help on this as I think it could be a
very
> > > > useful funtion of forms.
> > > >
> > > > Regards Anthony.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Apr 25 2000 - 00:00:00 CEST

Original text of this message