Select from Oracle Collection

actually the collection could be populated from within a table function which is pl/sql and used from sql

for example...

CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);

            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;

        RETURN;
    END str2tbl;

select * from table(str2tbl('a,b,c'))

Select allOpen in new window

My goal was to have everything in a package. This shows vcarray created as a sql line item. Also I am using a composit collection.  Notice in my code the commeted out statements I need to perform in sql. I want to encaplulate everything in a package so there are no outside actions to perform.

Here is my code.

create or replace procedure sample as

/*
These statements were executed in SQL
prior to compiling this procecure.

create or replace type r_rec as object
(
    employee_id   number,
    first_name   varchar2(20),
    job_id     varchar2(10)
);

create or replace type t_rec as table of r_rec;

*/

cnt NUMBER;

dat_rec t_rec;

-- Declare a collection (array) of 1 column.
-- Index is the subscript.
-- Binary integer is the numeric data type.
TYPE doc_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_doc_id doc_id;

BEGIN
        
    dat_rec := t_rec();
    dbms_output.PUT_LINE('Here 1 dat_rec : ' || dat_rec.count);
    
    select employee_id BULK COLLECT INTO v_doc_id from hr.employees;
    dbms_output.PUT_LINE('Here 2 v_doc : ' || v_doc_id.count);
    
    FOR i IN v_doc_id.FIRST..v_doc_id.LAST LOOP
        dbms_output.PUT_LINE('Here 3 i = : ' || i);
        dat_rec.extend;
        dat_rec(i) := r_rec(null,null,null);
        dbms_output.PUT_LINE('Here 4 dat_rec = : ' || dat_rec.count);
        select 
                employee_id, 
                first_name, 
                job_id 
        into
                dat_rec(i).employee_id,
                dat_rec(i).first_name,
                dat_rec(i).job_id      
        from 
                hr.employees
        where 
                employee_id = v_doc_id(i);
                
        select count(*) into cnt from table(dat_rec);
        dbms_output.PUT_LINE('Here 5 : ' || cnt);
        dbms_output.PUT_LINE('Here 6 : ' || dat_rec.Count);
    END LOOP;
end;
END;

Select allOpen in new window

Hi sdstuber,

There may be a misunderstanding of my question or I didn't phrase properly. Its not so much the population of the collection but the creation. I want to create the collection in a package so its more portable and only one thing needs to be executed.

Thanks much for your help,
Bob

a restriction with using these implicit types is the records aren't really "objects"

so you can't invoke a constructor for them.

for example:

    testtype.r_rec(employee_id, first_name, job_id)

is illegal because there is no t_rec function or t_rec object,  which is why I manually built the r_rec record