Oracle Apex中PL/SQL动态SQL报ORA-00905缺失关键字求助

Problem #

I'm hitting an ORA-00905: missing keyword error when running PL/SQL code in my Oracle Apex app. The logic is meant to:

1. Fetch a column name from the SUB_CATEGORY table into V_COLUMN
2. Build a dynamic SQL query to pull the value of that column from PLANNED_EFFORTS into V_VALUE and return it

I’ve confirmed 1 and 84 are valid values in the tables, and even swapping them for bind variables didn’t fix the error. The static version of the query runs perfectly, so I’m sure the issue is with how I’m constructing the dynamic SQL. Here’s my code:

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; 

Solution #

The root cause of your error is a misplacement of the INTO clause in your dynamic SQL setup. Let’s break down what’s wrong and how to fix it:

• Wrong INTO position: You included INTO V_VALUE inside the dynamic SQL string, but in Oracle, the INTO clause for fetching single-row results from dynamic SQL belongs to the EXECUTE IMMEDIATE statement—not the raw SQL query. When you embed it in the string, Oracle treats it as part of the SELECT statement, which isn’t valid for a standalone query (hence the "missing keyword" error).
• Bonus: Use bind variables for safety: Hardcoding 84 in the dynamic SQL opens you up to SQL injection risks and hurts performance by preventing plan reuse. Swap it for a bind variable instead.

Here’s the corrected code:

DECLARE 
  V_COLUMN VARCHAR2(50) := 'UNKNOWN'; 
  V_VALUE VARCHAR2(50) := '0'; -- Fixed initialization to match VARCHAR2 type
  V_SQL VARCHAR2(500); 
BEGIN 
  SELECT SUB_CAT_ABBREV INTO V_COLUMN FROM SUB_CATEGORY WHERE SUB_CATEGORY_ID = 1; 
  -- Remove INTO V_VALUE from the SQL string, add bind variable for PLAN_ID
  V_SQL := 'SELECT ' || V_COLUMN || ' FROM PLANNED_EFFORTS WHERE PLAN_ID = :p_plan_id'; 
  -- Move INTO V_VALUE to EXECUTE IMMEDIATE, pass the bind variable value with USING
  EXECUTE IMMEDIATE V_SQL INTO V_VALUE USING 84; 
  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; 

Key Notes #

• The EXECUTE IMMEDIATE ... INTO ... syntax is the correct way to retrieve single-row results from dynamic SQL in PL/SQL.
• Bind variables (:p_plan_id) keep your code secure and let Oracle reuse execution plans for better performance.
• I also fixed V_VALUE’s initialization to '0' (a string) since it’s declared as VARCHAR2(50)—initializing it to a numeric 0 would cause unnecessary implicit type conversion, which is best avoided.

内容的提问来源于stack exchange，提问作者philscic