display a record which contains a VARRAY column

Question

I have a function which returns a RECORD. One of the record's columns is VARRAY. Can someone hint me how to display the RECORD, please? (my problem is related to the VARRAY column.

create or replace TYPE phone_list_typ AS VARRAY(5) OF VARCHAR2(25);

CREATE TABLE "CUSTOMERS" 
    ("CUSTOMER_ID" NUMBER(6,0), 
     "CUST_FIRST_NAME" VARCHAR2(20 BYTE)
     "PHONE_NUMBERS" "OE"."PHONE_LIST_TYP" , 
     "CREDIT_LIMIT" NUMBER(9,2), 
     "CUST_EMAIL" VARCHAR2(40 BYTE)); 

TYPE r_cust_det IS RECORD( CUSTOMER_ID      customers.CUSTOMER_ID%TYPE
                         , CUST_FIRST_NAME  customers.CUST_FIRST_NAME%TYPE
                         , PHONE_NUMBERS    customers.PHONE_NUMBERS%TYPE
                         , CREDIT_LIMIT     customers.CREDIT_LIMIT%TYPE
                         , CUST_EMAIL       customers.CUST_EMAIL%TYPE);

CREATE OR REPLACE FUNCTION show_customer_details (n_customer_id customers.customer_id%TYPE) RETURN r_cust_det
IS
    v_return r_cust_det;
BEGIN
    SELECT CUSTOMER_ID
         , CUST_FIRST_NAME
         , PHONE_NUMBERS
         , CREDIT_LIMIT
         , CUST_EMAIL
    INTO v_return
    FROM CUSTOMERS
    WHERE CUSTOMER_ID = n_customer_id;
RETURN v_return;
END show_customer_details;

score 2 · Accepted Answer · answered Oct 31 '16 at 10:10

2

This may depend on how you want it to look and what the display medium is (text file, interactive web page etc), but one way might be to list the phone numbers as a comma-separated list.

select customer_id, cust_first_name, credit_limit, cust_email
     , listagg(p.column_value,', ') within group (order by p.column_value) as phone_numbers
from   customers c cross join table(c.phone_numbers) p
group by customer_id, cust_first_name, credit_limit, cust_email
order by customer_id;

I'm not sure what you expect out of your show_customer_details function, though.

(btw it's not a good idea to enclose identifiers in double-quotes unless you absolutely have to.)

answered Oct 31 '16 at 10:10

William Robertson

15,273
4
38
44

I want the result to be displayed "one information per line". Your solution matches perfectly in case of enumeration. For a table solution, I found this: – mikcutu Oct 31 '16 at 10:41
An alternative might be a control break report. How you build that will depend on the display media. – William Robertson Oct 31 '16 at 10:47
the function is used for trainnig purpose – mikcutu Oct 31 '16 at 12:17

score 0 · Answer 2 · answered Oct 31 '16 at 10:43

0

CREATE OR REPLACE FUNCTION show_customer_details (n_customer_id customers.customer_id%TYPE) RETURN t_cust_det PIPELINED
    IS
        v_return t_cust_det;
    BEGIN
        SELECT t1.CUSTOMER_ID
             , t1.CUST_FIRST_NAME
             , t2.*
             , t1.CREDIT_LIMIT
             , t1.CUST_EMAIL
        BULK COLLECT INTO v_return
        FROM CUSTOMERS t1, table(t1.phone_numbers) t2
        WHERE t1.CUSTOMER_ID = n_customer_id
        AND column_value is not null;

        FOR i IN 1 .. v_return.count
        LOOP
            PIPE ROW (v_return(i));
        END LOOP;

    END show_customer_details;

the function call is:

select * from table(SHOW_DETAILS.SHOW_CUSTOMER_DETAILS(101));

answered Oct 31 '16 at 10:43

mikcutu

1,013
2
17
34

That should work. However I don't think it needs to be a pipelined function if you fetch all the rows at once (and presumably there aren't that many rows per customer ID). I'd just have it return the array. – William Robertson Oct 31 '16 at 10:50
PIPELINED was used as there was not defined the table of record type in SQL. – mikcutu Oct 31 '16 at 12:12
Surely `t_cust_det` is the table type? Just have the function return `v_return` with no pipeline. – William Robertson Oct 31 '16 at 12:28
Yes, it is table of record. The difference comes from the way data is collected. In your case, all phone numbers are selected, "concatenated" in a single row, delimited by comma. In my case, each phone number is selected on a separated row. If there are 3 phone numbers, the select statement will return 3 rows (each phone number on its own row); the rest of the values being repeated. – mikcutu Oct 31 '16 at 12:31

score 0 · Answer 3 · answered Oct 31 '16 at 15:17

Another solution I found, without using PIPELINED is:

Define a object type

create or replace type customers_typ
is object 
    ( CUSTOMER_ID      number(6)
    , CUST_FIRST_NAME  varchar2(20)
    , PHONE_NUMBERS    varchar2(25) --phone_list_typ
    , CREDIT_LIMIT     number(9, 2)
    , CUST_EMAIL       varchar2(40)
    );

Define a new type, table of previously defined object.

create or replace type t_customers_typ is table of customers_typ;

The function become

CREATE OR REPLACE FUNCTION show_customer_details (n_customer_id customers.customer_id%TYPE) RETURN t_customers_typ
IS
    v_return t_customers_typ;
BEGIN
    SELECT customers_typ(t1.CUSTOMER_ID
         , t1.CUST_FIRST_NAME
         , t2.column_value
         , t1.CREDIT_LIMIT
         , t1.CUST_EMAIL)
    BULK COLLECT INTO v_return
    FROM CUSTOMERS t1, table(t1.phone_numbers) t2
    WHERE t1.CUSTOMER_ID = n_customer_id
    AND t2.column_value is not null;

    return v_return;

END show_customer_details;

The function is called as the same:

select * from table(SHOW_DETAILS.SHOW_CUSTOMER_DETAILS(101));

display a record which contains a VARRAY column

3 Answers3