Oracle 11g存储过程语法问题求助：JSON_VALUE特性缺失致执行失败

Got it, I totally get the frustration—Oracle 11g doesn’t come with native JSON functions like JSON_VALUE (that arrived in 12c), so we need to work around this using regular expressions to extract values from your JSON data. Let’s walk through a practical example to fix your procedure.

Example of Your Original (Failing) Procedure #

First, let’s assume your original code looked something like this (using JSON_VALUE which 11g rejects):

CREATE OR REPLACE PROCEDURE process_json_records (p_input_json CLOB)
IS
    v_customer_id VARCHAR2(50);
    v_order_amount NUMBER;
BEGIN
    -- This line would throw an error in 11g
    SELECT JSON_VALUE(p_input_json, '$.customer_id') INTO v_customer_id FROM DUAL;
    -- This line too
    SELECT JSON_VALUE(p_input_json, '$.order_amount') INTO v_order_amount FROM DUAL;

    -- Your business logic here
    DBMS_OUTPUT.PUT_LINE('Customer ID: ' || v_customer_id || ', Order Amount: ' || v_order_amount);
END;
/

Corrected Procedure Using Regular Expressions #

We’ll replace JSON_VALUE with REGEXP_SUBSTR, which is available in 11g. The regex patterns will target specific JSON keys and extract their values, accounting for string vs. number types:

CREATE OR REPLACE PROCEDURE process_json_records (p_input_json CLOB)
IS
    v_customer_id VARCHAR2(50);
    v_order_amount NUMBER;
BEGIN
    -- Extract string value (handles quoted strings, ignores whitespace around colons)
    v_customer_id := REGEXP_SUBSTR(
        p_input_json, 
        '"customer_id"\s*:\s*"([^"]+)"',  -- Match key, colon, then quoted value
        1, 1, 'i', 1                     -- 1=start at position 1, 1=first match, 'i'=case-insensitive, 1=return first capture group
    );

    -- Extract numeric value (handles integers and decimals, no quotes)
    v_order_amount := TO_NUMBER(REGEXP_SUBSTR(
        p_input_json, 
        '"order_amount"\s*:\s*(\d+(\.\d+)?)',  -- Match key, colon, then number (int/decimal)
        1, 1, 'i', 1
    ));

    -- Your business logic here
    DBMS_OUTPUT.PUT_LINE('Customer ID: ' || v_customer_id || ', Order Amount: ' || v_order_amount);
END;
/

Key Notes for Adjusting to Your Specific JSON #

• String Values: Use the pattern '"<your_key>"\\s*:\\s*"([^"]+)"'—replace <your_key> with your JSON key. The [^"]+ matches all characters until the closing quote (works for simple strings without escaped quotes; if you have escaped quotes, you’ll need a slightly more complex regex like '"<your_key>"\\s*:\\s*"((?:\\\\.|[^"])*)"').
• Numeric Values: Use '"<your_key>"\\s*:\\s*(\\d+(\\.\\d+)?)' to capture integers and decimals.
• Boolean Values: For true/false, use '"<your_key>"\\s*:\\s*(true|false)'.
• Nested Objects: If you need to extract from nested JSON (e.g., $.shipping.address.city), adjust the regex to match the nested structure: '"shipping"\\s*:\\s*\\{"address"\\s*:\\s*\\{"city"\\s*:\\s*"([^"]+)"'.

Test It! #

Try running the procedure with a sample JSON string to verify:

SET SERVEROUTPUT ON;
EXEC process_json_records('{"customer_id": "CUST-1234", "order_amount": 499.99}');

You should see the expected output in your console.

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