Oracle 19+ PL/SQL存储过程报错：COUNT等组件未声明

1. 补充未声明的变量 #

代码中req、resp、associatedSources三个变量被使用但未声明，需在变量声明段添加：

req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
associatedSources SYS.JSON_ARRAY_T;

2. 显式指定JSON类型的SYS前缀 #

Oracle 19c中JSON_OBJECT_T和JSON_ARRAY_T属于SYS系统模式，必须显式声明避免命名空间冲突：

-- JSON parsing variables
json_obj SYS.JSON_OBJECT_T;
items SYS.JSON_ARRAY_T;
item SYS.JSON_OBJECT_T;

3. 修正JSON数组的循环索引 #

JSON_ARRAY_T的索引从0开始（而非1），原代码的1 .. items.COUNT会导致索引越界，需修改循环范围：

FOR i IN 0 .. items.COUNT - 1 LOOP
  item := items.GET_OBJECT(i);
  -- 后续处理逻辑
END LOOP;

同时associatedSources.GET_OBJECT(1)需改为associatedSources.GET_OBJECT(0)。

4. 完善异常处理逻辑 #

避免在resp未初始化时调用UTL_HTTP.END_RESPONSE，添加状态判断：

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    IF UTL_HTTP.IS_RESPONSE_OPEN(resp) THEN
      UTL_HTTP.END_RESPONSE(resp);
    END IF;

完整修正后的代码 #

create or replace PROCEDURE ProcessTableauReportMetadata(
 tenant_identifier VARCHAR2
) IS
 -- Declare variables
 offset NUMBER := 0;
 limit NUMBER := 50;
 json_response CLOB;
 totalResults NUMBER;
 req UTL_HTTP.REQ;
 resp UTL_HTTP.RESP;

    -- JSON parsing variables
   json_obj SYS.JSON_OBJECT_T;
   items SYS.JSON_ARRAY_T;
   item SYS.JSON_OBJECT_T;
   associatedSources SYS.JSON_ARRAY_T;

  -- Data processing variables
   workbook_uuid VARCHAR2(36);
    datasource_uuid VARCHAR2(36);

  BEGIN
       LOOP
      -- Construct endpoint URL with parameters
    endpoint_url := 'https://' || tenant_identifier || '.api.us.devhealtheintent.com/business-intelligence/v1/reports?biType=TABLEAU&lite=false&offset=' || offset || '&limit=' || limit;

-- Begin HTTP request
req := UTL_HTTP.BEGIN_REQUEST(endpoint_url, 'GET');
UTL_HTTP.SET_TRANSFER_TIMEOUT(100);
UTL_HTTP.SET_HEADER(req, 'Authorization', 'Bearer ' || 'TOKEN'); -- Replace with actual token

-- Execute HTTP request
resp := UTL_HTTP.GET_RESPONSE(req);

-- Read the entire JSON response as text
UTL_HTTP.READ_TEXT(resp, json_response);

-- Parse JSON response using built-in JSON parsing functions
json_obj := SYS.JSON_OBJECT_T.PARSE(json_response);
items := json_obj.GET_ARRAY('items');

-- Extract totalResults from JSON response
totalResults := json_obj.GET_NUMBER('totalResults');

-- Process each item in the JSON response
FOR i IN 0 .. items.COUNT - 1 LOOP
  item := items.GET_OBJECT(i);

  -- Extract workbook_uuid and associatedSources from the item
  workbook_uuid := item.GET_STRING('id');
  associatedSources := item.GET_ARRAY('associatedSources');

  -- Check for non-empty associatedSources
  IF associatedSources.COUNT > 0 THEN
    datasource_uuid := associatedSources.GET_OBJECT(0).GET_STRING('id');

    -- Update datasource_uuid in TABLEAU_USAGE_WORKBOOK_METADATA table
    UPDATE TABLEAU_USAGE_WORKBOOK_METADATA
    SET DATASOURCE_UUID = datasource_uuid
    WHERE WORKBOOK_UUID = workbook_uuid;
  END IF;
END LOOP;

-- Close the HTTP response
UTL_HTTP.END_RESPONSE(resp);

-- Increment offset for the next API call
offset := offset + limit;

-- Exit the loop if totalResults is reached
EXIT WHEN offset >= totalResults;
 END LOOP;

EXCEPTION
 WHEN OTHERS THEN
-- Handle exceptions
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
IF UTL_HTTP.IS_RESPONSE_OPEN(resp) THEN
  UTL_HTTP.END_RESPONSE(resp);
END IF;
END ProcessTableauReportMetadata;

额外检查项 #

• 确保当前用户拥有EXECUTE权限访问相关对象，若缺失需执行授权：

  GRANT EXECUTE ON SYS.JSON_OBJECT_T TO your_user;
  GRANT EXECUTE ON SYS.JSON_ARRAY_T TO your_user;
  GRANT EXECUTE ON UTL_HTTP TO your_user;
  

• 确认API返回的JSON结构符合预期，避免因字段缺失或类型不匹配导致运行时错误。

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