oracle PL SQL 数字或值错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9060668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL SQL numeric or value error
提问by nalaiqChughtai
On Executing this query, im having an error:
在执行这个查询时，我有一个错误：
*Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 34
06502. 00000 - "PL/SQL: numeric or value error%s"*
.
My oracle version is:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
*错误报告：
ORA-06502：PL/SQL：数字或值错误
ORA-06512：在第 34 行
06502.00000 -“PL/SQL：数字或值错误%s”*
。
我的oracle版本是：
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
please help me..
Regards,
请帮助我.. 问候，
DECLARE CURSOR c1 is SELECT TABLE_NAME FROM All_Tables WHERE TABLE_NAME NOT LIKE '%$%' AND Owner NOT IN ('WMSYS', 'EXFSYS', 'CTXSYS', 'WKSYS', 'SYSMAN', 'SYSTEM', 'FLOWS_030000', 'MDSYS', 'ORDSYS', 'DBSNMP', 'XDB', 'OLAPSYS');
col_names SYS_REFCURSOR; TYPE dat_res IS RECORD(tab_name VARCHAR2(1000),col_name VARCHAR2(1000));
TYPE dr is table of dat_res;
act_dat dr:= dr();
status NUMBER := 0;
cnt NUMBER := 1;
sql_stmt VARCHAR2(10000);
tab_name1 VARCHAR2(100);
col_name1 VARCHAR2(100);
BEGIN
FOR I IN C1 LOOP sql_stmt:= 'SELECT table_name,column_name FROM all_Tab_cols WHERE table_name = '||CHR(39)||I.table_name||CHR(39);
OPEN col_names FOR sql_stmt; LOOP FETCH col_names INTO tab_name1,col_name1;
EXIT WHEN col_names%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE
'SELECT 1 FROM '||tab_name1|| ' WHERE REGEXP_LIKE('||'TO_CHAR('||col_name1||'), '||CHR(39)||'^[%][a-bA-B]'||CHR(39)||') '
INTO status;
EXCEPTION
WHEN VALUE_ERROR THEN NULL;
WHEN NO_DATA_FOUND THEN NULL;
WHEN OTHERS THEN NULL;
END;
IF (status = 1) THEN act_dat.extend; act_dat(cnt).tab_name:= tab_name1; act_dat(cnt).col_name:= col_name1; status := 0; cnt:= cnt + 1;
END IF; END LOOP;
CLOSE col_names;
END LOOP;
dbms_output.put_line('Table Name : Column Name');
FOR K IN act_dat.FIRST..act_dat.LAST LOOP insert into my_SAuditor_table VALUES (act_dat(K).tab_name, act_dat(K).col_name);
END LOOP;
Execute IMMEDIATE 'SELECT * FROM my_SAuditor_table';
END;
回答by A.B.Cade
You might be looping over an empty collection.
You need to check that act_dr has some rows before using act_dr.FIRSTand act_dr.LAST, otherwise you get null which is not a number.
您可能正在遍历一个空集合。
在使用act_dr.FIRSTand之前，您需要检查 act_dr 是否有一些行act_dr.LAST，否则您会得到不是数字的 null。
This can be done with:
这可以通过以下方式完成：
if act_dr.count > 0 then
FOR K IN act_dat.FIRST..act_dat.LAST LOOP
insert into my_SAuditor_table VALUES (act_dat(K).tab_name, act_dat(K).col_name);
END LOOP;
end if;
回答by steve
Problem is that column table name definitions are not consistent. In the end you end up inserting a 1000 bytes column in a 512 bytes column and the funny part is, you only read the column with a max length of 100 bytes.
问题是列表名称定义不一致。最后，您最终在 512 字节的列中插入了 1000 字节的列，有趣的是，您只读取了最大长度为 100 字节的列。
I'd advise to cleanup the code.
我建议清理代码。
