PLS-00201: 必须声明标识符

Ellion 2020-01-28 01:04:14

[警告] ORA-24344: 成功, 但出现编译错误
767/69 PLS-00201: 必须声明标识符 'RETOBJ20'
765/3 PL/SQL: Item ignored
1258/24 PLS-00201: 必须声明标识符 'RETDATA20'
1258/24 PL/SQL: Item ignored
1259/24 PLS-00201: 必须声明标识符 'RETOBJ20'
1259/24 PL/SQL: Item ignored
1279/9 PLS-00320: 此表达式的类型声明不完整或格式不正确
1279/9 PL/SQL: Statement ignored
1280/9 PLS-00320: 此表达式的类型声明不完整或格式不正确
1280/9 PL/SQL: Statement ignored
1303/11 PLS-00320: 此表达式的类型声明不完整或格式不正确
1303/11 PL/SQL: Statement ignored
1307/15 PLS-00320: 此表达式的类型声明不完整或格式不正确
1307/11 PL/SQL: Statement ignored
1323/20 PLS-00320: 此表达式的类型声明不完整或格式不正确
1323/20 PL/SQL: ORA-00904: "V_LIST": 标识符无效
1317/7 PL/SQL: SQL Statement ignored
(1: 0): 警告：已编译但带有编译错误

这个是需要建立类型吗？如何建立

create or replace PROCEDURE get_list_test(p_apply_level IN VARCHAR2
,p_level_value IN VARCHAR2
,x_ret_code OUT VARCHAR2
,x_ret_msg OUT VARCHAR2
,x_ret_data OUT SYS_REFCURSOR) IS
rec_profile sys_setting%ROWTYPE;
v_profile_value VARCHAR2(1024);
v_profile_value_id VARCHAR2(32);
v_list retdata20 := NEW retdata20();
v_model retobj20;
v_count NUMBER := 0;
BEGIN
x_ret_code := retsucc;
FOR cur IN (SELECT t.*
FROM sys_setting
WHERE t.apply_level LIKE '%' || p_apply_level || '%'
AND t.state = 'A')
LOOP
v_count := v_count + 1;
BEGIN
SELECT pv.value
,pv.id
INTO v_profile_value
,v_profile_value_id
FROM sys_setting_value pv
WHERE pv.profile_id = cur.id
AND pv.level_value = p_level_value;

v_list.extend();
v_list(v_list.count) := retobj20('PROFILEINFO'
,v_profile_value_id
,cur.profile_code
,p_apply_level
,p_level_value
,v_profile_value
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL));
EXCEPTION
WHEN no_data_found THEN
v_model := get_parent_profile_value(cur.profile_code
,p_apply_level
,p_level_value);

IF (v_model.f01 IS NOT NULL)
THEN
v_list.extend();
v_list(v_list.count) := v_model;
END IF;
END;

END LOOP;
v_count := v_count + 1;
OPEN x_ret_data FOR
SELECT res.f01 AS profile_value_id
,res.f02 AS profile_code
,res.f03 AS profile_level_type
,res.f04 AS profile_level_value
,res.f05 AS profile_value
,res.f06 AS profile_level_value_name
FROM TABLE(v_list) res;

END;

...全文

1006 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

lhdz_bj 2020-01-29

打赏
举报

引用楼主 Ellion 的回复:

[警告] ORA-24344: 成功, 但出现编译错误
767/69 PLS-00201: 必须声明标识符 'RETOBJ20'
765/3 PL/SQL: Item ignored
1258/24 PLS-00201: 必须声明标识符 'RETDATA20'
1258/24 PL/SQL: Item ignored
1259/24 PLS-00201: 必须声明标识符 'RETOBJ20'
1259/24 PL/SQL: Item ignored
1279/9 PLS-00320: 此表达式的类型声明不完整或格式不正确
1279/9 PL/SQL: Statement ignored
1280/9 PLS-00320: 此表达式的类型声明不完整或格式不正确
1280/9 PL/SQL: Statement ignored
1303/11 PLS-00320: 此表达式的类型声明不完整或格式不正确
1303/11 PL/SQL: Statement ignored
1307/15 PLS-00320: 此表达式的类型声明不完整或格式不正确
1307/11 PL/SQL: Statement ignored
1323/20 PLS-00320: 此表达式的类型声明不完整或格式不正确
1323/20 PL/SQL: ORA-00904: "V_LIST": 标识符无效
1317/7 PL/SQL: SQL Statement ignored
(1: 0): 警告：已编译但带有编译错误

这个是需要建立类型吗？如何建立

create or replace PROCEDURE get_list_test(p_apply_level IN VARCHAR2
,p_level_value IN VARCHAR2
,x_ret_code OUT VARCHAR2
,x_ret_msg OUT VARCHAR2
,x_ret_data OUT SYS_REFCURSOR) IS
rec_profile sys_setting%ROWTYPE;
v_profile_value VARCHAR2(1024);
v_profile_value_id VARCHAR2(32);
v_list retdata20 := NEW retdata20();
v_model retobj20;
v_count NUMBER := 0;
BEGIN
x_ret_code := retsucc;
FOR cur IN (SELECT t.*
FROM sys_setting
WHERE t.apply_level LIKE '%' || p_apply_level || '%'
AND t.state = 'A')
LOOP
v_count := v_count + 1;
BEGIN
SELECT pv.value
,pv.id
INTO v_profile_value
,v_profile_value_id
FROM sys_setting_value pv
WHERE pv.profile_id = cur.id
AND pv.level_value = p_level_value;

v_list.extend();
v_list(v_list.count) := retobj20('PROFILEINFO'
,v_profile_value_id
,cur.profile_code
,p_apply_level
,p_level_value
,v_profile_value
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL)
,to_char(NULL));
EXCEPTION
WHEN no_data_found THEN
v_model := get_parent_profile_value(cur.profile_code
,p_apply_level
,p_level_value);

IF (v_model.f01 IS NOT NULL)
THEN
v_list.extend();
v_list(v_list.count) := v_model;
END IF;
END;

END LOOP;
v_count := v_count + 1;
OPEN x_ret_data FOR
SELECT res.f01 AS profile_value_id
,res.f02 AS profile_code
,res.f03 AS profile_level_type
,res.f04 AS profile_level_value
,res.f05 AS profile_value
,res.f06 AS profile_level_value_name
FROM TABLE(v_list) res;

END;

1. RETOBJ20和RETDATA20类型没声明；

2. retobj20函数没声明;

3. 例子的话，那就附上我所著书中的一段例子吧，楼主自己理解：
SQL> set serveroutput on
SQL> create table tmp_stud as select * from t_stud;
SQL> declare
type tp_stud is table of t_stud%rowtype;
tb_stud tp_stud;
begin
delete from tmp_stud returning stud_no,name,age,class_no,teach_no
bulk collect into tb_stud;
for i in tb_stud.first .. tb_stud.last loop
DBMS_OUTPUT.PUT_LINE(tb_stud(i).name);
end loop;
end;
/