PL/SQL ORA-00905: missing keyword for Set Value

Question

I searched for this error, but since it's very vague, I could not find something similar to understand where is the problem. This code is actually for an Oracle Apex application. I actually have bind variables instead of numbers 1 and 84 (which I confirm are correct values within my tables), but still got same error.

After declaring the variables, it selects a string that will be the name of a column within another table and put it V_COLUMN.

Then i dynamically build a query to get the value of this column and put it into V_VALUE and finally I return a value (which is then shown in a form textfield). Unfortunately it returns the ORA 00905.

When I tried to run the sql commands separately using known values, it runs. So I think there must be some syntax problem somewhere in the dynamic sql. Thanks for any assistance.

DECLARE
    V_COLUMN VARCHAR2(50) := 'UNKNOWN';
    V_VALUE VARCHAR2(50) := 0;
    V_SQL VARCHAR2(500);
BEGIN
    SELECT SUB_CAT_ABBREV INTO V_COLUMN FROM SUB_CATEGORY WHERE SUB_CATEGORY_ID = 1;
    V_SQL := 'SELECT ' || V_COLUMN || ' INTO V_VALUE FROM PLANNED_EFFORTS WHERE PLAN_ID = 84';
    EXECUTE IMMEDIATE V_SQL;
    RETURN V_VALUE;   
EXCEPTION
  WHEN no_data_found THEN
    RETURN 'No Data Found Error'; 
  WHEN too_many_rows then
    RETURN 'Too many rows';
  WHEN OTHERS THEN
    RETURN 'Other Error';
END;

Answer 1

Just get rid off your INTO clause from your dynamic SQL statement:

V_SQL := 'SELECT ' || V_COLUMN || ' FROM PLANNED_EFFORTS WHERE PLAN_ID = 84';
EXECUTE IMMEDIATE V_SQL
INTO V_VALUE 

Moreover, if you expect more then one value you can use BULK COLLECT INTO and return values into some collection type:

 V_SQL := 'SELECT ' || V_COLUMN || ' FROM PLANNED_EFFORTS WHERE PLAN_ID = 84;
    EXECUTE IMMEDIATE V_SQL
    BULK COLLECT INTO V_VALUES

where V_VALUES can be declared as:

TYPE TABLE_OF_VARCHAR2 IS TABLE OF VARCHAR2(50);
V_VALUES TABLE_OF_VARCHAR2;

and accessed in the loop as follows:

for i in V_VALUES.FIRST .. V_VALUES.LAST LOOP
  -- V_VALUES(i)
END LOOP;