从返回对象类型的函数错误ORA-06530(Error ORA-06530 from function that returns an object type)
我有以下函数GET_UN_COLLECTED_4LD ,它返回类型ld_data_type :
CREATE OR REPLACE TYPE ld_data_type AS OBJECT(collected NUMBER, uncollected NUMBER);
/
CREATE OR REPLACE FUNCTION GET_UN_COLLECTED_4LD (VAPPOINTOFCAID NUMBER,
VPREVLIQUIDATE NUMBER,
VCURRLIQUIDATE NUMBER,
VNEXTLIQUIDATE NUMBER,
YEPT NUMBER)
RETURN LD_DATA_TYPE
AS
OUT_VAR LD_DATA_TYPE;
VNETV23 NUMBER;
VNETV24 NUMBER;
VCURR23 NUMBER;
VCURR24 NUMBER;
VCOLLECTED NUMBER;
VUNCOLLECTED NUMBER;
BEGIN
SELECT SUM (NETX) - SUM (NETP)
INTO VNETV23
FROM VIEW_CUSTOMER_TRN4INVS4LD
WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.23);
SELECT SUM (NETX) - SUM (NETP)
INTO VNETV24
FROM VIEW_CUSTOMER_TRN4INVS4LD
WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.24);
VCOLLECTED := 0;
VUNCOLLECTED := 0;
CASE
WHEN YEPT = 0
THEN
VCURR24 := VCURRLIQUIDATE - VNETV24;
CASE
WHEN VCURR24 > 0
THEN
VCOLLECTED := VNETV24 * 1.24;
VCURR23 := VCURRLIQUIDATE - VNETV23;
CASE
WHEN VCURR23 > 0
THEN
VCOLLECTED := -999;
ELSE
VCOLLECTED :=
VCOLLECTED + ( (VCURRLIQUIDATE - VCURR24) * 1.23);
END CASE;
ELSE
VCOLLECTED := -1999;
END CASE;
ELSE
OUT_VAR.COLLECTED := -888;
OUT_VAR.UNCOLLECTED := -889;
END CASE;
SELECT VCOLLECTED, VUNCOLLECTED
INTO OUT_VAR.COLLECTED, OUT_VAR.UNCOLLECTED
FROM DUAL;
/*
OPEN buffer_cur;
FETCH buffer_cur INTO out_var.val1, out_var.val2;
CLOSE buffer_cur; */
RETURN OUT_VAR;
END GET_UN_COLLECTED_4LD;
当我调用函数时
select GET_UN_COLLECTED_4LD(171231, 42240, 31680, 0, 0) from dual;`
我收到一个错误:
Execution (50: 8): ORA-06530: Reference to uninitialized composite
ORA-06512: at "C##SOLSA.GET_UN_COLLECTED_4LD", line 56
我使用的是Oracle 12c标准版。
我应该如何调用函数GET_UN_COLLECTED_4LD以及导致错误的问题在哪里?
I have the following function GET_UN_COLLECTED_4LD which returns the type ld_data_type:
CREATE OR REPLACE TYPE ld_data_type AS OBJECT(collected NUMBER, uncollected NUMBER);
/
CREATE OR REPLACE FUNCTION GET_UN_COLLECTED_4LD (VAPPOINTOFCAID NUMBER,
VPREVLIQUIDATE NUMBER,
VCURRLIQUIDATE NUMBER,
VNEXTLIQUIDATE NUMBER,
YEPT NUMBER)
RETURN LD_DATA_TYPE
AS
OUT_VAR LD_DATA_TYPE;
VNETV23 NUMBER;
VNETV24 NUMBER;
VCURR23 NUMBER;
VCURR24 NUMBER;
VCOLLECTED NUMBER;
VUNCOLLECTED NUMBER;
BEGIN
SELECT SUM (NETX) - SUM (NETP)
INTO VNETV23
FROM VIEW_CUSTOMER_TRN4INVS4LD
WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.23);
SELECT SUM (NETX) - SUM (NETP)
INTO VNETV24
FROM VIEW_CUSTOMER_TRN4INVS4LD
WHERE APPOINTOFCAID = VAPPOINTOFCAID AND VAT = ABS (1.24);
VCOLLECTED := 0;
VUNCOLLECTED := 0;
CASE
WHEN YEPT = 0
THEN
VCURR24 := VCURRLIQUIDATE - VNETV24;
CASE
WHEN VCURR24 > 0
THEN
VCOLLECTED := VNETV24 * 1.24;
VCURR23 := VCURRLIQUIDATE - VNETV23;
CASE
WHEN VCURR23 > 0
THEN
VCOLLECTED := -999;
ELSE
VCOLLECTED :=
VCOLLECTED + ( (VCURRLIQUIDATE - VCURR24) * 1.23);
END CASE;
ELSE
VCOLLECTED := -1999;
END CASE;
ELSE
OUT_VAR.COLLECTED := -888;
OUT_VAR.UNCOLLECTED := -889;
END CASE;
SELECT VCOLLECTED, VUNCOLLECTED
INTO OUT_VAR.COLLECTED, OUT_VAR.UNCOLLECTED
FROM DUAL;
/*
OPEN buffer_cur;
FETCH buffer_cur INTO out_var.val1, out_var.val2;
CLOSE buffer_cur; */
RETURN OUT_VAR;
END GET_UN_COLLECTED_4LD;
When I call the function like
select GET_UN_COLLECTED_4LD(171231, 42240, 31680, 0, 0) from dual;`
I get an error:
Execution (50: 8): ORA-06530: Reference to uninitialized composite
ORA-06512: at "C##SOLSA.GET_UN_COLLECTED_4LD", line 56
I'm use Oracle 12c Standard edition.
How should I be calling the function GET_UN_COLLECTED_4LD and Where is the problem that causes the error?
原文:https://stackoverflow.com/questions/40590941
更新时间:2020-07-03 08:07
最满意答案
首先,您需要初始化对象类型OUT_VAR然后才能设置其属性,如下所示:
OUT_VAR:= LD_DATA_TYPE(0,0);
在这里查看详细信息。
First you need to initialize your object type OUT_VAR before you can set its properties like this:
OUT_VAR:= LD_DATA_TYPE(0,0);
See the details here.
2016-11-14
相关问答
最可能的解释是,当您告诉它您正在发送JSON但是您的JSON无效时,服务器会抛出错误。 您不能使用'来分隔JSON中的字符串。 不要尝试手工编写JSON,构造JavaScript对象,然后使用JSON序列化库。 data: JSON.stringify({requestParameters:{username:'xxxx', password:'xxxx'}),
Already found a solution for my project but can not do it with JQue
...
我在Oracle 11gR1中重现了同样的行为。 我同意你的看法,这对我来说似乎也是一个错误,尽管是一个微不足道的问题。 SQL> CREATE OR REPLACE TYPE tbusiness_inter_item_bag AS OBJECT (
2 item_id NUMBER(1),
3 system_event_cd VARCHAR2 (20),
4 CONSTRUCTOR FUNCTION tbusiness_in
...
你很亲密; 你正在寻找允许你迭代键的映射类型 (而不是使用索引器)。 也就是说,不要使用这个: {[key: K]: IResources[K]}
使用此代替: {[P in K]: IResources[P]}
实际上,这是一个非常有用的构造,它作为Pick存在于标准库中: Pick
所以你的功能变成了: export function mapResources(resources: IReso
...
您必须在集合中初始化emp_rec_type。 这两行 l_arr(l_arr.last).emp_name := 'a';
l_arr(l_arr.last).addr_rec := address_type ( 'a','b','c');
用这个linie替换 l_arr(l_arr.last) := emp_rec_type('a', address_type ( 'a','b','c'));
You have to initialize emp_rec_type in the co
...
在现实生活中没有人使用object_type表,但我设法让这个工作。 您需要使用在第一个脚本中使用的相同“选择值”语法。 给它一个别名(我使用x)然后你可以在循环中引用它: DECLARE
student student_typ;
cursor find is select value(s) x from student_obj_table s;
BEGIN -- PL/SQL block for selecting-displaying a student
for find_rec in f
...
您可能有另一个具有相同名称的对象( PERFORM_CHECK )。 您可以通过查询user_objects找到它: select *
from user_objects
where object_name = 'PERFORM_CHECK'
然后删除它(将TYPE_OF_OBJECT替换为上述查询中对象的类型): drop TYPE_OF_OBJECT perform_check
You probably have another object with the same name
...
当找不到任何东西时你应该返回一些东西 在声明中的exception评论之前 return v_postcode;
编写例程的方式可能确实找不到任何内容然后命中函数的末尾而不返回任何内容,因此错误“函数返回没有值”。 这确实是这个。 但是,通过直接在郊区选择,您可以更轻松地完成这项工作。 但如果您在郊区获得多个邮政编码(如果可能的话),您会怎么做? create or replace FUNCTION get_postCode(i_suburb IN varchar2)
RETURN varcha
...
当使用记录而不是对象时,无论如何您都不必初始化它。 您也可以使用tablename%rowtype而不是显式定义类型。 declare
TYPE CUSTOM_REC_TYPE IS RECORD
(
ID NUMBER(19),
COLUMN1 NUMBER(19),
COLUMN2 NUMBER(19),
...
如果要执行shared_ptr的运行时向下转换,请使用std::dynamic_pointer_cast<> : std::shared_ptr b = std::dynamic_pointer_cast(createObject(0));
如果要在运行时确定要创建的对象的类型,并且您的尝试是检查返回的对象是否具有类型B ,那么我看不到比动态向下转换更好的替代方案。 另一方面,如果您100%确定知道指向对象具有类型B ,那么您可以使用std::static_pointer_cast<
...
首先,您需要初始化对象类型OUT_VAR然后才能设置其属性,如下所示: OUT_VAR:= LD_DATA_TYPE(0,0);
在这里查看详细信息。 First you need to initialize your object type OUT_VAR before you can set its properties like this: OUT_VAR:= LD_DATA_TYPE(0,0);
See the details here.