Oracle APEX select values into classic report rows

Question

I'm working on an Oracle database-driven web app using apex.There is a P2_ROWS field which contains a list of values selected from the data table, defined in the source sql query. There is a button on the report region, which allows users to sort the list of values in a certain order. A dynamic action is assigned to the button. When the button is clicked, a PL/SQL is executed with a order by query that should change the order in which the rows are displayed.

The source sql is defined as:

select 
"PRODUCT_ID",
"PRODUCT_NAME",
"PRODUCT_DESCRIPTION",
"PRICE",
"PRODUCT_LOCATION",
dbms_lob.getlength("THUMBNAIL") as "THUMBNAIL"
 from   "PRODUCTS" 
where 
(   
    instr(upper("PRODUCT_NAME"), upper(nvl(:P2_REPORT_SEARCH, "PRODUCT_NAME"))) > 0
)

The PL/SQL assigned to the button is:

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.PRODUCT_NAME, PRODUCTS.PRODUCT_DESCRIPTION, PRODUCTS.PRICE, PRODUCTS.PRODUCT_LOCATION, dbms_lob.getlength("THUMBNAIL") as "THUMBNAIL"
INTO :P2_ROWS
FROM PRODUCTS
INNER JOIN TEMP_DISTANCES ON TEMP_DISTANCES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
ORDER BY DISTANCE ASC;

When the button is clicked, the app returns error " ORA-00947: not enough values ORA-06550".

How would you solve this? Please feel free to comment and feel free to suggest any improvement ideas. Thanks.

Answer 1

As @Scott say you trying select many values into one scalar variable. You should define few variables or use concatenation.

SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.PRODUCT_NAME, PRODUCTS.PRODUCT_DESCRIPTION, PRODUCTS.PRICE, PRODUCTS.PRODUCT_LOCATION, dbms_lob.getlength("THUMBNAIL") as "THUMBNAIL"
  INTO :P2_PROID_ID, :P2_PROD_NAME, ...
  FROM PRODUCTS
       INNER JOIN TEMP_DISTANCES 
          ON TEMP_DISTANCES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
 ORDER BY DISTANCE ASC;

or

SELECT PRODUCTS.PRODUCT_ID || ', ' || PRODUCTS.PRODUCT_NAME || ', ' || PRODUCTS.PRODUCT_DESCRIPTION || ', ' || PRODUCTS.PRICE ...
  INTO :P2_ROWS
  FROM PRODUCTS
       INNER JOIN TEMP_DISTANCES 
          ON TEMP_DISTANCES.PRODUCT_ID = PRODUCTS.PRODUCT_ID
 ORDER BY DISTANCE ASC;