0

I make DB of game and I need to make table(vector) of IDs where type='x'. ID is number.

create or replace procedure add_items
is
  TYPE ID_TAB IS TABLE OF NUMBER(20);
  id_eq_weapon ID_TAB;
begin
  select id into id_eq_weapon from EQ where TYPE='weapon'; --ERROR HERE
  for [..]

Can somebody help?

APC
  • 144,005
  • 19
  • 170
  • 281
Xalion
  • 623
  • 9
  • 27
  • 1
    Errors usually come with codes or messages explaining exactly what's wrong. You should practice reading those if you want to learn. (They also make it much easier for others to help you.) – Disillusioned May 28 '17 at 16:24
  • @CraigYoung Error(16,18): PLS-00642: local collection types not allowed in SQL statements. it doesnt help at all.. – Xalion May 28 '17 at 16:26
  • 1
    Of course it helps. Please add it to the question. – William Robertson May 28 '17 at 17:04

1 Answers1

1

You are populating a collection but your code uses the syntax for populating a scalar variable. Try this instead:

select id 
bulk collect into id_eq_weapon 
from EQ where TYPE='weapon';

"how to check values after this select? dbms_output.put_line(id_eq_weapon); didnt show anything"

It's a collection: you need to reference the entries with an index, e.g.

for idx in id_eq_weapon.first().. id_eq_weapon.last()
loop
    dbms_output.put_line(id_eq_weapon(idx));
end loop;

It seems likely you would benefit from reading the Oracle documentation on PL/SQL collections. Find out more.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thank You! It works, but how to check values after this select? `dbms_output.put_line(id_eq_weapon);` didnt show anything – Xalion May 28 '17 at 16:52
  • `id_eq_weapon` is a collection. `dbms_output.put_line` takes a string. You need to loop through one element at a time, or use the debugger. – William Robertson May 28 '17 at 16:56
  • I havent hear about collection. Thanks. – Xalion May 28 '17 at 16:59
  • @Xalion At a minimum, you heard about "collection types" in the error message you chose to ignore. So if you have a better attitude towards reading and making sure you understand your error messages, you'll find learning much easier. – Disillusioned May 31 '17 at 02:25