Re: Record Type Question

imc wrote:
> 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

CREATE OR REPLACE TYPE uw_sel_row AS OBJECT ( part_num NUMBER, part_name VARCHAR2(15)); /

CREATE OR REPLACE TYPE uw_sel_tab AS TABLE OF uw_sel_row; /

CREATE OR REPLACE PROCEDURE bulk_test IS

uw_selection uw_sel_tab;

BEGIN
   SELECT uw_sel_row(part_num, part_name)    BULK COLLECT INTO uw_selection
   FROM parent;

• your code here END bulk_test; /

The full demo, and others, can be found at: http://www.psoug.org
click on Morgan's Library
click on Bulk Binding / Bulk Collection

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---