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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.