function - Save answer of a query in a variable PL/SQL

Question

I want to save the answer from the query into a variable, which I then go through and check the individual values.

FUNCTION F_capa(
v_carId car.carid%Type,
v_snumber box.snumber%Type)
  RETURN Boolean
IS
 
 type t_box is Table OF box%rowtype;
 v_possibleBox t_box := t_box();
 
BEGIN
 SELECT  b.carid, COUNT(p.trayID) AS amaunt,b.capacity ,b.date
 INTO  v_possibleBox
 FROM Box b
 left JOIN place p on b.carID = p.carID
 WHERE b.snumber = v_snumber
 GROUP BY b.carID, b.date, b.capacity ;
 
 --  LOOP IF(  v_carId = v_possibleBox(i).carID)  Something like that

 End;

But I always get this error.

ORA-00947: not enough values

I am relatively new to PLSQL and really doubt this site, so i would be grateful for any help.

Answer 1

The ORA-00947 is telling you that the values returned by your query and the v_possibleBox record don't match. The obvious problem is the amaunt column you create. The t_box type has the same structure as your table (in fact you don't really need it, it's just an alias for box%rowtype) and that table won't have a column amaunt.

In this case you might do better using a Cursor for loop, this has the following structure:

FOR record IN (select_statement)
LOOP
    process_record_statements;
END LOOP; 

record becomes a record with a type that matches the columns returned by your select statement, so inside the loop record.amaunt will work. You don't have to call the record record BTW so for myName IN (select statement) is fine.

If you want to use the original construct then you need to define t_box so that it has the same columns and types as returned by the SQL.