2

I, Oracle newbie, am trying to select the primary key ids with a complicated query into an array structure to work with this afterwards. The basic workflow is like:

1. Select many ids (of type long) found by a long and complicated query (if I would know if and how this is possible, I would separate this into a function-like subquery of its own)
2. Store the ids in an array like structure
3. Select the rows with those ids
4. Check for duplicates (compare certain fields for equality) 
5. exclude some (i.e. duplicates with a later DATE field)

I read a lot of advice on PL / SQL but haven't found the right concept. The oracle documentation states that if I need an array, I should use VARRAY. My best approach so far is

declare
TYPE my_ids IS VARRAY(100000) OF LONG
begin
SELECT id INTO my_ids FROM myTable WHERE mycondition=true
-- Work with the ids here, i.e. loop through them
dbms_output.put_line('Hello World!');
END;

But I get an error: "Not suitable for left side". Additionally, I don't want to declare the size of the array at the top. So I think this approach is wrong.

Could anyone show me a better one? It doesn't have to be complete code, just "use this data structure, these SQL-structures, that loop and you'll get what you need". I think I could figure it out once I know which direction I should take.

Thanks in advance!

poshtad
  • 25
  • 1
  • 6
  • You could use an associative array to hold the ID's: http://stackoverflow.com/questions/5183330/pl-sql-selecting-from-a-table-into-an-assoc-array?rq=1 – Ollie Oct 01 '15 at 13:46

1 Answers1

1

My_ids in your example is a type, not a variable.
You cannot store data into the type, you can store data only to some variable of this type.

Try this code:

declare
  TYPE my_ids_type IS VARRAY(100000) OF LONG; /* type declaration */
  my_ids my_ids_type; /* variable declaration */
begin
  SELECT my_id BULK COLLECT INTO my_ids FROM myTable;
  -- Work with the ids here, i.e. loop through them
  dbms_output.put_line('Hello World!');
END;

Read this article: http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
to learn basics how to bulk collect data in PL/SQL.

krokodilko
  • 35,300
  • 7
  • 55
  • 79