Lookup Items in a Forms Block.

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/12/29
Message-ID: <19971229191900.OAA22002_at_ladder01.news.aol.com>#1/1

Hi

Sorry this isn't the orginal subject - I lost the posting so I hope the person who posted the question reads this. (If you do please let me know).

I think what you are trying to do is populate a block with foreign key lookup values,

Example using Oracle's scott/tiger demo tables:

Table EMP Table DEPT

 EMPNO        DEPTNO
 ENAME        DNAME    
 JOB          LOC     
 MGR                    
 HIREDATE               
 SAL                    
 COMM                   
 DEPTNO                 

We want to display the employee information with the manager's name and the
department name.

1. Create a block EMP with all the table columns, but set the displayed properties of MGR and DEPTNO to false.
2. Create non-base table items of type varchar2, MANAGER and DEPARTMENT. Make these displayed and place them where you want them in the layout.
3. Create a POS-QUERY trigger on the block EMP to retrieve the values from tables DEPT and EMP (we treat this second occurrence of EMP as a separate table as we are looking for a different record).

   A post-query trigger fires once for
   EACH record retrieved by the query

   Trigger Code:

   BEGIN
    BEGIN

     SELECT dname
       INTO :emp.department
       FROM dept
      WHERE deptno = :emp.deptno;
     EXCEPTION
      WHEN OTHERS THEN NULL;

    END;
    BEGIN

     SELECT ename
       INTO :emp.manager
      WHERE empno = :emp.mgr;
     EXCEPTION
      WHEN OTHERS THEN NULL;

    END;
   END; Note that I have just ignored, rather than handled exceptions.

You can find more examples of this, plus how to do it more efficiently using stored functions and procedures, on my Oracle Utilities Page at my web site - http://members.aol.com/jomarlen/

Check out "Functional Magic" - an article I wrote for Oracle Informant Magazine
and
"Towards Object Orientation Using Stored Functions and Procedures" - my award winning paper from the ODTUG Fall '97 conference.

Regards
John

---

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd. LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

---

Received on Mon Dec 29 1997 - 00:00:00 CET