1

EDITED

I have a problem with performing some PL/SQL code. I have real table a. I want to take only elements with range<=100. I make a collection inside my PL/SQL based on that table. Then I want to perform SELECT operation on that collection. But I got a problem with it.

Prepared table (this is all for example, it's not a real problem. I just would like to know how can I select from collection in PL/SQL code block).

CREATE TABLE a (amount NUMBER);
INSERT INTO a VALUES (50);
INSERT INTO a VALUES (100);
INSERT INTO a VALUES (200);

And then I got this block:

DECLARE
  TYPE aTable IS TABLE OF a%ROWTYPE;
  aActual aTable;
  temp NUMBER;
BEGIN
    SELECT * BULK COLLECT INTO aActual 
    FROM a WHERE amount<=100;

    SELECT SUM(amount) INTO temp FROM TABLE(aActual);
    DBMS_OUTPUT.PUT_LINE(temp);
END;

But I got eroor PLS-00642 and ORA-22905. What can I do? Why it doesn't work that way? I'm on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production version (according to SELECT * FROM V$VERSION;)

michalsol
  • 752
  • 8
  • 29
  • If you don't need the aActual table for anything else, don't use it. Just select the sum of the range from the Kocury table. – Dan Bracuk Jan 13 '17 at 17:51
  • @DanBracuk I know, but I do need this table. Here I only presented small amount of code to explain what problem I get. – michalsol Jan 13 '17 at 17:57

2 Answers2

4

You can't do it because aTable is not a database table. (Yes I know it's defined with table of but that doesn't define a table. One of those things.)

To ask SQL to treat a collection as a database table you would use the table() construction:

select sum(amount) into temp from table(aActual);

although this will fail in your example due to scoping issues and you'll get the self-explanatory

PLS-00642: local collection types not allowed in SQL statements

For it to work, you'd need a schema-level type i.e. one created with create type:

create or replace type xyz as object (a integer, b varchar2(3), c date);

create or replace type xyz_tt as table of xyz;

Now type xyz_tt is in effect published to SQL and it can be used in SQL table() expressions.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
  • Yes, you know what I mean :) But when I did it that way I got ORA-22905 error instead of PLS-00642. How can I fix it? – michalsol Jan 13 '17 at 18:21
  • btw, there's no such thing as an 'internal table' and `table of` in PL/SQL actually defines a collection. From a PL/SQL perspective Oracle could have used a more self-explanatory keyword like `array` (as in Ada) or `collection`, but they also needed the syntax to refer to [nested table columns](http://www.orafaq.com/wiki/NESTED_TABLE) in database tables. – William Robertson Jan 13 '17 at 18:33
  • Well, I actually got both messages in my error stack when I tested it with a local collection type. Add it to your question and I'll have a look. – William Robertson Jan 13 '17 at 18:43
  • Updated answer with an example of what I meant by 'created with `create type`'. – William Robertson Jan 15 '17 at 16:35
0

As WilliamRobertson showed, you can't use a PL/SQL collection in a SQL query. You can loop over the collection and add each amount to your temp variable, initialising it to zero first:

temp := 0;
for i in 1..aActual.count loop
  temp := temp + aActual(i).amount;
end loop;
DBMS_OUTPUT.PUT_LINE(temp);
Alex Poole
  • 183,384
  • 11
  • 179
  • 318