ORA-06502 PL/SQL: numeric or value error Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. ORA-06512 at str line num Cause: This is usually the last of a message stack and indicates where a problem occurred in the PL/SQL code. Action: Fix the problem causing the exception or write an exception handler for this condition. It may be necessary to contact the application or database administrator.
DECLARE all_units VarChar(200) ; CURSOR c1 IS SELECT unit_code FROM t_factory; CURSOR c2(p_unit_code varchar2) IS SELECT unit_code FROM t_factory Where parent_code =p_unit_code; BEGIN FOR v1 IN c1 LOOP FOR v2 IN c2(v1.unit_code) LOOP all_units := all_units || v2.unit_code ; END LOOP; END LOOP; End ; /
declare定义,应该放在begin外面
SQL> begin 2 DECLARE 3 all_units VarChar(200) ; 4 CURSOR c1 IS 5 SELECT unit_code FROM t_factory; 6 CURSOR c2(p_unit_code varchar2) IS 7 SELECT unit_code FROM t_factory Where parent_code =p_unit_code; 8 BEGIN 9 FOR v1 IN c1 LOOP 10 FOR v2 IN c2(v1.unit_code) LOOP 11 all_units := all_units || v2.unit_code ; 12 END LOOP; 13 END LOOP; 14 End ; 15 end ; 16 / begin * ERROR 位于第 1 行: ORA-06502: PL/SQL: 数字或值错误 ORA-06512: 在line 11
谢谢各位的鼎力相助, 问题是,变量没有初始化 !SQL> Begin 2 DECLARE all_units VarChar(200) ; 3 4 CURSOR c1 IS 5 SELECT unit_code FROM t_factory ; 6 BEGIN 7 FOR parentcode IN c1 LOOP 8 BEGIN 9 all_units := '' ; 10 DECLARE 11 CURSOR c2 IS 12 SELECT unit_code FROM t_factory 13 Where parent_code = parentcode.unit_code ; 14 Begin 15 16 FOR unitcode IN c2 LOOP 17 Begin 18 all_units := all_units || unitcode.unit_code ; 19 End ; 20 END LOOP; 21 End ; 22 End ; 23 END LOOP; 24 End ; 25 End ; 26 /PL/SQL 过程已成功完成。
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. ORA-06512 at str line num Cause: This is usually the last of a message stack and indicates where a problem occurred in the PL/SQL code.
Action: Fix the problem causing the exception or write an exception handler for this condition. It may be necessary to contact the application or database administrator.
all_units VarChar(200) ;
CURSOR c1 IS
SELECT unit_code FROM t_factory;
CURSOR c2(p_unit_code varchar2) IS
SELECT unit_code FROM t_factory Where parent_code =p_unit_code;
BEGIN
FOR v1 IN c1 LOOP
FOR v2 IN c2(v1.unit_code) LOOP
all_units := all_units || v2.unit_code ;
END LOOP;
END LOOP;
End ;
/
2 DECLARE
3 all_units VarChar(200) ;
4 CURSOR c1 IS
5 SELECT unit_code FROM t_factory;
6 CURSOR c2(p_unit_code varchar2) IS
7 SELECT unit_code FROM t_factory Where parent_code =p_unit_code;
8 BEGIN
9 FOR v1 IN c1 LOOP
10 FOR v2 IN c2(v1.unit_code) LOOP
11 all_units := all_units || v2.unit_code ;
12 END LOOP;
13 END LOOP;
14 End ;
15 end ;
16 /
begin
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在line 11
谢谢各位的鼎力相助, 问题是,变量没有初始化 !SQL> Begin
2 DECLARE all_units VarChar(200) ;
3
4 CURSOR c1 IS
5 SELECT unit_code FROM t_factory ;
6 BEGIN
7 FOR parentcode IN c1 LOOP
8 BEGIN
9 all_units := '' ;
10 DECLARE
11 CURSOR c2 IS
12 SELECT unit_code FROM t_factory
13 Where parent_code = parentcode.unit_code ;
14 Begin
15
16 FOR unitcode IN c2 LOOP
17 Begin
18 all_units := all_units || unitcode.unit_code ;
19 End ;
20 END LOOP;
21 End ;
22 End ;
23 END LOOP;
24 End ;
25 End ;
26 /PL/SQL 过程已成功完成。