Select from Oracle Collection

I understand that to create a collection that allows sql selects on it that it must be created outside of a pl/sql block. Is there a way to create this collection in a package and still allow select from SQL?
bobcaputoAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
yes, you can, sort of..


CREATE OR REPLACE PACKAGE testtype
IS
    TYPE r_rec IS RECORD
    (
        employee_id   NUMBER,
        first_name    VARCHAR2(20),
        job_id        VARCHAR2(10)
    );

    TYPE t_rec IS TABLE OF r_rec;

    FUNCTION testproc
        RETURN t_rec
        PIPELINED;
END;

CREATE OR REPLACE PACKAGE BODY testtype
IS
    FUNCTION testproc
        RETURN t_rec
        PIPELINED
    IS
        r   r_rec;
    BEGIN
        FOR x IN (SELECT employee_id, first_name, job_id FROM hr.employees)
        LOOP
            r.employee_id  := x.employee_id;
            r.first_name   := x.first_name;
            r.job_id       := x.job_id;
            PIPE ROW (r);
        END LOOP;

        RETURN;
    END;
END;

Open in new window



depending on your version of oracle you may see system generated types in USER_TYPES
they will have names like SYS_PLSQL_xxxxxx_xx

in 11gR2 the system generated types don't get created (or if they do, they aren't visible in that view)

in either case you use the types in sql statements

SELECT * FROM TABLE(testtype.testproc)

Open in new window

0
 
sdstuberCommented:
actually the collection could be populated from within a table function which is pl/sql and used from sql
0
 
sdstuberCommented:
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'))

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
bobcaputoAuthor Commented:
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;

Open in new window

0
 
bobcaputoAuthor Commented:
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
0
 
sdstuberCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.