在执行此查询时，我遇到错误：
* 错误报告： ORA-06502：PL / SQL：数值或值错误 ORA-06512：第34行 06502. 00000 - “PL / SQL：数字或值错误％s” * 。 我的oracle版本是： Oracle9i企业版9.2.0.1.0版 - 生产 PL / SQL版本9.2.0.1.0 - 生产
请帮帮我..问候，
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;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
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;最满意答案
您可能正在循环一个空集合。 在使用act_dr.FIRST和act_dr.LAST之前，您需要检查act_dr是否有某些行，否则您将获得null，这不是数字。
这可以通过以下方式完成：
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;You might be looping over an empty collection. You need to check that act_dr has some rows before using act_dr.FIRST and act_dr.LAST, otherwise you get null which is not a number.
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;PL SQL数字或值错误(PL SQL numeric or value error)在执行此查询时，我遇到错误：
* 错误报告： ORA-06502：PL / SQL：数值或值错误 ORA-06512：第34行 06502. 00000 - “PL / SQL：数字或值错误％s” * 。 我的oracle版本是： Oracle9i企业版9.2.0.1.0版 - 生产 PL / SQL版本9.2.0.1.0 - 生产
请帮帮我..问候，
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;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
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;最满意答案
您可能正在循环一个空集合。 在使用act_dr.FIRST和act_dr.LAST之前，您需要检查act_dr是否有某些行，否则您将获得null，这不是数字。
这可以通过以下方式完成：
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;You might be looping over an empty collection. You need to check that act_dr has some rows before using act_dr.FIRST and act_dr.LAST, otherwise you get null which is not a number.
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;
发布评论