plsqlrecordvarray

display a record which contains a VARRAY column


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;

Solution

  • 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.)