Re: Procedure based block in Forms 6.0
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...Received on Tue Apr 25 2000 - 00:00:00 CEST
> 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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>