1.创建一个表存储错误码
2. 查询所有错误码并插入到表中
3. 执行存储过程
4. 查询结果
SQL> create table dict_errordesc(eno integer primary key, emsg long);
Table created
2. 查询所有错误码并插入到表中
create or replace procedure createErrorDesc is
msg long;
status integer;
begin
-- insert table
dbms_output.enable(1000000);
for i in 0..10000 loop
dbms_output.put_line(SQLERRM(0-i));
dbms_output.get_line(msg, status);
insert into dict_errordesc values(i, msg);
end loop;
dbms_output.disable();
dbms_output.enable(1000000);
for i in 10001..20000 loop
dbms_output.put_line(SQLERRM(0-i));
dbms_output.get_line(msg, status);
insert into dict_errordesc values(i, msg);
end loop;
-- commit
commit;
end createErrorDesc;
3. 执行存储过程
SQL> set serveroutput on
SQL> exec createErrorDesc
PL/SQL procedure successfully completed
4. 查询结果
SQL> select count(*) from dict_errordesc;
COUNT(*)
----------
20001
SQL> select * from dict_errordesc where rownum <= 10;
ENO EMSG
--------------------------------------- --------------------------------------------------------------------------------
0 ORA-0000: normal, successful completion
1 ORA-00001: 违反唯一约束条件 (.)
2 ORA-00002: Message 2 not found; product=RDBMS; facility=ORA
3 ORA-00003: Message 3 not found; product=RDBMS; facility=ORA
4 ORA-00004: Message 4 not found; product=RDBMS; facility=ORA
5 ORA-00005: Message 5 not found; product=RDBMS; facility=ORA
6 ORA-00006: Message 6 not found; product=RDBMS; facility=ORA
7 ORA-00007: Message 7 not found; product=RDBMS; facility=ORA
8 ORA-00008: Message 8 not found; product=RDBMS; facility=ORA
9 ORA-00009: Message 9 not found; product=RDBMS; facility=ORA
10 rows selected