CODEWITHSUNDEEP
oracle-databaseoracle9i
wundidajah
wundidajah

Reputation: 177

What is the purpose of "RETURN AS VALUE" in NESTED TABLES (Oracle 9i)

Is there a specific case, when i should use RETURN AS VALUE? Normally i use only NESTED TABLE xxx STORE AS xxx

For example:

    CREATE OR REPLACE TYPE address_t AS OBJECT (
    ADDID      NUMBER(10,0),
    STREET     VARCHAR2(40),
    ZIP        VARCHAR2(5),
    CITY       VARCHAR2(40)
)
/

CREATE OR REPLACE TYPE addresses_nt AS TABLE OF address_t
/

CREATE OR REPLACE TYPE invoicepos_t AS OBJECT (
    ARTID      NUMBER(10,0),
    AMOUNT     NUMBER(10,0)
)
/

CREATE OR REPLACE TYPE invoicepos_nt AS TABLE OF invoicepos_t
/

CREATE OR REPLACE TYPE customer_t AS OBJECT (
    CUSID      NUMBER(10,0),
    FIRSTNAME  VARCHAR2(30),
    LASTNAME   VARCHAR2(30),
    ADDRESSES  addresses_nt
)
/

CREATE OR REPLACE TYPE invoice_t AS OBJECT (
    INVOICEID  NUMBER(10,0),
    CUSTOMER   REF customer_t,
    ADDID      NUMBER(10,0),
    POSITIONS  invoicepos_nt
)
/

CREATE TABLE customer OF customer_t
    NESTED TABLE ADDRESSES STORE AS all_adresses RETURN AS VALUE    
/

CREATE TABLE invoices OF invoice_t
    NESTED TABLE POSITIONS STORE AS all_invoicepos RETURN AS VALUE
/

Upvotes: 1

Views: 421

Answers (1)

Jon Heller
Jon Heller

Reputation: 36832

As far as I can tell, the only difference is that LOCATORs are a bit faster than VALUEs. But that doesn't make sense and I'm hoping somebody will prove me wrong; there's almost never a "fast=true" switch.

According to the SQL Language Reference:

RETURN [AS] Specify what Oracle Database returns as the result of a query.

    VALUE returns a copy of the nested table itself.

    LOCATOR returns a collection locator to the copy of the nested table.

    The locator is scoped to the session and cannot be used across sessions. Unlike a LOB locator, the collection locator cannot be used to modify the collection instance.

This implies that LOCATORs are read-only. But on 11gR2 a LOCATOR can still be modified.

The Object Relational Developer's Guide also discusses LOCATORs, but does not mention any downsides to using them.

Sample Schema

CREATE OR REPLACE TYPE invoicepos_t AS OBJECT (
    ARTID      NUMBER(10,0),
    AMOUNT     NUMBER(10,0)
)
/

CREATE OR REPLACE TYPE invoicepos_nt AS TABLE OF invoicepos_t
/

create table invoices_val
(
    INVOICEID  NUMBER,
    POSITIONS  invoicepos_nt
)
    NESTED TABLE POSITIONS STORE AS all_invoicepos_val RETURN AS VALUE
/

create table invoices_loc
(
    INVOICEID  NUMBER,
    POSITIONS  invoicepos_nt
)
    NESTED TABLE POSITIONS STORE AS all_invoicepos_loc RETURN AS locator
/

insert into invoices_val values(1, invoicepos_nt(invoicepos_t(1,1)));
insert into invoices_loc values(1, invoicepos_nt(invoicepos_t(1,1)));
insert into invoices_def values(1, invoicepos_nt(invoicepos_t(1,1)));
commit;

Compare performance and funcionality

--Value: 1.0 seconds
declare
    v_positions invoicepos_nt;
begin
    for i in 1 .. 10000 loop
        select positions
        into   v_positions
        from   invoices_val;
    end loop;

    v_positions.extend;
    v_positions(2) := invoicepos_t(3,3);
    update invoices_val set positions = v_positions;
end;
/

--Locator: 0.8 seconds
declare
    v_positions invoicepos_nt;
begin
    for i in 1 .. 10000 loop
        select positions
        into   v_positions
        from   invoices_loc;
    end loop;

    v_positions.extend;
    v_positions(2) := invoicepos_t(3,3);
    update invoices_loc set positions = v_positions;
end;
/

Upvotes: 1

Related Questions