Select SQL Against Oracle Collections Table Type

I have a need to run a SELECT against a collection table type. No matter what I do I can't seem to get it to work. It's more of a learning/work task. Could someone explain to me where I am making a mistake, and help wtih the rewrite of the code to explain how to do selects against the collection?
SET SERVEROUTPUT ON 
Declare
	TYPE SLFeeds_REC IS RECORD(
		SL_LocalAccNo 	bank.local_acc_no%type,
		SL_SubAccNo 	  message_feed.sub_acc_no%type,
		SL_MLNV 		    message_feed.mlnv%type,
		SL_Comment		  varchar2(4000));
    TYPE SLFeeds_TBL IS TABLE OF SLFeeds_REC INDEX BY binary_integer;
    SLFeeds			    SLFeeds_TBL := SLFeeds_REC;
    TEST1 	        bank.local_acc_no%type;
Begin
  SLFeeds := SLFeeds;
	SLFeeds(1).SL_LocalAccNo := 'Test1';
	SLFeeds(1).SL_SubAccNo := 'SubAcc1';
	SLFeeds(1).SL_MLNV := 9000;
	SLFeeds(1).SL_Comment := '100';
	SLFeeds(2).SL_LocalAccNo := 'Test2';
	SLFeeds(2).SL_SubAccNo := 'SubAcc2';
	SLFeeds(2).SL_MLNV := 9000;
	SLFeeds(2).SL_Comment := '100';
	SLFeeds(3).SL_LocalAccNo := 'Test3';
	SLFeeds(3).SL_SubAccNo := 'SubAcc3';
	SLFeeds(3).SL_MLNV := 9000;
	SLFeeds(3).SL_Comment := '100';

  SELECT SL_LocalAccNo INTO TEST1 FROM table(cast(SLFeeds as SLFeeds)) sf WHERE sf.SL_SubAccNo = 'SubAcc3' ;

  DBMS_OUTPUT.PUT_LINE(TEST1);
End;

Open in new window

ramanjitsingh04Development ManagerAsked:
Who is Participating?
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:
>>> TABLE OF SLFeeds_REC INDEX BY binary_integer


this is what is known formally as an "associative array",  previously "index-by table" and prior to that,  "pl/sql table"

what you need is a nested table type.

0
ramanjitsingh04Development ManagerAuthor Commented:
Well could you show me how to correct my code to make it work?
Because I've tried variations to the table type and configuration, but I'm unable to get it sorted.
I need to be able to store multiple columns within the table to access thru my iteriation.
0
sdstuberCommented:
First you need to create the record and table object types

CREATE OR REPLACE TYPE slfeeds_obj AS OBJECT
(
    sl_localaccno VARCHAR2(10),                                        -- adjust this size as needed
    sl_subaccno VARCHAR2(10),                                          -- adjust this size as needed
    sl_mlnv NUMBER,
    sl_comment VARCHAR2(4000)
);

CREATE OR REPLACE TYPE slfeeds_tbl AS TABLE OF slfeeds_obj;


then you can write you block like this...

DECLARE
    slfeeds   slfeeds_tbl := slfeeds_tbl(NULL);  -- initialze table, this creates one null element
    test1     VARCHAR2(10); -- this can be delared with  table.column%type, but I don't have your table
BEGIN
    slfeeds(1)  := slfeeds_obj('Test1', 'SubAcc1', 9000, '100');
    slfeeds.EXTEND;  -- allocate space for next element in table
    slfeeds(2)  := slfeeds_obj('Test2', 'SubAcc2', 9000, '100');  
    slfeeds.EXTEND;  -- allocate space for next element in table
    slfeeds(3)  := slfeeds_obj('Test3', 'SubAcc3', 9000, '100');

    SELECT sl_localaccno
      INTO test1
      FROM TABLE(slfeeds) sf
     WHERE sf.sl_subaccno = 'SubAcc3';

    DBMS_OUTPUT.put_line(test1);
END;

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ramanjitsingh04Development ManagerAuthor Commented:
I don't have permissions to create type's.
Error report:
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"

Anyway to work around it?

I tried to intergrate the object as part of the declare statement and that didn't work either.

DECLARE

    TYPE slfeeds_obj AS OBJECT
    (
      sl_localaccno VARCHAR2(10),                                        -- adjust this size as needed
      sl_subaccno VARCHAR2(10),                                          -- adjust this size as needed
      sl_mlnv NUMBER,
      sl_comment VARCHAR2(4000)
    );
    
    TYPE slfeeds_tbl IS TABLE OF slfeeds_obj;
    slfeeds   slfeeds_tbl := slfeeds_tbl(NULL);  -- initialze table, this creates one null element
    test1     VARCHAR2(10); -- this can be delared with  table.column%type, but I don't have your table
BEGIN
    slfeeds(1)  := slfeeds_obj('Test1', 'SubAcc1', 9000, '100');
    slfeeds.EXTEND;  -- allocate space for next element in table
    slfeeds(2)  := slfeeds_obj('Test2', 'SubAcc2', 9000, '100');  
    slfeeds.EXTEND;  -- allocate space for next element in table
    slfeeds(3)  := slfeeds_obj('Test3', 'SubAcc3', 9000, '100');

    SELECT sl_localaccno
      INTO test1
      FROM TABLE(slfeeds) sf
     WHERE sf.sl_subaccno = 'SubAcc3';

    DBMS_OUTPUT.put_line(test1);
END;

Open in new window

0
sdstuberCommented:
to use nested tables in sql, you must create the types.  If you can't do it yourself, have someone with privileges do it or give you privileges.
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:
other option is to not use sql.  create your associative arrays and iterate through them.  If you'll be doing lookups by strings
you may want to consider indexing varchar2,  and doing something like this...



SLFeeds('SubAcc1').SL_LocalAccNo := 'Test1';
SLFeeds('SubAcc1').SL_MLNV := 9000;
SLFeeds('SubAcc1').SL_Comment := '100';


test1 := slfeeds('SubAcc1').sl_localaccno;
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.

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.