Oracle 12c执行原生SQL遇ORA-01422错误的解决求助

Hey there! Let's tackle that ORA-01422 error you're hitting head-on. The root cause is straightforward: your SELECT query is returning more than one row, but you’re using EXECUTE IMMEDIATE ... INTO—which expects exactly one row to populate the v_create_ts variable.

Let's Break Down Your Code Issue #

This snippet is where the problem lies:

sqltest := 'select create_ts from WORKFLOW_CODES where id =' || p_id;
execute immediate (sqltest) into v_create_ts;

When you run this, the query against WORKFLOW_CODES is pulling multiple rows for the given p_id, but INTO v_create_ts can only hold a single value. Hence the "exact fetch returns more than requested number of rows" error.

Also, a critical side note: concatenating p_id directly into the SQL string exposes you to SQL injection risks—we’ll fix that with bind variables in the solutions below.

Solutions to Resolve the Error #

1. Ensure the Query Returns Exactly One Row

If you expect id to be a unique identifier (like a primary key), first check your data—there might be duplicate id values in WORKFLOW_CODES. If you just need any one of the rows (and don’t care which), you can limit the result set:

DECLARE
  sqltest VARCHAR2(200);
  v_create_ts DATE;
  p_id NUMBER; -- Your input parameter
  p_recordset SYS_REFCURSOR;
BEGIN
  -- Use bind variables to avoid SQL injection and improve performance
  sqltest := 'select create_ts from WORKFLOW_CODES where id = :id AND ROWNUM = 1';
  EXECUTE IMMEDIATE sqltest INTO v_create_ts USING p_id;

  OPEN p_recordset FOR SELECT v_create_ts FROM dual;
  DBMS_SQL.RETURN_RESULT(p_recordset);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records found for ID: ' || p_id);
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Multiple records exist for ID: ' || p_id);
END;
/

We added exception handling here to gracefully handle cases where no rows are found, or multiple rows still slip through (though ROWNUM=1 should prevent the ORA-01422 error).

2. Handle Multiple Rows (If Expected)

If multiple rows for the same id are valid in your use case, use a collection to fetch all results at once:

DECLARE
  -- Define a collection type to hold multiple date values
  TYPE date_collection IS TABLE OF DATE;
  v_create_ts_list date_collection;
  p_id NUMBER; -- Your input parameter
  p_recordset SYS_REFCURSOR;
BEGIN
  -- Bulk collect all matching rows into the collection
  EXECUTE IMMEDIATE 'select create_ts from WORKFLOW_CODES where id = :id'
    BULK COLLECT INTO v_create_ts_list USING p_id;

  -- Return all collected dates as a structured result set
  OPEN p_recordset FOR
    SELECT column_value AS create_ts FROM TABLE(v_create_ts_list);
  DBMS_SQL.RETURN_RESULT(p_recordset);

  -- Optional: Iterate over the collection if you need to process each row individually
  FOR i IN v_create_ts_list.FIRST..v_create_ts_list.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Create timestamp: ' || v_create_ts_list(i));
  END LOOP;
END;
/

This approach uses BULK COLLECT to fetch all rows into a collection, then you can either return them as a result set or process each entry one by one.

Key Takeaways #

• Avoid SQL injection: Always use bind variables (:id) instead of string concatenation for input parameters.
• Match fetch method to expected rows: Use INTO for single rows, BULK COLLECT INTO for multiple rows.
• Add exception handling: Catch NO_DATA_FOUND and TOO_MANY_ROWS to make your code more resilient to edge cases.

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