oracle - ORA-06530 引用 PL/SQL 过程中未初始化的复合错误
问题描述
我有一个程序如下。
create or replace PACKAGE BODY "DTS_INVOICE_RESEARCH_PKG" AS
PROCEDURE DO_RESEARCH (IN_ROW_TBL IN OUT NOCOPY DTS.DTS_CARRIER_ROW_TBL,
OUT_ROW_VARRAY IN OUT NOCOPY DTS.DTS_CARRIER_ROW_RETURN_VARRAY)
AS
TYPE CARRIER_ROW_REC_COLLECTION IS TABLE OF DTS.DTS_CARRIER_ROW_REC INDEX BY VARCHAR2(50);
COUNTER INTEGER := 0;
TRACKING_NUMBER VARCHAR2(50);
ROW_COLLECTION CARRIER_ROW_REC_COLLECTION; --K,V pair of tracking number and CARRIER_ROW_REC
--This cursor finds all tracking numbers which already exist in the INVOICE_CARTON table
--If they already exist, that means the tracking number was previously invoiced
CURSOR C1 IS
SELECT /*+ PARALLEL(8) */ TRACKING_NUMBER
FROM DTS.DTS_INVOICE_CARTON
WHERE TRACKING_NUMBER IN
(SELECT TRACKING_NUMBER FROM TABLE(IN_ROW_TBL));
CURSOR C2 IS
select do.PROMISE_DATE, do.CUSTOMER_ID
, do.region_id, upper(trim(dm.market)) market
, count(do.CUSTOMER_ID) carton_count
, dc.TRACKING_NUMBER TRACKING_NUMBER
from dts.dts_order_container dc,
dts.dts_order do,
dts.dts_route_matrix dm,
dts.dts_invoice_ctr ct
where ct.region_id = dm.region_id
and ct.market_id = dm.market_id
and ct.vendor_id = dm.carrier_id
and dm.region_id = do.region_id
and dm.route_id = do.route_id
and do.order_number = dc.order_number
and dm.carrier_id IN (SELECT DISTINCT dm.CARRIER_ID FROM TABLE(IN_ROW_TBL))
and dm.active = 'Y' --William, ensures we only reference active RM rows, fixes NAIT-28235
and dm.carrier_mode not in ('LTL','USPS','PF','OTHER','Other','UPS')
and do.ORDER_NUMBER in (SELECT DISTINCT do.ORDER_NUMBER FROM TABLE(IN_ROW_TBL))
and upper(trim(dm.market)) not like 'FURNITURE' --remove any markets that have 'furniture' in them
group by do.PROMISE_DATE, do.CUSTOMER_ID, do.region_id, upper(trim(dm.market)), dc.tracking_number
having count(do.CUSTOMER_ID) = 1
order by do.PROMISE_DATE, do.REGION_ID, upper(trim(dm.market)), do.CUSTOMER_ID;
C1_REC C1%ROWTYPE;
C2_REC C2%ROWTYPE;
BEGIN
OUT_ROW_VARRAY := DTS.DTS_CARRIER_ROW_RETURN_VARRAY();
OUT_ROW_VARRAY.EXTEND (IN_ROW_TBL.COUNT);
--Loops through the input table to create a collection for easy object updating
FOR IDX1 IN 1..IN_ROW_TBL.COUNT LOOP
--Stores the payable order statuses for each tracking number, if applicable
--The storing happens before the object is transferred into the collection
BEGIN
SELECT NVL(TO_NUMBER(ORDER_STATUS, '99'), 0)
INTO IN_ROW_TBL(IDX1).ORDER_STATUS --sets the current object's edi status
FROM DTS.DTS_ORDER_STATUS_LOG
WHERE ORDER_NUMBER = IN_ROW_TBL(IDX1).ORDER_NUMBER
AND TRACKING_NUMBER = IN_ROW_TBL(IDX1).TRACKING_NUMBER
AND ORDER_STATUS IN ('50','51','52','07')
AND ROWNUM < 2; --ensures we don't get multple results and choke
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
ROW_COLLECTION(IN_ROW_TBL(IDX1).TRACKING_NUMBER) := IN_ROW_TBL(IDX1);
END LOOP;
--Loop through the cursor, and update the invoiced flag of whichever tracking numbers
--are returned by the cursor
OPEN C1;
LOOP
FETCH C1 INTO C1_REC;
EXIT WHEN C1%NOTFOUND;
--SET THE INVOICED FLAG FOR THE CARRIER_REC WHICH IS ASSOCIATED WITH THE TRACKING NUMBER WE FOUND
ROW_COLLECTION(C1_REC.TRACKING_NUMBER).INVOICED := 1; --INVOICED SHOULD EITHER BE 1 OR 0, SETTING TO 1 MEANS INVOICED
END LOOP;
CLOSE C1;
OPEN C2;
LOOP
FETCH C2 INTO C2_REC;
EXIT WHEN C2%NOTFOUND;
--SET THE INVOICED FLAG FOR THE CARRIER_REC WHICH IS ASSOCIATED WITH THE TRACKING NUMBER WE FOUND
ROW_COLLECTION(C2_REC.TRACKING_NUMBER).IS_SINGLE_CARTON := 1; --INVOICED SHOULD EITHER BE 1 OR 0, SETTING TO 1 MEANS INVOICED -- getting error at this line
END LOOP;
CLOSE C2;
--These are both control variables for iterating through the collection and storing
--the CARRIER_ROW objects in the output array
TRACKING_NUMBER := ROW_COLLECTION.FIRST;
COUNTER := 1;
--Loop through the collection to copy the ROW_REC objects into the output varray
WHILE TRACKING_NUMBER IS NOT NULL
LOOP
OUT_ROW_VARRAY(COUNTER) := ROW_COLLECTION(TRACKING_NUMBER);
TRACKING_NUMBER := ROW_COLLECTION.NEXT(TRACKING_NUMBER);
COUNTER := COUNTER + 1;
END LOOP;
END DO_RESEARCH;
END DTS_INVOICE_RESEARCH_PKG;
-> 一种对象类型为:
CREATE OR REPLACE TYPE "DTS"."DTS_CARRIER_ROW_REC" FORCE AS OBJECT
(
ORDER_NUMBER VARCHAR2(25 BYTE) ,
TRACKING_NUMBER VARCHAR2(50 BYTE) ,
ROW_NUMBER INT ,
INVOICED INT ,
ORDER_STATUS INT ,
CARRIER_ID VARCHAR2(20) ,
IS_SINGLE_CARTON INT
);
对于上述过程,我在光标 C2 处遇到错误。错误:错误消息 =
ORA-06530: Reference to uninitialized composite
ORA-06512: at "DTS.DTS_INVOICE_RESEARCH_PKG", line 89
ORA-06512: at line 2
请帮助我如何解决此错误。谢谢!
解决方案
除了你的包中有几个弱点(见我的评论),看起来你用未初始化的值调用了这个过程。
CREATE OR REPLACE TYPE DTS_CARRIER_ROW_TBL AS TABLE OF NUMBER;
CREATE OR REPLACE PROCEDURE DO_RESEARCH(IN_ROW_TBL IN DTS_CARRIER_ROW_TBL) IS
BEGIN
DBMS_OUTPUT.PUT_LINE ( 'Result: ' || IN_ROW_TBL.COUNT );
END;
/
和电话:
DECLARE
IN_ROW_TBL DTS_CARRIER_ROW_TBL;
BEGIN
DO_RESEARCH(IN_ROW_TBL);
END;
ORA-06531: Reference to uninitialized collection
ORA-06512: at "DO_RESEARCH", line 3
ORA-06512: at line 4
DECLARE
IN_ROW_TBL DTS_CARRIER_ROW_TBL := DTS_CARRIER_ROW_TBL();
BEGIN
DO_RESEARCH(IN_ROW_TBL);
END;
Result: 0
推荐阅读
- compiler-construction - 使用没有解析器模块的 ocamllex 构建词法分析器
- splunk - 如果没有更改任何输入,Splunk 提交按钮 (submitButton) 不会刷新仪表板
- go - 在 Go 中如何向无缓冲通道写入/读取多个值?
- c# - 如何用 NAudio 处理输入的音频信号并实时播放?(C#WPF.NET)
- typescript - 可以接受两个对象或一个合并对象的重载函数
- flutter - 扑动中的firestore和rtc引擎问题
- r - 使用 ggplot 比较 2 个分布
- laravel - 在 https://proclubs.app/login 上找不到所有 Laravel 路由(404 错误)
- mysql - MYSQL 删除具有最小 DATE 值的所有行
- java - 如何将 Jedis 实例返回到池中?