We help IT Professionals succeed at work.

Select SQL Against Oracle Collections Table Type

ramanjitsingh04
on
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

Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

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

ramanjitsingh04Development Manager

Author

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.
Most Valuable Expert 2011
Top Expert 2012

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

ramanjitsingh04Development Manager

Author

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

Most Valuable Expert 2011
Top Expert 2012
Commented:
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.
Most Valuable Expert 2011
Top Expert 2012

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