Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Unanswered: Objects in Oracle forms

    I am trying to create a view as detailed below.

    I want to be able to create a form that has fields for property for rent table and the nested collection of lease.

    When I create a datablock for Property_lease_view, I do not have acces to the object. How do I ensure that the object is part of the view and that the fields will appear on the form in Oracle 9i.

    Help on this one would be appreciated.........can see it but I cannot use it...........arghhhh

    ragazza


    CREATE OR REPLACE TYPE lease_type
    AS OBJECT
    (ls_leaseNo NUMBER(4),
    ls_paymentMethod VARCHAR2(15),
    ls_rentStartDate DATE,
    ls_rentFinishDate DATE,
    ls_clientNO NUMBER(4),
    ls_propertyNo NUMBER(4),
    ls_insert_user VARCHAR2(30),
    ls_insert_date DATE,
    ls_update_user VARCHAR2(30),
    ls_update_date DATE);
    /

    CREATE TYPE property_lease_collection AS TABLE OF lease_type;

    CREATE OR REPLACE TYPE property_type IS OBJECT
    (pr_propertyNo NUMBER(4) ,
    pr_street VARCHAR2(30),
    pr_city VARCHAR2(15),
    pr_postcode VARCHAR2(8),
    pr_type VARCHAR2(15),
    pr_room NUMBER(2),
    pr_monthlyRent NUMBER(4),
    pr_sumOfRentReceived NUMBER(6),
    pr_sumOfAdminCost NUMBER(6),
    pr_sumOfMaintenanceCost NUMBER(6),
    pr_ownerNo NUMBER(4),
    pr_staffNo NUMBER(4),
    pr_branchNo NUMBER(4),
    pr_insert_user VARCHAR2(30),
    pr_insert_date DATE,
    pr_update_user VARCHAR2(30),
    pr_update_date DATE,
    pr_details property_lease_collection);
    /

    CREATE OR REPLACE VIEW property_lease_view
    OF property_type
    WITH OBJECT IDENTIFIER (pr_propertyNo) AS
    SELECT p.pr_propertyNo, p.pr_street,p.pr_city, p.pr_postcode, p.pr_type, p.pr_room,
    p.pr_monthlyRent, p.pr_sumOfRentReceived, p.pr_sumOfAdminCost,
    p.pr_sumOfMaintenanceCost, p.pr_ownerNo, p.pr_staffNo, p.pr_branchNo,
    p.pr_insert_user, p.pr_insert_date, p.pr_update_user, p.pr_update_date,
    CAST( MULTISET(
    SELECT l.ls_leaseNo,l.ls_paymentMethod,l.ls_rentStartDate ,l.ls_rentFinishDate,
    l.ls_clientNO,l.ls_propertyNo,l.ls_insert_user,l.l s_insert_date,l.ls_update_user,
    l.ls_update_date
    FROM dh_lease l
    WHERE l.ls_propertyNo = p.pr_propertyNo)
    AS property_lease_collection) AS pr_details
    FROM dh_property_For_Rent p;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Objects in Oracle forms

    I know Oracle Forms very well, but haven't used the latest version. I don't know if it handles all this object stuff.

    Frankly it beats me why you would want to wrap up all your straightforward relational data in object tables and nested tables, and then try to deconstruct it again in Forms. Forms was designed to work with relational tables. The normal Forms approach would be:

    1) CREATE OR REPLACE TABLE property
    (pr_propertyNo NUMBER(4) PRIMARY KEY ,
    pr_street VARCHAR2(30),
    pr_city VARCHAR2(15),
    pr_postcode VARCHAR2(8),
    pr_type VARCHAR2(15),
    pr_room NUMBER(2),
    pr_monthlyRent NUMBER(4),
    pr_sumOfRentReceived NUMBER(6),
    pr_sumOfAdminCost NUMBER(6),
    pr_sumOfMaintenanceCost NUMBER(6),
    pr_ownerNo NUMBER(4),
    pr_staffNo NUMBER(4),
    pr_branchNo NUMBER(4),
    pr_insert_user VARCHAR2(30),
    pr_insert_date DATE,
    pr_update_user VARCHAR2(30),
    pr_update_date DATE,
    pr_details property_lease_collection);
    /

    2) CREATE OR REPLACE TABLE lease
    AS OBJECT
    (ls_leaseNo NUMBER(4) PRIMARY KEY,
    ls_paymentMethod VARCHAR2(15),
    ls_rentStartDate DATE,
    ls_rentFinishDate DATE,
    ls_clientNO NUMBER(4),
    ls_propertyNo NUMBER(4) FOREIGN KEY REFERENCES property(pr_propertyno),
    ls_insert_user VARCHAR2(30),
    ls_insert_date DATE,
    ls_update_user VARCHAR2(30),
    ls_update_date DATE);
    /

    3) Base a block on table property

    4) base a block on table lease, with a master-detail relationship to the property block.

    Simple as that. So is there any reason for creating types, basing tables on those types, creating a view with nested tables, etc. - or just it just sound "sexier" than doing it the easy way?

    (OK, so I'm a Luddite!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •