Record Type Question

From: imc <icohen33_at_gmail.com>
Date: 10 Jan 2005 18:47:22 -0800
Message-ID: <1105411642.722268.180420_at_f14g2000cwb.googlegroups.com>

[Quoted] [Quoted] I have a large record that exactly mimics the table structure that I want to store in memory. I can easily use a record type but because the table contains 30 columns it is a rather long type. Is there a way to create a record type for a whole table structure and populate it with less code.

My example:

TYPE company_data_type IS RECORD
(

company_id                       COMPANIES.company_id%TYPE DEFAULT
NULL,
address_1                        COMPANIES.address_1%TYPE DEFAULT
NULL,
address_1_upd                    COMPANIES.address_1_upd%TYPE
DEFAULT NULL,
address_1_lupd                   COMPANIES.address_1_lupd%TYPE
DEFAULT NULL,
address_2                        COMPANIES.address_2%TYPE DEFAULT
NULL,
address_2_upd                    COMPANIES.address_2_upd%TYPE
DEFAULT NULL,
address_2_lupd                   COMPANIES.address_2_lupd%TYPE
DEFAULT NULL,
city                             COMPANIES.city%TYPE DEFAULT NULL,
city_upd                         COMPANIES.city_upd%TYPE DEFAULT
NULL,
city_lupd                        COMPANIES.city_lupd%TYPE DEFAULT
NULL,
postal_code                      COMPANIES.postal_code%TYPE
DEFAULT NULL,
postal_code_upd                  COMPANIES.postal_code_upd%TYPE
DEFAULT NULL,
postal_code_lupd                 COMPANIES.postal_code_lupd%TYPE
DEFAULT NULL,
district                         COMPANIES.district%TYPE DEFAULT
NULL,
district_upd                     COMPANIES.district_upd%TYPE
DEFAULT NULL,
district_lupd                    COMPANIES.district_lupd%TYPE
DEFAULT NULL,
country_id                       COMPANIES.country_id%TYPE DEFAULT
NULL,
country_id_upd                   COMPANIES.country_id_upd%TYPE
DEFAULT NULL,
country_id_lupd                  COMPANIES.country_id_lupd%TYPE
DEFAULT NULL,
telephone_number                 COMPANIES.telephone_number%TYPE

DEFAULT NULL,
telephone_number_upd
COMPANIES.telephone_number_upd%TYPE DEFAULT NULL, telephone_number_lupd
COMPANIES.telephone_number_lupd%TYPE DEFAULT NULL, business_description
COMPANIES.business_description%TYPE DEFAULT NULL, business_description_upd
COMPANIES.business_description_upd%TYPE DEFAULT NULL, business_description_lupd
COMPANIES.business_description_lupd%TYPE DEFAULT NULL,

quotation_symbol                 COMPANIES.quotation_symbol%TYPE

DEFAULT NULL,
quotation_symbol_upd
COMPANIES.quotation_symbol_upd%TYPE DEFAULT NULL, quotation_symbol_lupd
COMPANIES.quotation_symbol_lupd%TYPE DEFAULT NULL,

parent_id_usage                  COMPANIES.parent_id_usage%TYPE

DEFAULT NULL,
parent_id_usage_upd
COMPANIES.parent_id_usage_upd%TYPE DEFAULT NULL, parent_id_usage_lupd
COMPANIES.parent_id_usage_lupd%TYPE DEFAULT NULL,

parent_id                        COMPANIES.parent_id%TYPE DEFAULT
NULL,
parent_id_upd                    COMPANIES.parent_id_upd%TYPE
DEFAULT NULL,
parent_id_lupd                   COMPANIES.parent_id_lupd%TYPE
DEFAULT NULL,
scale                            COMPANIES.scale%TYPE DEFAULT
NULL,
scale_upd                        COMPANIES.scale_upd%TYPE DEFAULT
NULL,
scale_lupd                       COMPANIES.scale_lupd%TYPE DEFAULT
NULL,
employees                        COMPANIES.employees%TYPE DEFAULT
NULL,
employees_upd                    COMPANIES.employees_upd%TYPE
DEFAULT NULL,
employees_lupd                   COMPANIES.employees_lupd%TYPE
DEFAULT NULL,
url                              COMPANIES.url%TYPE DEFAULT NULL,
url_upd                          COMPANIES.url_upd%TYPE DEFAULT
NULL,
url_lupd                         COMPANIES.url_lupd%TYPE DEFAULT
NULL,
ownership_id                     COMPANIES.ownership_id%TYPE
DEFAULT NULL,
ownership_id_upd                 COMPANIES.ownership_id_upd%TYPE
DEFAULT NULL,
ownership_id_lupd                COMPANIES.ownership_id_lupd%TYPE
DEFAULT NULL,
co_rowid                         ROWID

);

