Oracle 11g: How to dynamically select multiple columns into BULK COLLECT

Question

I have a query like the example below where I want to dynamically bulk collect 2 columns using a join into from a table that has 4 columns, but I keep getting the error that 'variable not in select list'. I believe this is because it is because the query has less columns than the table, but I don't know how to get around it.

DECLARE
    TYPE test_table_type IS TABLE OF TEST_PRODUCTS%ROWTYPE;
    BULK_COLLECTOR test_table_type;
BEGIN
    EXECUTE IMMEDIATE
    'SELECT tp.PRODUCT AS PRODUCT, td.ALT_DESCRIPTION AS DESCRIPTION
    FROM TEST_PRODUCTS tp
    JOIN ALT_DESCRIPTIONS td ON tp.ALT_DESCRIPTION_ID = td.ALT_DESCRIPTION_ID'
    BULK COLLECT INTO BULK_COLLECTOR;
END;

Here are examples of the tables:

CREATE TABLE TEST_PRODUCTS
(
    PRODUCT_ID NUMBER,
    PRODUCT VARCHAR2(50),
    DESCRIPTION VARCHAR2(255)
    ALT_DESCRIPTION_ID NUMBER,
    INVENTORY_CODE VARCHAR2(100)
);

CREATE TABLE ALT_DESCRIPTIONS
(
    ALT_DESCRIPTION_ID NUMBER,
    ALT_DESCRIPTION VARCHAR2(255)
);

You select only 2 values, and try to put them in a type with 5 properties. — GolezTrol, Mar 30 '17 at 20:12

score 3 · Accepted Answer · answered Mar 30 '17 at 20:39

You are selecting from two different tables and trying to put it into a rowtype of one table.

You might want to take a different approach - something like this:

DECLARE
TYPE test_record IS RECORD
(
  PRODUCT VARCHAR2(50),
  ALT_DESCRIPTION VARCHAR2(255)
);
type test_tab is table of test_record;
BULK_COLLECTOR test_tab;
BEGIN
   EXECUTE IMMEDIATE
   'SELECT tp.PRODUCT AS PRODUCT, td.ALT_DESCRIPTION AS DESCRIPTION
   FROM TEST_PRODUCTS tp
   JOIN ALT_DESCRIPTIONS td ON tp.ALT_DESCRIPTION_ID = td.ALT_DESCRIPTION_ID'
   BULK COLLECT INTO BULK_COLLECTOR;
END;

Oracle 11g: How to dynamically select multiple columns into BULK COLLECT

1 Answers1