ORA-06502错误排查：Oracle存储过程生成JSON CLOB缓冲区过小

Hey there, let's tackle that ORA-06502 error you're hitting with your Toad for Oracle PL/SQL code. This error almost always means a variable can't hold the data you're trying to assign to it (buffer too small) or there's a mismatch in data types/string handling. Let's break down the root causes in your code and fix them step by step:

1. Undefined Variable Lengths (Major Culprit) #

Looking at your variable definitions:

v_city VARCHAR2;
v_country VARCHAR2;

Oracle defaults to a VARCHAR2 length of 1 when no length is specified. But your in_emp_type defines city VARCHAR2(40) and country VARCHAR2(50), and you're fetching values from tables into these variables. When the fetched value is longer than 1 character, you'll hit the "character string buffer too small" error immediately.

Fix: Define the variables with matching lengths:

v_city VARCHAR2(40);
v_country VARCHAR2(50);

2. Uninitialized CLOB Variable #

In your convert_result_json function, you declare v_clob CLOB but never initialize it. PL/SQL requires CLOBs to be initialized before you can concatenate values to them.

Fix: Initialize the CLOB with an empty value:

FUNCTION convert_result_json (in_result out_emp_tab_type) RETURN CLOB IS
    v_clob CLOB := EMPTY_CLOB(); -- Initialize empty CLOB
BEGIN
    -- Rest of your code
END;

3. Broken Manual JSON String Concatenation #

Your code for building v_json_input has syntax errors and unsafe string handling:

• You use " (HTML entities) instead of escaped single quotes or double quotes in PL/SQL.
• The concatenation logic is broken (mismatched quotes, incorrect || placement).
• If any input value contains a single quote, the JSON will be invalid, and you might hit truncation errors.

Better Fix: Use Oracle's native JSON_OBJECT function to safely generate valid JSON instead of manual concatenation:

v_json_input := JSON_OBJECT(
    'EmployeeDetails' VALUE JSON_OBJECT(
        'EmployeeID' VALUE emp_id,
        'EmployeeFirstName' VALUE emp_fname,
        'EmployeeLastName' VALUE emp_lname,
        'EmployeeCity' VALUE v_city,
        'EmployeeCountry' VALUE v_country
    )
) CLOB;

This handles escaping automatically and ensures valid JSON structure.

4. Cursor Parameter Name Collision #

Your get_emp_addr cursor has parameters that match column names (emp_id, city):

CURSOR get_emp_addr (emp_id NUMBER, city VARCHAR2) IS
    select addr_1, addr_2 from emp_addr 
    where emp_id = emp_id and city = city;

Oracle can't distinguish between the parameter and the column name, so this condition will always evaluate to TRUE (matching all rows in emp_addr). Fetching a large number of rows can lead to excessive data being loaded into query_tab, which might trigger buffer overflow errors when building the JSON CLOB.

Fix: Rename parameters to avoid collisions:

CURSOR get_emp_addr (p_emp_id NUMBER, p_city VARCHAR2) IS
    select addr_1, addr_2 from emp_addr 
    where emp_id = p_emp_id and city = p_city;

Then call the cursor with the correct variable:

open get_emp_addr (emp_id, v_city); -- Use v_city instead of undefined 'city' variable

5. Manual JSON Output Risks #

Your convert_result_json function manually builds JSON with loops and concatenation, which is error-prone:

• You don't handle trailing commas (which will break JSON validity).
• For large result sets, repeated concatenation can cause memory issues and buffer overflows.

Fix: Use Oracle's JSON_ARRAYAGG and JSON_OBJECT to generate the result JSON safely:

FUNCTION convert_result_json (in_result out_emp_tab_type) RETURN CLOB IS
    v_clob CLOB;
BEGIN
    SELECT JSON_OBJECT(
        'customerResults' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'addr1' VALUE emp_addr_1,
                'addr2' VALUE emp_addr_2
            )
        )
    ) INTO v_clob
    FROM TABLE(in_result);
    
    RETURN v_clob;
END;

This leverages Oracle's optimized JSON handling to build valid CLOB output without manual string manipulation.

6. Minor Syntax Fixes in Type Definitions #

Your record type definitions have small syntax errors that could lead to unexpected behavior:

• in_emp_type has country(50) missing the VARCHAR2 type:

  TYPE in_emp_type IS RECORD ( 
      emp_id NUMBER, 
      emp_fname VARCHAR2(50), 
      emp_lname VARCHAR2(50), 
      city VARCHAR2(40), 
      country VARCHAR2(50) -- Fixed: added VARCHAR2
  );
  

• out_emp_type is missing a closing parenthesis:

  TYPE out_emp_type IS RECORD (
      emp_addr_1 VARCHAR2(100), 
      emp_addr_2 VARCHAR2(100)
  ); -- Fixed: added closing )
  

After applying these fixes, your code should resolve the ORA-06502 error and generate valid JSON output for your REST service.

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