v_company_type                    company_data_type;

and to populate it I would need to do the following:

SELECT company_id,

address_1,
address_1_upd,
address_1_lupd,
address_2,
address_2_upd,
address_2_lupd,

city,
city_upd,
city_lupd,

postal_code,
postal_code_upd,
postal_code_lupd,

district,
district_upd,
district_lupd,

country_id,
country_id_upd,
country_id_lupd,
telephone_number,

telephone_number_upd,
telephone_number_lupd,

business_description,
business_description_upd,
business_description_lupd,
quotation_symbol,
quotation_symbol_upd,
quotation_symbol_lupd,
parent_id_usage,
parent_id_usage_upd,
parent_id_usage_lupd,
parent_id,

parent_id_upd,
parent_id_lupd,
scale,
scale_upd,
scale_lupd,
employees,
employees_upd,
employees_lupd,
url,
url_upd,
url_lupd,

ownership_id,
ownership_id_upd,
ownership_id_lupd,

rowid
INTO v_company_type.company_id,

v_company_type.address_1,
v_company_type.address_1_upd,
v_company_type.address_1_lupd,
v_company_type.address_2,
v_company_type.address_2_upd,
v_company_type.address_2_lupd,
v_company_type.city,
v_company_type.city_upd,
v_company_type.city_lupd,
v_company_type.postal_code,
v_company_type.postal_code_upd,
v_company_type.postal_code_lupd,
v_company_type.district,
v_company_type.district_upd,
v_company_type.district_lupd,
v_company_type.country_id,
v_company_type.country_id_upd,
v_company_type.country_id_lupd,
v_company_type.telephone_number,
v_company_type.telephone_number_upd,
v_company_type.telephone_number_lupd,
v_company_type.business_description,
v_company_type.business_description_upd,
v_company_type.business_description_lupd,
v_company_type.quotation_symbol,
v_company_type.quotation_symbol_upd,
v_company_type.quotation_symbol_lupd,
v_company_type.parent_id_usage,
v_company_type.parent_id_usage_upd,
v_company_type.parent_id_usage_lupd,
v_company_type.parent_id,
v_company_type.parent_id_upd,
v_company_type.parent_id_lupd,
v_company_type.scale,
v_company_type.scale_upd,
v_company_type.scale_lupd,
v_company_type.employees,
v_company_type.employees_upd,
v_company_type.employees_lupd,
v_company_type.url,
v_company_type.url_upd,
v_company_type.url_lupd,
v_company_type.ownership_id,
v_company_type.ownership_id_upd,
v_company_type.ownership_id_lupd,
v_company_type.co_rowid

FROM companies
WHERE company_id = v_company_id
FOR UPDATE OF co.address_1;
Thanks in advance,

icohen33 Received on Tue Jan 11 2005 - 03:47:22 CET

This message: [ Message body ]
Next message: Mark C. Stock: "Re: Record Type Question"
Previous message: DA Morgan: "Re: Oracle Database Installation Information"
Next in thread: Mark C. Stock: "Re: Record Type Question"
Reply: Mark C. Stock: "Re: Record Type Question"
Reply: DA Morgan: "Re: Record Type Question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message